F. Financial Literacy · Financial Decisions: Interest, Borrowing, Appreciation, Depreciation and Budgets

Using Spreadsheets and Code to Compare Borrowing Options

F1.3 A1About 75 minutes

Learning goals

I can build an amortization table in a spreadsheet using formulas and absolute cell references.
I can use a spreadsheet's PMT function to find a loan payment.
I can change one input at a time (rate, term or payment) to see its effect on the total cost.
I can read and run a short Python program that finds how long it takes to pay off a debt.

Words to know

amortization table
A table that shows, for each payment, how much goes to interest, how much goes to principal and the balance left.

cell reference
The address of a spreadsheet cell, such as B2.

absolute reference
A cell reference with $ signs, such as $H$1, that does not change when you copy a formula to other cells.

PMT function
A spreadsheet function that finds the payment for a loan: =PMT(rate per period, number of periods, −amount).

what-if analysis
Changing one input at a time to see how the result changes.

loop
Code that repeats steps, such as once for every month, until a condition is met.

Hook: let the computer do the repeating

In Lesson 6, Jesse's credit card took 14 months to pay off. Working that out month by month on paper is slow and easy to get wrong. A spreadsheet or a few lines of code can do all 14 months, or 300 months of a mortgage, in an instant. Then you can ask "what if?" questions: What if the rate were lower? What if I paid more each month?

F1.3 asks you to compare borrowing options using appropriate tools. Today you build two of them.

Part 1: an amortization table in a spreadsheet

We will model a $5 000 loan at 8% per year, compounded monthly, paid over 24 months.

Put the inputs in their own cells: H1 = 0.08 (rate), H2 = =ROUND(PMT(H1/12, 24, -5000), 2). The PMT function gives 226.14.
Make column headings in row 1: Month, Start balance, Interest, Principal, End balance.
Fill in row 2 (month 1) with the formulas below.
Fill in row 3 (month 2), then copy row 3 down to row 25.

The $ signs in $H$1 and $H$2 make them absolute references, so every row uses the same rate and payment cells.

Cell	Formula	Meaning
A2	1	month 1
B2	5000	starting balance
C2	=ROUND(B2*$H$1/12, 2)	interest for the month
D2	=$H$2-C2	part of the payment that goes to principal
E2	=B2-D2	balance after the payment
A3	=A2+1	next month
B3	=E2	this month starts where last month ended

The formulas for the first two rows. Copy row 3's formulas down (C3, D3 and E3 are copies of C2, D2 and E2).

Month	Start balance	Interest	Principal	End balance
1	$5 000.00	$33.33	$192.81	$4 807.19
2	$4 807.19	$32.05	$194.09	$4 613.10
3	$4 613.10	$30.75	$195.39	$4 417.71
…	…	…	…	…
23	$447.71	$2.98	$223.16	$224.55
24	$224.55	$1.50	$224.64	−$0.09

The first three rows and the last two rows of the spreadsheet. The −$0.09 at the end is a rounding effect (see Example 2).

Every payment is the same, but the split changes. Early payments are mostly principal with $33.33 of interest; by the end almost all of the payment is principal.

The balance drops a little faster each month, because less of each payment goes to interest. The line curves slightly downward instead of being perfectly straight.

Worked example

Example 1: Check a row by hand

Check month 2 of the table: start balance $4 807.19, rate 8%, payment $226.14.

Interest: 4807.19 × 0.08 ÷ 12 ≈ 32.048, which rounds to $32.05.
Principal: 226.14 − 32.05 = $194.09.
End balance: 4807.19 − 194.09 = $4 613.10. ✓ This matches the table.

The row is correct: $32.05 interest, $194.09 principal, $4 613.10 left.

Worked example

Example 2: The last payment and the total interest

The spreadsheet's last row ends at −$0.09. What should the last payment really be, and what is the true total interest?

A negative balance means the last payment was 9 cents too big. Lenders adjust the final payment: 226.14 − 0.09 = $226.05.
Total paid: 23 × 226.14 + 226.05 = 5201.22 + 226.05 = $5 427.27.
Total interest: 5 427.27 − 5 000 = $427.27. (The quick estimate 226.14 × 24 − 5000 = $427.36 is only 9 cents off.)

Last payment $226.05; total interest $427.27.

What-if analysis

Now change one input at a time. Type 0.06 into H1, and every cell updates. Change the 24 in the PMT formula (and the number of rows) to try a different term. Record the results in a table like this one (totals estimated as payment × number of payments − 5000).

Rate	Term	Payment	Total interest (about)
8%	24 months	$226.14	$427.36
6%	24 months	$221.60	$318.40
10%	24 months	$230.72	$537.28
8%	12 months	$434.94	$219.28
8%	36 months	$156.68	$640.48

What-if results for a $5 000 loan. Change only one thing at a time so you can see its effect.

Part 2: a Python program for paying off a credit card

A spreadsheet is great when you know the number of payments. When you want to know how many months it will take, a loop in code is handy. This program keeps going "while the balance is more than 0". Each pass through the loop is one month: add the interest, make a payment, and count the month.

def pay_off(balance, rate, payment):

months = 0

total = 0

while balance > 0:

interest = round(balance * rate / 12, 2)

balance = balance + interest

pay = min(payment, balance)

balance = round(balance - pay, 2)

total = total + pay

months = months + 1

return months, round(total, 2)

for p in [50, 100, 200]:

months, total = pay_off(1200, 0.1999, p)

print(p, months, total, round(total - 1200, 2))

Python code. Indented lines belong to the function or loop above them. min(payment, balance) makes sure the last payment is not bigger than what is owed.

50 31 1545.02 345.02

100 14 1350.09 150.09

200 7 1275.17 75.17

Output: monthly payment, months to pay off, total paid, total interest.

Doubling the monthly payment from $50 to $100 cuts the time from 31 months to 14, and the interest from $345.02 to $150.09.

Good habits with digital tools

Put inputs (rate, amount, term) in their own cells or variables, not buried inside formulas.
Check one row by hand so you trust the tool.
Change one input at a time and record what happens.
Remember that tools give exact-looking answers from example inputs. Real lenders may calculate interest daily and round differently, so expect small differences.

Hands-on activity

Build it and play with it

You need: a computer or tablet with a free spreadsheet program (for example Google Sheets, LibreOffice Calc or Excel), and optionally a free online Python editor (ask a grown-up which one to use).

Build the $5 000 amortization table above. Check that month 3 ends at $4 417.71.
Add a cell that totals the Interest column with =SUM(C2:C25). You should get $427.27, the true total interest. (The quick estimate 226.14 × 24 − 5 000 = $427.36 is 9 cents higher because it counts the overpaid 9 cents as interest.)
Do the what-if table: try two rates and two terms of your own.
Optional: type in the Python program. Change 1200 to a price you choose and add a payment of your own to the list.
Write two sentences: which change saved the most interest, and why?

Model answer: with =SUM(C2:C25) I got $427.27, which matches Example 2. Shortening the term from 24 to 12 months saved more ($208.08) than lowering the rate from 8% to 6% ($108.96), because the balance is paid off much faster.

Just for fun

A spreadsheet will happily calculate 300 months of mortgage payments in less than a second, and it never once complains that it is bored.

Your turn

Write the spreadsheet formula for the interest in cell C5 (the rate is in H1).

Why do we write $H$1 instead of H1 in the interest formula?

Month 3 starts at $4 613.10. Calculate its interest, principal and end balance (rate 8%, payment $226.14).

In the what-if table, how much interest does a 6% rate save compared with 8% (both 24 months)?

What does the program print for p = 100? Explain each number.

What is the job of min(payment, balance) in the code?

Write a PMT formula for a $12 000 loan at 6% for 5 years with monthly payments.

More practice: warm-up

How many periods go in the PMT formula for a 5-year loan with monthly payments?

Find month 1's interest on $5 000 at 8% compounded monthly.

In the spreadsheet, what does B3 = E2 mean?

In the code, what is the first month's interest on $1 200 at 19.99%?

In an amortization table, does the interest part of each payment go up or down over time?

More practice: on your own

Using the what-if table, find how much more interest the 10% loan costs than the 8% loan (both 24 months).

Compare the 12-month and 36-month options at 8%. Give the payment and interest for each, and one reason someone might choose each.

After 12 payments the balance is $2 599.56. How much principal has been paid? How much interest?

Change the last line of the code so it prints only the interest. What would it print for p = 200?

Explain why the balance graph is slightly curved instead of perfectly straight.

Challenge

Jesse wants to pay off the $1 200 card (19.99%) in exactly 6 months. Use PMT to find the payment, then the total interest.

Run (or trace) pay_off(1200, 0.1999, 25). The result is (98, 2432.5). Explain what this means and why it happens.

For the $5 000 loan at 8% for 24 months, which saves more interest: lowering the rate to 6%, or shortening the term to 12 months? Use the what-if table and explain.

Check your understanding

Quiz

Which spreadsheet formula finds the monthly payment for $5 000 at 8% for 24 months?

Why use $H$1 in a formula that will be copied down?

In an amortization table, what happens to the interest part of each payment over time?

A row starts at $1 000, the monthly rate is 0.5% and the payment is $100. What is the end balance?

In the Python program, what makes the loop stop?

What is the best way to do a what-if analysis?

For parents and teachers

Big idea: F1.3 asks students to compare borrowing options using appropriate tools. This lesson builds two: a spreadsheet amortization table (with PMT and absolute references) and a short Python loop. It also strengthens A1 (mathematical processes: selecting tools, reasoning and checking). It connects to the coding strand (C2) of this course.

Common difficulties:

Forgetting absolute references, so copied formulas point to empty cells and show 0 or errors.
Entering the yearly rate in PMT instead of the monthly rate (0.08 instead of 0.08/12).
Not checking one row by hand. A small formula error repeats in every row.

How to help: Free spreadsheet programs work well; no purchase is needed. Ask your teen to show you their what-if table and explain which change saves the most. If you use online banking, many banks have loan calculators you can compare with the spreadsheet (results may differ by a few cents because of rounding or daily interest).