Page 2 of 4
The Excel/Open Office function:
will calculate the Future Value FV of a repayment loan of PV being paid back at a rate of S per time period with interest at I%. As explained before type is zero if the payment is at the start of the period and one if it is at the end.
For a repayment loan the payment is at the start of the period and isn't involved in the interest rate calculation until the following period. Also PV is positive and S (PMT in Excel's terminology) is negative.
In other words if you borrow PV at an interest rate of I% per period and pay the loan back at S per period then FV is the state of the account i.e. the debt after nper periods.
For example, if you borrow $1000 at 15% per annum paid annually then if you repay $200 per year after the first year the account stands at:
or -$950 i.e. the debt has reduced by $150. After 1 year the debt has increased to -$1150 and you have repaid $200 making a total of -$950 as given. In 10 years the debt is reduced to
which works out to $15.19 and a positive value indicates that the loan has been over paid by this amount - i.e. the loan was fully repaid sometime during the 10th year.
Number of periods to repay the loan
The FV function can be used to give the balance after any number of periods but it is also important to know when the loan is repaid - i.e. when the FV becomes zero.
To calculate the number of periods needed to reduce the Future Value to zero you can use the NPER function that has already been introduced in earlier chapters but this time with the FV set to zero:
For example, how long will it take to repay a loan of $1000 at 15% at a rate of $200 per year? The answer is:
which works out to approximately 9.92 years. This agrees with the earlier calculation that the same loan is repaid before 10 years are up.
In the case of a repayment loan the present value represents the amount that can be borrowed if you can find a loan at a given rate, term and payment.I
Most spreadsheets provide a financial function to work this out. The PV function has been introduced in earlier chapters but in this case FV is generally set to zero:
gives the amount you can borrow if the interest rate is I and the loan is repaid with n periodic payments of S.
For example, if you can pay $200 per month for 10 years at 15% per annum then you can borrow:
which works out to $1003.75 - a little more than the $1000 in the previous examples.
Another important repayment calculation is the amount needs to be paid to clear a loan at a given rate and term. As repayment loans are generally arrange on the basis of the amount for a given term this is particularly important. This is achieved using the PMT function again with FV set to 0::
where I is the interest rate, n the number of periods, PV the size of the loan.
For example, how much do you have to pay to repay a loan of $1000 at 15% in exactly 10 years? The formula needed is:
which works out to -$199.25, which again should be compared to the result given earlier.
The interest rate needed
A far less common calculation for a repayment loan is to find the interest rate needed to repay the loan in a given time at a given rate. The reason is simply that usually you are in search of the lowest rate available not the one that fits the time period and repayments exactly. However the calculation is worth exploring for completeness and just in case it is needed as a way of judging the value of a given financial arrangement in terms of the equivalent repayment loan rate.
Once again, as in the case of the savings plan, solving for I turns out to be impossible and iteration has to be used. In most cases you can simply use the RATE function with FV set to zero:
I= RATE(n, S,PV,0,type,guess)
where n is the number of periods, S the payment, PV the loan, type is zero or one depending on whether the payments are the start or the end of the time periods and guess is a guess at what the interest rate is. If you don't specify a guess then a random value is used as the starting value.
For example,what rate do you need to completely repay a loan of $1000 over 10 years of repaying $100 per year? The formula you need is:
which works out to 15% per annum, a value that again should be compared to the previous examples.