These two examples show how to create an Excel-like spreadsheet for creating sinking fund and amortization schedules using the TABLE facility of the graphing calculator.
Annuity: $100 per month into an account earning 9% compounded monthly.
Find the interest earned during each year.
| The x-variable will stand for the year. We will define three
functions:
y1 = future value at end of current year y2 = future value at end of previous year y3 = interest = increase in future value - payments = y1 - y2 - 1200 To see how to define functions, see the lesson on GRAPHING. Opposite is the screen after entering Y1 and Y2.
You can't see the whole function without using the => key, but here's what
has been entered:
|
|
| Now to define y3. Place the cursor at the definition point for
y3. Now
F2, 1 (to select y1), etc. to give us: |
 |
| To see the "spreadsheet", press TABLE F1: |  |
| To see y3, use the =>: |  |
| The y3 column shows interest paid in each year. |
Annuity: Amortize a $6000 debt at 12% compounded monthy over 3 years.
We use the Present Value formula (not shown) to compute the monthly payment
= $199.29, or $2391.48 total payments per year.
| As above, the x-variable will stand for the year. We will define
four functions:
y1 = present value at end of current year y2 = present value at end of previous year y3 = reduction in principal owed = y1 - y2 y4 = interest paid = payments - y3 |
|
| See above discussion for how to get
Y-variables into the formulas.
Opposite are the functions. In their entirety, the first two formulas are:
|
 |
| To see the "spreadsheet", press TABLE F1.
Opposite are spreadsheet columns X (year), y1 (current PV) and y2 (previous PV). Note: ignore the rows after 3, when the loan has been repaid. |
 |
| Here are columns y3 (reduction in amount owed) and y4 (interest paid): |  |
| These columns tell us how each year's total payment of $2391.48 was
divided up into:
y3 = reduction of loan y4 = interest payment |
