Page 3 of 4
Repayment schedules
Repayment loans are often arranged and then subject to much ongoing scrutiny and possible rearrangement in the light of new financial circumstances. As a result the actual detailed workings of the change in the debt and where the payments are going are usually of concern. The same calculations apply to both annuities and savings plans with minor modifications but generally few bother.
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:
=PVFV(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:
=1000FV(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*SPV+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 you have.
All of the functions given above work out a cumulative total  e.g. cumulative total repaid and cumulative total 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 n1 and at n.
For example: the amount of the debt repaid in time period n is just the difference between FV at period n1 and period n:
=FV(I,n1,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 n1 and n:
=(n1)*SPV+FV(I,n1,S,PV) n*S+PV+FV(I,n,S,PV)
which after a little algebra reduces to:
=FV(I,n1,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.
To see these formulas in action see the example on the next page and the Janet Swift's loans and savings spreadsheets.
Formula summary
For a repayment loan type is set to zero or omitted and FV is zero:
FV the balance

=FV(I,n,S,PV,type) 
S the payment

=PMT(I,n,PV,0,type) 
n the term

=NPER(I,S,PV,0,type) 
I the rate

=RATE(n,S,PV,0,type,guess) 
Total repaid at n

=PVFV(I,n,S,PV)

Total interest at n 
=n*SPV+FV(I,n,S,PV)

<ASIN: 0273714066>
<ASIN:0143115766>
