Образователни технологии
FINANCIAL PROJECTS AND LOANS WITH DIFFERENT, DECURSIVE ANNUITIES, INTEREST RATES AND CAPITALIZATION PERIODS WITH THE USE OF EXCEL
https://doi.org/10.53656/math2021-4-1-fin
Резюме. The authors of the paper have innovated and explored the theory of size relations of a financial project, the duration of its creation, the determination of its price, the duration of use and financing of the price of a project. The duration of use of a financial project has two planned time intervals. The first time interval is planned for repayment of the loan amount of the project price, and the second one is for acquisition of the planned profit. A new model of loan repayment is used – with different; decursive annuities, interest rates and periods of capitalization. The introduced theory was applied to calculations in the form of a financial project model using Excel.
Ключови думи: financial project; loan; annuities; interest rates; capitalization
Introduction
Projects with planned duration, price, price financing and duration of use are financial projects, projects in general. Project holders are mostly legal entities or individuals. Project funding can be self-financing, loan (credit), and a combination of both. Project management and administration is mainly performed by project management with associates.
The topic of the paper is innovation and research of the theory for the introduction of a model of a financial project, with loan financing of its price, which can be supported by spreadsheets (MS Excel). Among other things, the theory will be based on the application of complex, conformal capitalizations, by discounting and discounting the amounts of the present term to the amounts of earlier and later terms of the same time course.
1. Planned loan financing of the project price amount by tranche payments
Loan financing of the project price can be done by paying one tranche at the beginning of the project or by paying two or more tranches in the time interval of project development. The general theory will be introduced for \(m\) different tranche amounts with different payment terms, different interest rates and different lengths of capitalization period.
Sizes with introduced marks for the 1st capitalization period:
(1) Amount of the \(1^{\text {st }}\) tranche, \(T R_{1}\)
(2) Payment term of the \(1^{\text {st }}\) tranche, \(T E_{0}\) in format dd.mm.yyyy hh: mm: ss
(3) Length of the \(1^{\text {st }}\) capitalization period, in days, \(d_{1}\)
(4) Length of the \(1^{\text {st }}\) capitalization period, in years, \(g_{1}=\tfrac{d_{1}}{365}\)
(5) Amount of the annual interest rate, \(P_{1}\)
(6) Amount of the annual interest factor, \(R_{1}=1+P_{1}\)
Sizes with introduced marks for the \(\mathbf{2}^{\text {nd }}\) capitalization period:
(1) Amount of the \(2^{\text {nd }}\) tranche, \(T R_{2}\)
(2) Payment term of the \(2^{\text {nd }}\) tranche, \(T E_{2}=T E_{0}+d_{1}\) in format dd.mm.yyyy hh: mm: ss
(3) Length of the \(2^{\text {nd }}\) capitalization period, in days, \(d_{2}\)
(4) Length of the \(2^{\text {nd }}\) capitalization period, in years, \(g_{2}=\tfrac{d_{2}}{365}\)
(5) Amount of the annual interest rate, \(P_{2}\)
(6) Amount of the annual interest factor, \(R_{2}=1+P_{2}\).
Sizes with introduced marks for \(\boldsymbol{m}\) capitalization period:
(1) Amount \(m\) of the tranche, \(T R_{\mathrm{m}}\), for \(m \in\{3,4, \ldots, M\}\)
(2) Term of payment \(m\) tranche, \(T E_{\mathrm{m}}=T E_{\mathrm{m}-2}+d_{\mathrm{m}-1}\)
(3) Length \(m\) of the capitalization period, in days, \(d_{m}\)
(4) Length \(m\) of the capitalization period, in years, \(g_{m}=\tfrac{d_{m}}{365}\)
(5) Amount of the annual interest rate, \(P_{\mathrm{m}}\)
(6) Amount of the annual interest factor, \(R_{\mathrm{m}}=1+P_{\mathrm{m}}\)
(7) Term end \(m\) of the capitalization period, \(T E_{\mathrm{m}}=T E_{\mathrm{m}-1}+d_{\mathrm{m}}\)
(8) Amount of self-financing for term \(T E_{\mathrm{m}}, S F\)
Input values for calculations are: tranche amounts, payment term of the \(1^{\text {st }}\) tranche (\(T E_{0}\) ), length of capitalization period in days, annual interest rate amounts and self-financing amount (\(S F\) ).
Some or all of the tranche amounts, capitalization periods and interest rates may have equal values.
Price of the project and length of its development:
(1) Date of completion of the project, \(T E_{\mathrm{m}}=T E_{\mathrm{m}-1}+d_{\mathrm{m}}\),
(2) Discounted amount of the first tranche \(E T R_{1}\) for the date of completion of the project, \(E T R_{1}=T R_{1} \cdot R_{1}{ }^{g}{ }^{l} \cdot R_{2}{ }^{g 2} \ldots R_{m}{ }^{g m}\)
(3)Discountedamount of the \(2^{\text {nd }}\) tranche \(E T R_{2}\) forthedate of completionoftheproject, \(E T R_{2}=T R_{2} \cdot R_{1}{ }^{g l} \cdot R_{2}{ }^{g 2} \ldots R_{m}{ }^{g m}\)
(4) Discounted amount \(m\) of the tranche \(E T R_{\mathrm{m}}\) for the project completion date, \(E T R_{m}=T R_{m} \cdot R_{m}{ }^{g m}\),
(5) Amount of the project cost \(G_{0}\); the sum of discounted tranche amounts, reduced by the amount of self-financing, \(G_{0}=E T R_{1}+E T R_{2}+\ldots+E T R_{\mathrm{m}}-S F\)
(6) Length of the project development period, in days, \(R d=d_{1}+d_{2}+\ldots+d_{\mathrm{m}}\) or \(R d=T E_{\mathrm{m}}-T E_{0}\)
(7) Length of the project development period, in years, \(R g=\tfrac{R d}{365}\)
In addition to the price and length of the development period, the project has a planned duration, which begins with the deadline for the development of the project. The duration of the project has \(n\) internal time intervals for decursive presentation of the amount in Bosnia and Herzegovina convertible marks (KM) of the two sides of the business; DEBT (expenditures, expenses) and DEMAND (receipts, revenues). The amounts of the DEBT side \(D u\) are negative or zero (\(\mathrm{Du} \leq 0\) ), and the amounts of the DEMAND side \(P o\) are positive or zero (\(P o \geq 0\) ), at the end of the time interval with the internal rate \(p\). The sum of the amount DEBTED and the amount RECEIVED is BALANCE \(S\), i.e. \(S=D u+P o\), for the same term of the time interval of the internal rate \(p\).
The amount of the balance \(S\) in relation to the amounts owed \(D u\) and claimed Po, has the following relations:
(1) \(S \gt 0 S \gt 0\) for \(P o \gt |D u| P o \gt |D u|\),
(2) \(S=0 S=0\) for \(P o=|D u| P o=|D u|\),
(3) \(S \lt 0 S \lt 0\) for \(P o \lt |D u| P o \lt |D u|\).
In general, the discounted amount \(D S\) of the balance \(S\) in relation to the project price amount \(G_{0}\) and its term, determines the absolute payback \(A O\) of the project price for the internal rate \(p\) of the time interval.
The relative payback \(R O\) of the project price amount is the quotient of the absolute payback amount and the project price amount \(R O=\tfrac{A O}{G_{0}}\) or expressed as a percentage, \(R O \%=\tfrac{A O}{G_{0}} \cdot 100 \%\).
A series of balance amounts, \(S_{1}, S_{2}, \ldots, S_{\mathrm{n}}\) has a number of discounted amounts, \(D S_{1}, D S_{2}, \ldots, D S_{\mathrm{n}}\) for the start date of the project. There are a number of discounted amounts through partial sums \(D S_{1} ; D S_{1}+D S_{2} ; \ldots ; D S_{1}+D S_{2}+\ldots+D S_{\mathrm{n}}\), each of which, starting from the first, can repay the amount of the project price \(G_{0}\), if:
\(S U M_{1}=D S_{1} \geq G_{0} ; S U M_{2}=D S_{1}+D S_{2} \geq G_{0} ; S U M_{n}=D S_{1}+D S_{2}+\ldots+D S_{n} \geq G_{0}\).
By definition, the partial sums are absolute repayments, i.e.:
\(S U M_{1}=A O_{1}, S U M_{2}=A O_{2}, \ldots, S U M_{\mathrm{n}}=A O_{\mathrm{n}}\).
If within the planned duration, the project is not in operation for some reason, then all partial sums of the discounted balance amounts are equal to zero. In this case, it is necessary to calculate the increased project price amounts for the amounts of compound interest by capitalization periods. For negative balance amounts, the project beneficiary has losses with adequate, new loan amounts.
The best option is to repay the project price with the first partial amount \(S U M_{1}=D S_{1} \geq\) \(G_{0}\) ), that is, the discounted amount of the first balance \(S_{1}\).
The worse option is to repay the project price with the last partial amount, \(S U M_{\mathrm{n}}=D S_{1}+D S_{2}+\ldots+\mathrm{DS}_{\mathrm{n}} \geq G_{0}\).
The last partial amount cannot repay the amount of the project price, if it is:
\(D S_{1}+D S_{2}+\ldots+D S_{\mathrm{n}} \leq G_{0}\).
It is desirable that all amounts of the balance be positive, that is \(S_{1} \gt 0, S_{2} \gt 0, \ldots, S_{\mathrm{n}} \gt 0\) and that their discounted amounts can repay the amount of the project price up to and including the time interval of the ordinal number \(k\) for \(1 \leq k \leq n\). Some balance amounts may be equal to zero or negative amounts, with the proviso that all discounted balance amounts may repay the project cost. Therefore, a loan with \(k\) different, decursive annuities that are successively equal to the balance amounts has been contracted to repay the project price: \(\mathrm{a}_{1}=S_{1}, a_{2}=S_{2}, \ldots a_{\mathrm{k}-1}=S_{\mathrm{k}-1}, a_{\mathrm{k}}=S \leq S_{\mathrm{k}}\).
The last amount of the annuity \(a_{\mathrm{k}}\) is to cover the last balance of the debt. At the end of the time interval of the ordinal number \(k\), the project price was repaid, and the rest of the balance \(S_{\mathrm{k}}-a_{\mathrm{k}}=P_{1} \geq 0\) was the first amount of profit \(P_{1} \geq 0\). The following balance amounts, \(S_{\mathrm{k}+1}, S_{\mathrm{k}+2}, \ldots, S_{\mathrm{n}-\mathrm{k}}\), get the role of the amount of profit, \(P_{2}=S_{\mathrm{k}+1}, P_{3}=S_{\mathrm{k}+2}, \ldots\), \(P_{\mathrm{k}+1}=S_{\mathrm{n}-\mathrm{k}}\). Like balance amounts, profit amounts can be positive (greater than zero), negative (less than zero), and can have an amount equal to zero.
In general, in the loan repayment calculations, the amount of the \(1^{\text {st }}\) profit \(P_{1}\) presents the financial condition of the project beneficiary after the loan repayment, based on:
(1) For \(P_{1} \gt 0\), it follows that the loan has been repaid and the amount of the new balance is profit;
(2) For \(P_{1}=0\), it follows that the loan has been repaid and the amount of the new balance is profit;
(3) For \(P_{1} \lt 0\), it follows that the loan has been repaid and the amount of the new balance is profit or the amount of new debt, \(G_{000}=\left|P_{1}\right|\).
The amount of the new debt will be paid by the project beneficiary at the time of its arrival or will contract a new loan with the same or another creditor.
The following theory is based on the planned equalization of internal rates of return for balance amounts, with interest rates for annuity amounts, for periods of capitalization of ordinal numbers from 1 to \(k\).
2. Sizes and their relations for project cost repayment periods
During the duration of the project, the repayment of the project price amount is planned with the balance amounts in the first \(k\) capitalization periods of different durations with different interest rates.
Sizes of the 1st capitalization period:
(1) Start date of the \(1^{\text {st }}\) capitalization period, \(T_{0}=T E_{\mathrm{m}}\) in format dd.mm.yyyy hh: mm: ss
(2) Amount of the project cost, \(G_{0}\)
(3) Amount of the \(1^{\text {st }}\) balance; amount of 1st annuity, \(S_{1}=a_{1}\),
(4) Annual interest rate, \(p_{1}\)
(5) Annual interest rate factor, \(r_{1}=1+p_{1}\)
(6) Length of the \(1^{\text {st }}\) capitalization period, in days, \(x_{1}\)
(7) Length of the \(1^{\text {st }}\) capitalization period, in years, \(y_{1}=\tfrac{x_{1}}{365}\)
(8) Amount of the \(1^{\text {st }}\) discounted balance; the amount of the absolute repayment, for the term \(T_{0}, D S_{1}=S U M_{1}=A O_{1}=\tfrac{S_{1}}{r_{1} y_{1}}\)
(9) Relative payback of the project price, in percentage, \(R O_{1} \tfrac{A O_{1}}{G_{0}} \cdot 100 \%\)
Sizes of the \(\mathbf{2}^{\text {nd }}\) capitalization period:
(1) Start date of the \(2^{\text {nd }}\) capitalization period, \(T_{1}=T_{0}+x_{1}\)
(2) Amount of \(2^{\text {nd }}\) balance, amount of \(2^{\text {nd }}\) annuity, \(S_{2}=a_{2}\)
(3) Annual interest rate, \(p_{2}\),
(4) Annual interest factor, \(r_{2}=1+p_{2}\),
(5) Length of the \(2^{\text {nd }}\) capitalization period, in days, \(x_{2}\),
(6) Length of the \(2^{\text {nd }}\) capitalization period, in years, \(y_{2}=\tfrac{x_{2}}{365}\)
(7) Amount of the \(2^{\text {nd }}\) discounted balance for the term \(T_{0}, D S_{2}=\tfrac{S_{2}}{r_{1} y_{1, r_{2}} y_{2}}\)
(8) Sum of the \(1^{\text {st }}\) and \(2^{\text {nd }}\) discounted balance; the amount of the \(2^{\text {nd }}\) absolute payback, for the term \(T_{0}, S U M_{2}=A O_{2}=D S_{1}+D S_{2}=\tfrac{S_{1}}{r_{1} y_{1}}+\tfrac{S_{2}}{r_{1} y_{1, r_{2}} y_{2}} \leq G_{0}\)
(9) Relative payback of the project price, in percent, \(\mathrm{RO}_{2} \tfrac{\mathrm{AO}_{2}}{\mathrm{G}_{0}} \cdot 100 \%\)
…
Sizes \(\mathbf{k}^{\text {th }}\) of the capitalization period:
(1) Term of the beginning \(k^{\mathrm{th}}\) of the period of capitalization, \(T_{\mathrm{k}}=T_{\mathrm{k}-2}+x_{\mathrm{k}-1}\),
(2)Relation of the amount of the \(k^{\text {th }}\) balance, \(k^{\text {th }}\) annuity and the amount of the first profit, \(S_{\mathrm{k}}=a_{\mathrm{k}}+P_{1}\)
(3) Annual interest rate, \(p_{\mathrm{k}}\)
(4) Annual interest rate factor, \(r_{\mathrm{k}}=1+p_{\mathrm{k}}\)
(5) Length of the \(k^{\text {th }}\) capitalization period in days, \(x_{\mathrm{k}}\)
(6) Length of the \(k^{\text {th }}\) capitalization period in years, \(y_{k}=\tfrac{x_{k}}{365}\)
(7) Amount of the \(k^{\text {th }}\) discounted balance, for term \(T_{0}, D S_{k}=\tfrac{S_{k}}{r_{1}^{y_{1}}, r_{2}^{y_{2}} \ldots . r_{k}^{y_{k}}}\)
(8) Sum of the \(1^{\text {st }}\) till \(k^{\text {th }}\) discounted balance; the amount of absolute repayment, for the term \(T_{0}, S U M_{k}=A O_{k}=D S_{1}+D S_{2}+\cdots .+D S_{k}=\) \(S U M_{k}=A O_{k}=D S_{1}+D S_{2}+\cdots \cdot+D S_{k} \tfrac{S_{1}}{r_{1} y_{1}}+\tfrac{S_{2}}{r_{1} y_{1, r_{2}} y_{2}}+\cdots+\tfrac{S_{2}}{r_{1} y_{1,} r_{2} y_{2, \ldots, r_{k}} y_{k}} \leq G_{0}\)
(9) Relative payback of the project cost, in percent, \(R O_{k} \tfrac{A O_{k}}{G_{0}} \cdot 100 \%\),
(10) Term end of the \(k^{\text {th }}\) capitalization period, \(T_{\mathrm{k}}=T_{\mathrm{k}-1}+x_{\mathrm{k}}\)
(11) Length of the \(k^{\text {th }}\) capitalization period in days, \(D \mathrm{x}=x_{1}+x_{2}+\ldots+x_{\mathrm{k}}\), or \(D \mathrm{x}=T-T_{0}\)
(12) Length of the \(k^{\text {th }}\) capitalization period in years, \(D y=\tfrac{D x}{365}\)
The annuity \(a_{\mathrm{k}}\) for the term \(T_{0}\) has a discounted amount \(D a_{\mathrm{k}}=G_{0}-\left(D S_{1}+D S_{2}+\ldots+D S_{\mathrm{k}-1}\right.\).
The amount of the annuity \(a_{\mathrm{k}}\) determines the discounted amount of its discounted amount for the term \(T_{\mathrm{k}}\), i.e. \(a_{k}=D_{a_{k}} \cdot r_{1}{ }^{y_{1}} \cdot r_{2}{ }^{y_{2}} \ldots . r_{2}{ }^{y_{2}}\)
The first amount of profit \(P_{1}\) in the \(\mathrm{k}^{\text {th }}\) period of capitalization determines \(P_{1}=S_{\mathrm{k}}-a_{\mathrm{k}}\).
Therefore, it is planned to repay the project price with a loan amount \(G_{0}\) with interest amounts \(K_{\mathrm{i}}\), planned different annuity amounts \(a_{\mathrm{i}}\), different lengths of capitalization periods \(x_{\mathrm{i}}\) )and different interest rates \(p_{\mathrm{i}}\), for \(i \in\{1,2, \ldots, k\}\). Some or all of the stated amounts may be the same.
What follows is the introduction of the theory for making a repayment plan for this new loan repayment model.
3. Loan of various annuities, interest rates and capitalization periods
Primary loan amounts at the beginning and end of the 1st capitalization period:
(1) Amount of the loan at the beginning of the \(1^{\text {st }}\) capitalization period, \(G_{0}\)
(2) Term of the beginning of the loan capitalization, \(T_{0}\) in format dd.mm.yyyy hh: mm: ss
(3) Planned amount of annuities at the end of the \(1^{\text {st }}\) capitalization period, \(a_{1}=S_{1}\)
(4) Amount of the remaining debt at the end of the \(1^{\text {st }}\) capitalization period, \(G_{1}=G_{0} . r_{1}^{y_{1}}-a_{1}\)
(5) Amount of interest at the end of the \(1^{\text {st }}\) capitalization period, \(K_{1}=G_{0} \cdot\left(r_{1}{ }^{y_{1}}-1\right)\)
(6) Repayment amount at the end of the \(1^{\text {st }}\) capitalization period, \(b_{1}=a_{1}-K_{1}\)
(7) Amount repaid at the end of the \(1^{\text {st }}\) capitalization period, \(\mathrm{O}_{1}=\mathrm{b}_{1}\)
(8) Amount of the loan price at the end of the \(1^{\text {st }}\) capitalization period, \(C_{1}=G_{0} \cdot\left(r_{1}{ }^{y_{1}}-1\right)\) Primary loan amounts at the beginning and end of the \(\mathbf{2}^{\text {nd }}\) capitalization period:
(1) Start date of the 2nd capitalization period, \(T_{1}=T_{0}+x_{1}\)
(2) Planned amount of annuities at the end of the \(2^{\text {nd }}\) capitalization period, \(a_{2}=S_{2}\)
(3) Amount of the remaining debt at the end of the \(2^{\text {nd }}\) capitalization period, \(G_{2}=G_{1} \cdot r_{2}{ }^{y_{2}}-a_{2}\)
(4) Amount of interest at the end of the \(2^{\text {nd }}\) capitalization period, \(K_{2}=G_{1} \cdot\left(r_{2}{ }^{y_{2}}-1\right)\)
(5) Repayment amount at the end of the \(2^{\text {nd }}\) capitalization period, \(b_{2}=a_{2}-K_{2}\)
(6) Amount repaid at the end of the \(2^{\text {nd }}\) capitalization period, \(O_{2}=O_{1}+b_{2}\)
(7) Amount of the loan price at the end of the \(2^{\text {nd }}\) capitalization period,
\(C_{2}=G_{0} \cdot\left(r_{1}{ }^{y_{1}} r_{2}{ }^{y_{2}}-1\right)\) …
Primary loan amounts at the beginning and end of the \(\boldsymbol{k}^{\text {th }}\) capitalization period:
(1) Beginning date of the \(k^{\text {th }}\) capitalization period, \(T_{\mathrm{k}-1}=T_{\mathrm{k}-2}+x_{\mathrm{k}-1}\)
(2) Term of the end of the \(k^{\text {th }}\) capitalization period, \(T_{\mathrm{k}}=T_{\mathrm{k}-1}+x_{\mathrm{k}}\)
(3) Amount of the balance at the end of the \(k^{\text {th }}\) capitalization period, \(S_{\mathrm{k}} \geq a_{\mathrm{k}}\)
(4) Amount of annuity at the end of the \(k^{\text {th }}\) capitalization period, \(a_{\mathrm{k}}=S \leq S_{\mathrm{k}}\)
(5) Amount of the \(1^{\text {st }}\) profit at the end of the \(k^{\text {th }}\) capitalization period, \(P_{1}=S_{\mathrm{k}}-a_{\mathrm{k}}\)
(6) Amount of the remaining debt at the end of the \(k^{\text {th }}\) capitalization period, \(G_{k}=G_{k-1} \cdot r_{k}{ }^{y_{k}}-a_{k}=0\)
(7) Amount of interest at the end of the \(k^{\text {th }}\) capitalization period, \(K_{k}=G_{k-1} \cdot\left(r_{k}{ }^{{ }^{y_{k}}}-1\right)\)
(8) Amount of repayment at the end of the \(k^{\text {th }}\) capitalization period, \(b_{\mathrm{k}}=a_{\mathrm{k}}-K_{\mathrm{k}}\)
(9) Amount repaid at the end of the \(k^{\text {th }}\) capitalization period, \(O_{\mathrm{k}}=O_{\mathrm{k}-1}+b_{\mathrm{k}}\)
(10) Amount of the loan price at the end of the \(k^{\text {th }}\) capitalization period, \(C_{k}=G_{0} \cdot\left(r_{1}{ }^{y_{1}} r_{2}{ }^{y_{2}} \ldots r_{k}{ }^{y_{k}}-1\right)\)
The introduced theory is supported by spreadsheets (MS Excel) data processing without additional programming, shown on the example of one financial project.
4. Application of Excel in making an example of a financial project
Example 1:
With the term \(T E_{1}=24.03 .2017\) 09:54:22, the preparation of the financial project of loan financing began with the payment of the \(1^{\text {st }}\) tranche of the amount, \(T R_{1}=35,800 \mathrm{KM}\). After \(d_{1}=300\) days, the \(2^{\text {nd }}\) tranche of the amount was paid, \(T R_{2}=29,000 \mathrm{KM}\). After the next \(d_{2}=220\) days, the \(3^{\text {rd }}\) tranche of the amount \(T R_{3}=31,200\) KM was paid when the financing of the project was completed, and, after \(d_{3}=380\) days, its development was completed. There was no self-financing of project development costs, to reduce the loan amount.
Annual interest rates have been agreed for the capitalization of tranches and the calculation of the project price, successively: \(P_{1}=4.2 \%, P_{2}=4.5 \%\) and \(P_{3}=4.9 \%\). During the duration of the project, 4 time intervals are planned: \(x_{1}=150\) days, \(x_{2}=235\) days, \(x_{3}=310\) days and \(x_{4}=255\) days, to repay its price \(G_{0}\), planned decursive balance amounts; \(S_{1}=-7.420 \mathrm{KM}, S_{2}=0.000 \mathrm{KM}, S_{3}=59.530 \mathrm{KM}\), and the amount \(S \leq S_{4}=69.845 \mathrm{KM}\). Successive annual internal rates are planned: \(p_{1}=5.1 \%, p_{2}=5.3 \%, p_{3}=5.5 \%\), and \(p_{4}=5.9 \%\) for discounting and discounting the balance amount.
Using spreadsheet processor, calculate: the amount of the project price and the length of its development, the absolute and relative repayability of the project price amount with partial amounts of discounted balance amounts, the amount of the balance, covering the last remaining loan debt, and the amount of the \(1^{\text {st }}\) profit. Make a loan repayment plan for the project cost amount, with different annuity amounts, equal to different balance amounts, for planned equal internal balance rates and interest rates for annuities with different capitalization period lengths. Present the amounts of the primary loan amounts graphically.
The representation of the example model was done by the use of MS Excel data processor, through a spreadsheet containing 4 sheets. The cells with input values are highlighted in yellow color, the cells, containing nested and cloned values are white, and cells, presenting the output amounts are in green color.
In the \(1^{\text {st }}\) spreadsheet (Figure 1), the tranches were capitalized with the calculation of the project price amount \(G_{0}=104.062,292 \mathrm{KM}\) and the length of the project development period \(R d=900\) days or \(\mathrm{Rg}=2,465753425\) years.
Figure 1. Static form of he spreadsheet 1
In the \(2^{\text {nd }}\) spreadsheet (Figure 2), the decursive amounts of the balance were discounted for the period of the beginning of the duration and repayment of the project price. Calculations of the amount of absolute and relative payback of the project price for the first 4 capitalization periods were performed:
\[ \begin{aligned} & A O_{1}=A O_{2}=-7.269,124 \mathrm{KM} ; R O_{1}=R O_{2}=-6,986 \% \\ & A O_{3}=46.640,124 \mathrm{KM} ; R O_{3}=44,819 \% \\ & A O_{4}=107.488,520 \mathrm{KM} ; R O_{4}=103,292 \% \end{aligned} \]
Figure 2. Static form of spreadsheet 2
In the \(3^{\text {rd }}\) spreadsheet (Figure 3), the calculations of the amounts of the primary loan were performed, for the primarily known annuity amounts \(a_{1}=S_{1}=-7.420\) \(\mathrm{KM}, a_{2}=S_{2}=0.000 \mathrm{KM}\), and \(a_{3}=S_{3}=59.530 \mathrm{KM}\). The amount of coverage annuity was calculated: \(a_{4}=S=65.912,195 \mathrm{KM}\) for the rest of the loan debt, and the amount of the \(1^{\text {st }}\) profit \(P_{1}=3.932,805 \mathrm{KM}\).
The loan repayment period is \(R x=950\) days or \(R y=2,603\) years.
Figure 3. Static form of spreadsheet 3
In the \(4^{\text {th }}\) spreadsheet (Figure 4) there is a loan repayment plan with primarily known different, decursive annuity amounts, for 4 capitalization periods of different lengths and different interest rates. With the amounts of the primary values of the loan repayment plan, their graphic overview is harmonized, (Figure 4).
Table 4: Load repayment plan of different annuiѝes, interest rates and capitalizaѝon periods
Figure 4: Static form of spreadsheet 4 copied from List C
Presented example shows a way in which the data processors could support the implementation of one model, based on the introduced theory, of financial project.
The solution is working even if, during the project (as it happen in practice) there are changes in all or some parameters.
Conclusion
In the used literature on the topic of the model of making and loan repay ment of the price of making a financial project, the starting point is the relation \(G_{0}=\tfrac{a_{1}}{r}+\tfrac{a_{2}}{r^{2}}+\cdots+\tfrac{a_{k}}{r^{k}}\). In this relation, the loan amount \(G_{0}\) is equal to the sum of the discounted amounts \(k\) of annual, decursive annuities, for the term of the beginning of loan capitalization, and \(r\) is the annual, decursive interest factor. In this case all annual, decursive annuities \(a_{1}, a_{2}, \ldots, a_{\mathrm{k}}\) are higher than zero and are not related to the planned amounts of the balance, i.e. to the borrowing capacity of the project beneficiary, within its duration. The repayment of the loan is generally equal, annual, decursive annuities or equal repayments, which is not in line with the amounts of the balance, and especially with its negative amounts or amounts equal to zero.
The authors of the paper performed the necessary research and introduced the theory of a new model of loan financing of the amount of the price of a financial project. The new loan model is with different annuity amounts, different lengths of capitalization period and dif ferent interest rates. Annuity amounts are aligned with balance amounts, which means that annuities can have negative amounts and amounts, equal to zero. The last annuity of the loan repayment plan is the annuity to cover the last balance of the debt, which, with the last amount of the balance, determines the amount of the first profit from the project at the end of the loan repayment period. The negative amount of the first profit represents the amount of debt for the term of the end of the loan repayment period. The introduced theory was demonstrated by the use of MS Excel spreadsheet by representation of calculations, related to the Example 1. For each change of one, more or all input amounts, the spreadsheet automatically creates a new loan repayment plan with a graphical overview of the amounts of primary sizes.
REFERENCES
Danielle Stein Fairhurst, D., 2015. Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals 2nd Edition.
Benninga, S., 2006. Principles Finance With Excel. Oxford: Oxford Universty Press.
Benninga, S., 2014. Financial Modeling fourth edition.
Alastair, L., 2011. Day, Mastering Financial Mathematics in Microsoft Excel. Great Britain.
Joachim Häcker, Dietmar Ernst,Financial Modeling: An Introductory Guide to Excel and VBA Applications in Finance 1st ed. 2017 Edition.
Zdravka Aljinović, Z., Marasović, B. & Šego, B., 2011. Financijsko modeliranje. Split.