Present value of a loan

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. We use the PV function that has been introduced in earlier chapters, but with FV, the target value at the end of the term, generally set to zero:

=PV(I,n,S,0,type)

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, you can borrow:

=PV(15%,10,-200,0)

which works out to $1003.75, a little more than the $1000 in the previous examples.

Calculating repayments

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:

S = PMT(I,n,PV,0,type)

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:

=PMT(15%,10,1000,0)

which works out to -$199.25, which again should be compared to the result given earlier.

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:

=RATE(10,-200,1000,0)

which works out to 15% per annum, a value that again should be compared to the previous examples. 

Repayment schedules

Repayment loans are often arranged and then subject to much on-going scrutiny and possible rearrangement in the light of new financial circumstances. As a result the detailed workings of change in the debt and where the payments are going are usually of concern. The same calculations apply both to annuities and savings plans with minor modifications.

As well as knowing the values relating to the end of the loan or annuity it is also often necessary to know the balance at any time. This is just the future value at the specified time. That is, the balance of a loan of PV at I% repaid at S per period after n periods is:

=FV(I,n,S,PV)

For example, a loan of $1000 at 15% repaid at $200 per annum has a balance after 6 years of:

=FV(15%,6,-200,1000)

which is -$562.31.

As well as knowing the balance you might also want to know the amount of the principal that has been repaid. This is simply the difference between the PV and the balance, i.e. the reduction in the debt:

=PV-FV(I,n,S,PV)

For example, after 6 years of a loan of $1000 at 15% repaid at $200 per annum the amount of the loan repaid is:

=-1000-FV(15%,6,-200,1000)

which works out to -$437.69.

In the same way the amount of interest paid to date is easily found as the difference between the total amount paid and the amount of the loan repaid :

=n*S-PV+FV(I,n,S,PV)

For example, after 6 years of a loan of $1000 at 15% repaid at $200 per annum the amount paid in interest is:

=-6*200+1000+FV(15%,6,-200,1000)

which works out to -$762.31.

There are a range of spreadsheet functions that will give you the amount of the loan and the interest repaid in any given time period, but it is generally easier to use the basic functions we have already met.

All of the functions given above work out a cumulative total for the amount repaid and for interest. You can use these to find the amount in any given period, n say,  simply by taking the difference between the cumulative amount at time n-1 and at n.

For example: the amount of the debt repaid in time period n is just the difference between FV at period n-1 and period n:

=FV(I,n-1,S,PV)-FV(I,n,S,PV)

and the amount paid in interest in period n is just the difference in cumulative interest between period n-1 and n:

=(n-1)*S-PV + FV(I,n-1,S,PV)- n*S + PV + FV(I,n,S,PV)

which after a little algebra reduces to:

=FV(I,n-1,S,PV)-FV(I,n,S,PV)-S

You should see that this is just the difference between the payment and the amount paid off the debt in that period. This is exactly what you would expect as each payment goes in part to pay off the loan amount and part to pay the interest.

Formula summary

For a repayment loan type is set to zero or omitted and FV is zero: