Page 4 of 4
A schedule calculator
Using 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 Figure 1.
Figure 1. The basic loan spreadsheet
Enter 1 in A8 to start the formulae off and enter:
=FV($B$2/12,A8*12,$B$5, $B$1) in B8
=$B$5*A8*12 in C8
=$B$1+B8 in D8
=-(C8-D8) in E8
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 25-year 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 25-year period of the loan more than $130,000 is paid in interest.
If you look a graph of the balance against time - see Figure 2 - 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.
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.
Figure 2: The rate of decrease of a repayment loan
More Financial Functions: