Exploring Repayment Loans 
Written by Janet Swift  
Page 2 of 2
Repayment schedulesRepayment 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:
For example, a loan of $1000 at 15% repaid at $200 per annum has a balance after 6 years of:
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:
For example, after 6 years of a loan of $1000 at 15% repaid at $200 per annum the amount of the loan repaid is:
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 :
For example, after 6 years of a loan of $1000 at 15% repaid at $200 per annum the amount paid in interest is:
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:
and the amount paid in interest in period n is just the difference in cumulative interest between period n1 and n:
which after a little algebra reduces to:
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. {loadposition finfunc} Formula summaryFor a repayment loan type is set to zero or omitted and FV is zero:
A schedule calculatorUsing these formulae it is quite easy to put together a spreadsheet that will detail the state of a repayment loan at any stage in its life. The only real problem is allowing for the required number of months in the table. First enter all of the text and Loan, Rate and Term data as shown in below.
The basic loan spreadsheet
Enter 1 in A8 to start the formulae off and enter:
The first formula computes the state of the account at the end of each year. The second works out the total amount paid by the end of the year and the final two work out the amount of the debt paid off and the amount paid in interest to date. Next fill column A with as many year numbers as you are interested in and copy the formulae in B8:G8 into the same number of rows. You can see an example of a 25year loan in Figure 1. Notice the way that the first year's payments only succeeds in paying off $21.02 of the principal although over $4400 is paid! Over the 25year period of the loan more than $130,000 is paid in interest. If you look a graph of the balance against time you can see the characteristic shape of the repayment loan. The principal is paid off only slowly at first but the speed of repayment increases dramatically in the final years.
The rate of decrease of a repayment loan Notice that there is no real decrease in the amount owed for the first 15 years of the loan. This is the reason why additional payments early in the life of a loan can greatly reduce the total cost.
Now available as a print bookThe draft chapters are still available on the website Financial Functions
Now available as a print bookThe draft chapters are still available on the website Spreadsheets take the hard work out of calculations, but you still need to know how to do them. Financial Functions with a spreadsheet is all about understanding and reasoning, using a spreadsheet to do the actual calculation.
<ASIN:1871962013>
To be informed about new articles on I Programmer, install the I Programmer Toolbar, subscribe to the RSS feed, follow us on, Twitter, Facebook, Google+ or Linkedin, or sign up for our weekly newsletter.
Comments
or email your comment to: comments@iprogrammer.info <ASIN: 0470475366> <ASIN:1118510100> <ASIN:0735672431> <ASIN:0789748576> <ASIN:0470178892> <ASIN:0195301501> <ASIN:1119067510>
