In the fifth box (E3) type: = E2 - D3
Now, you don't want to be doing this 360 times. So the program will do the rest for you:
Highlight the 3 boxes you just did (C3,D3,E3), let go, then drag the far right corner down to the
bottom of your spread sheet. Your balance should be 0 (it may not be exactly zero but could be a
small number due to round off error).
Below the last row, in the first column, type: totals:
In the C column type in: = sum(C2:Ck)where k is the column number of the last payment cell
(this will add all the numbers in this column starting with C2 through Ck)
Do the same in the D column - type: =sum(D2:Dk) this should add up to a number that is very
close to your loan amount
You can use the Excel program to make the following graphs.
For each year of your loan (every 12 payments) find the interest amount and plot it as the y-value
with the year number as the x-value. On the same axes, plot the principal amount versus year
(use a different color). Indicate where the two graphs cross and discuss what happens after this
1. What happens to the interest as you go down this column?
2. What happens to the amount allocated to principal as you go down that column?
3. After how many months was the balance of the loan half of the original amount? (The answer
will not be half-way through the loan period)