C. Discrete Functions · Financial Applications: Interest, Present Value and Annuities

Using Technology: TVM Solver, Spreadsheets and Amortization Tables

C3.7 C3.6 C3.4About 75 minutes

Learning goals

I can use a TVM Solver or spreadsheet functions to find the future value, present value, payment, number of payments or rate of an ordinary simple annuity.
I can follow the sign convention: money paid out is negative and money received is positive.
I can build and read an amortization table, and explain why early payments are mostly interest.
I can use technology to compare the total interest paid on a loan with the original principal.

Words to know

TVM Solver
A time value of money tool with fields N, I%, PV, PMT, FV, P/Y and C/Y. Enter all but one and it solves for the missing one.

P/Y and C/Y
Payments per year and compounding periods per year. For a simple annuity they are equal.

sign convention
Money that leaves you (a deposit, a loan payment) is entered as negative; money you receive is positive.

amortization table (schedule)
A table that shows, for each payment, the interest, the principal repaid and the balance owing.

outstanding balance
The amount still owed on a loan after a given payment.

Let the machine do the arithmetic

Banks, car dealers and financial planners do not type annuity formulas into a scientific calculator; they use financial calculators, TVM Solvers and spreadsheets. Using these tools well is part of the curriculum. You still need to understand the formulas, so you can set up problems, check answers and spot mistakes. In this lesson every technology answer is checked with a formula you already know.

The TVM Solver

Graphing calculators (for example, the Finance application on TI-83 and TI-84 models) and many free online financial calculators have a TVM Solver with these fields:

N: total number of payments
I%: annual interest rate as a percent (type 6, not 0.06)
PV: present value
PMT: regular payment
FV: future value
P/Y: payments per year; C/Y: compounding periods per year (equal for a simple annuity)
PMT: END for an ordinary annuity (payments at the end)

Enter every value you know, put the cursor on the unknown and solve. Signs matter: deposits and loan payments are negative (money out); a loan received or a savings total collected is positive (money in).

Kiran's $150-a-month plan from Lesson 8. The TVM Solver gives the same future value as the formula: about $10 465.50.

Spreadsheet function	What it finds	Example	Result
=FV(rate, nper, pmt)	future value	=FV(0.06/12, 60, -150)	10 465.50
=PV(rate, nper, pmt)	present value	=PV(0.069/12, 60, -425)	21 514.57
=PMT(rate, nper, pv)	payment for a loan	=PMT(0.0624/12, 60, 24000)	−466.67
=PMT(rate, nper, 0, fv)	payment for a savings goal	=PMT(0.036/12, 48, 0, 8000)	−155.20
=NPER(rate, pmt, pv, fv)	number of payments	=NPER(0.048/12, -200, 0, 5000)	23.88
=RATE(nper, pmt, pv, fv)	rate per period	=RATE(60, -300, 0, 20000)	0.003515

These functions work in Google Sheets, Excel and LibreOffice Calc. The rate is the rate per period and nper is the number of payments. A negative result means money paid out.

Worked example

Example 1: How many deposits? (number of payments)

Zoe deposits $200 at the end of every month at 4.8% compounded monthly. How many deposits until she has at least $5 000?

Technology: =NPER(0.004, -200, 0, 5000), or a TVM Solver with I% = 4.8, PV = 0, PMT = −200, FV = 5000, P/Y = C/Y = 12, gives N ≈ 23.9.
Check by guess and check with the formula: 200[(1.004)²³ − 1] ÷ 0.004 ≈ $4 808.18 (not enough); 200[(1.004)²⁴ − 1] ÷ 0.004 ≈ $5 027.42 (enough).
So she needs 24 deposits (2 years).

24 monthly deposits.

Worked example

Example 2: What rate? (interest rate)

What annual rate, compounded monthly, turns $300 at the end of every month for 5 years into $20 000?

There is no simple formula for i here, so use technology: =RATE(60, -300, 0, 20000) or a TVM Solver solving for I%.
The rate per month is about 0.003515, so the annual rate ≈ 12 × 0.003515 ≈ 4.22% compounded monthly.
Check with guess and check: at i = 0.0035 the FV is $19 990.78 (just short); at i = 0.004 it is $20 298.05 (too much). The rate per month is just over 0.0035 ✓.

About 4.22% compounded monthly.

Amortization: where does each loan payment go?

Take an $18 000 car loan at 5.4% compounded monthly, repaid over 4 years. The payment is R = 18000(0.0045) ÷ [1 − (1.0045)⁻⁴⁸] ≈ $417.80.

Each month:

Interest = balance × 0.0045 (rounded to the cent)
Principal repaid = payment − interest
New balance = old balance − principal repaid

In a spreadsheet, with the balance in column B: interest **=ROUND(B20.0045, 2), principal =D2-C2, new balance =B2-E2*, and the next row starts with the new balance. You built a table like this in Grade 9; now you know where the payment itself comes from.

Payment	Balance before	Interest	Payment	Principal	Balance after
1	$18 000.00	$81.00	$417.80	$336.80	$17 663.20
2	$17 663.20	$79.48	$417.80	$338.32	$17 324.88
3	$17 324.88	$77.96	$417.80	$339.84	$16 985.04
…	…	…	…	…	…
47	$829.81	$3.73	$417.80	$414.07	$415.74
48	$415.74	$1.87	$417.61	$415.74	$0.00

The first three and last two rows. Because payments are rounded to the cent, the final payment is adjusted slightly so the balance ends at exactly $0.

The balance curve sits just above the dashed straight line: it falls a little more slowly at first and faster at the end. Early payments are mostly interest; later payments are mostly principal.

Each year the same total is paid ($5 013.60 in each of the first three years), but the share going to interest shrinks from $870.44 in Year 1 to $143.60 in Year 4.

Worked example

Example 3: Total interest compared with the principal

For the $18 000 loan above, find the total interest and compare it with the principal. Then use technology to compare a 6-year term at the same rate.

From the table: 47 payments of $417.80 and a last payment of $417.61. Total paid ≈ $20 054.21.
Total interest ≈ $20 054.21 − 18 000 = $2 054.21, about 11.4% of the principal.
6-year term: =PMT(0.0045, 72, 18000) gives about $293.24. Total interest ≈ 72 × 293.24 − 18 000 ≈ $3 113.28, about 17.3% of the principal.

4 years: about $2 054.21 of interest; 6 years: about $3 113.28.

Checking technology

Technology is only as good as what you type. Before trusting a result: (1) check that P/Y and C/Y match the problem, (2) check the signs, (3) estimate (60 deposits of $150 must give a bit more than $9 000), and (4) when possible, check with a formula.

Just for fun

A TVM Solver has seven fields and can solve for any one of five money values. It is basically a very polite calculator that refuses to help until you tell it which way your money is going (in or out).

Hands-on activity

Build an amortization table

You need: a free spreadsheet app (Google Sheets, LibreOffice Calc or Excel).

In cell H1 type the loan amount (18000), in H2 the rate per month (=0.054/12), in H3 the number of payments (48), and in H4 the payment: =ROUND(PMT(H2, H3, -H1), 2). (With -H1, the payment comes out positive.)
Headings in row 1: Payment, Balance before, Interest, Payment, Principal, Balance after.
Row 2: A2 = 1, B2 = =H1, C2 = =ROUND(B2*$H$2, 2), D2 = =$H$4, E2 = =D2-C2, F2 = =B2-E2.
Row 3: A3 = =A2+1, B3 = =F2, and copy C2:F2 down into C3:F3. Then copy row 3 down to row 49.
Find the total of column C (total interest). Then change H3 to 72 and watch everything update.

What to look for: the payment is $417.80. The last balance will be a few cents away from zero (here −$0.19, because each payment was rounded up slightly); a lender would make the final payment $417.61 instead. The total interest is about $2 054 for 48 payments, and about $3 113 for 72 payments. (Your spreadsheet may differ by a few cents depending on rounding.)

Your turn

Write the TVM Solver entries to find the future value of $150 at the end of every month for 5 years at 6% compounded monthly.

What spreadsheet formula gives the monthly payment on a $24 000 loan at 6.24% compounded monthly for 5 years? What is the result?

Zoe saves $200 a month at 4.8% compounded monthly. How many deposits until she has at least $5 000? Check with the formula.

In the amortization table above, how much of the first payment is interest, and how much is principal?

In the same table, what is the balance after the second payment?

For the $18 000 loan, find the total interest paid over 4 years and express it as a percent of the principal.

More practice: warm-up

On a TVM Solver, what does I% = 5.4 mean?

For a simple annuity with monthly payments, what are P/Y and C/Y?

You borrow money. Is PV entered as positive or negative?

In an amortization table, how is each month's interest found?

Why is the last payment of a loan sometimes slightly different from the others?

More practice: on your own

What annual rate, compounded monthly, turns $300 at the end of every month for 5 years into $20 000? Explain how you could check it without a RATE function.

Use the third row of the amortization table to show how the balance after payment 3 is found.

For the $18 000 loan at 5.4% compounded monthly, compare the total interest for 4-year and 6-year terms.

A student enters a TVM Solver with PV = 5000 and PMT = 200 (both positive) and gets an error. What is wrong?

How much is still owed on the $18 000 loan after 24 payments? Use the fact that the balance owing equals the present value of the remaining payments.

Challenge

Explain why early loan payments are mostly interest, using the amortization rule interest = balance × i.

A $250 000 loan at 5.4% compounded monthly is repaid over 25 years. Use the payment formula (or PMT) to find the monthly payment and compare the total interest with the principal. (Note: real Canadian fixed-rate mortgages usually state their rate compounded semi-annually, which makes them general annuities; this question uses monthly compounding to keep it a simple annuity.)

Find the outstanding balance of the $18 000 loan after 36 payments in two ways: the present value of the remaining 12 payments, and the future value of the loan minus the future value of the payments made.

Check your understanding

Quiz

On a TVM Solver, you deposit $100 at the end of every month to save money. How should PMT be entered?

For a simple annuity with quarterly payments, P/Y and C/Y are:

Which spreadsheet function finds the monthly payment on a $10 000 loan at 6% compounded monthly for 3 years?

In an amortization table, the interest portion of each payment:

A loan balance is $8 000 at 6% compounded monthly, and the payment is $300. How much of the next payment is principal?

A TVM Solver says N = 23.8 for a savings goal with monthly deposits. How many deposits are needed?

For parents and teachers

Big idea: This lesson uses technology for C3.7 (future value, present value and payment of annuities, including comparing total interest with the principal), C3.6 (investigating changes) and C3.4 (finding the number of periods with technology). A free spreadsheet is enough; a graphing calculator is not required.

How to help: If your teen builds the amortization spreadsheet, ask them to explain why the interest part gets smaller and the principal part gets larger from one row to the next, and to find the row where the interest first drops below $50. It is a great check of understanding.

Common difficulties:

Signs in TVM Solvers and spreadsheet functions.
Typing the annual rate where the function wants the rate per period (spreadsheets) or the reverse (TVM Solver I% is annual).
Forgetting to set P/Y and C/Y.
Trusting technology without an estimate or a formula check.