F. Financial Literacy · Money and Finances: Payments, Financial Plans, Budgets, Interest and Smart Spending

Exploring Interest with Spreadsheets and Planning for the Long Term

F1.4 A1About 50 minutes

Learning goals

I can build a spreadsheet that shows compound growth year by year.
I can change the rate, time and deposits in a spreadsheet to compare results.
I can use the Rule of 72 to estimate how long money takes to double.
I can explain how interest affects long-term financial planning, for saving and for borrowing.

Words to know

cell reference
The name of a spreadsheet cell, like B4 (column B, row 4).

absolute reference
A cell reference with $ signs, like $B$1, that stays the same when a formula is filled down.

fill down
Copying a formula into the cells below it so the spreadsheet repeats the calculation for every row.

Rule of 72
A quick estimate: 72 ÷ the yearly interest rate (as a whole number) ≈ the number of years for money to double.

regular deposit
Money added to savings on a schedule, like $50 every month or $600 every year.

inflation
A general rise in prices over time, so the same money buys less.

Why use a digital tool?

In the last lesson you found compound interest with a formula and with year-by-year tables. A 5-year table is fine by hand. A 40-year table, at three different rates, with a deposit every year? That's hundreds of calculations.

A spreadsheet (like Google Sheets, Excel, LibreOffice Calc or Numbers) does all of it in seconds. Change one number, and every year updates. Online compound interest calculators do the same thing. The Financial Consumer Agency of Canada, a federal government agency, offers free money tools and calculators on its website.

Cell	Type this	Meaning
A1	Rate	A label
B1	0.05	The yearly rate (5%). Change it here to try other rates.
A3 and B3	Year, Balance	Column headings
A4	0	Start
B4	1000	The principal
A5	=A4+1	Next year number
B5	=B4*(1+$B$1)	Last year's balance × (1 + rate)
A5:B5	Fill down to row 34	Gives years 1 to 30

A compound interest spreadsheet. The $ signs in $B$1 keep the formula pointing to the rate cell when you fill down.

Worked example

Example 1: Try three rates

Use the spreadsheet for $1 000 over 30 years. Change B1 to 0.02, then 0.05, then 0.08. Record the balance in year 30.

B1 = 0.02: year 30 shows $1 811.36 (that's 1 000 × 1.02³⁰).
B1 = 0.05: year 30 shows $4 321.94.
B1 = 0.08: year 30 shows $10 062.66.
The 8% rate is 4 times the 2% rate, but its interest (9 062.66) is more than 11 times as much (811.36).

Over a long time, a few percentage points make an enormous difference: from about $1 800 to over $10 000.

$1 000 after 30 years, compounded annually at 2%, 5% and 8% (rounded to the nearest dollar). Bar lengths are drawn to scale.

Worked example

Example 2: The Rule of 72

About how long does money take to double at 6%? At 8%? At 3%? Check with a spreadsheet or calculator.

6%: 72 ÷ 6 = 12 years. Check: 1 000 × 1.06¹² ≈ $2 012.20. ✓ About double.
8%: 72 ÷ 8 = 9 years. Check: 1 000 × 1.08⁹ ≈ $1 999.00. ✓
3%: 72 ÷ 3 = 24 years. Check: 1 000 × 1.03²⁴ ≈ $2 032.79. ✓

The Rule of 72 is a quick estimate, close enough for planning. It works for debts too: at 20%, a debt with no payments doubles in about 72 ÷ 20 ≈ 3.6 years.

Worked example

Example 3: Starting early

In this example, both people earn 6% a year compounded annually until age 65. Sam invests $1 000 at age 13. Tia invests $2 000 at age 25. Who has more at 65?

Sam: 65 − 13 = 52 years. 1 000 × 1.06⁵² ≈ $20 696.89.
Tia: 65 − 25 = 40 years. 2 000 × 1.06⁴⁰ ≈ $20 571.44.
Sam has 20 696.89 − 20 571.44 = $125.45 more, after investing half as much.

Sam. Twelve extra years of compounding beat twice the money. Starting early is one of the most powerful parts of a long-term plan. (Real investments that might earn around 6% usually come with risk and are not guaranteed.)

Sam puts in half as much money as Tia, but his money grows for 12 more years, so he ends up with a little more. Time is the strongest ingredient.

Year	Balance at the end of the year
1	$600.00
2	$1 230.00
3	$1 891.50
4	$2 586.08
5	$3 315.38
10	$7 546.74

Saving $600 at the end of each year at 5%. Spreadsheet formula: =B4*(1+$B$1)+600 (with B4 = 0).

Worked example

Example 4: Regular deposits

Nia saves $600 at the end of every year at 5% compounded annually. How much does she have after 10 years? How much of that is interest?

Change B4 to 0 and the formula in B5 to **=B4(1+$B$1)+600*, then fill down.
Each year: last balance × 1.05, then add the new $600. Year 2: 600 × 1.05 + 600 = 1 230. Year 3: 1 230 × 1.05 + 600 = 1 891.50.
Year 10 shows $7 546.74.
She deposited 10 × 600 = $6 000. Interest: 7 546.74 − 6 000 = $1 546.74.

$7 546.74, including $1 546.74 of interest. Regular saving plus compounding is how many people reach long-term goals.

What this means for long-term planning

Start early. Time is the strongest ingredient in compound growth.
Save regularly, even small amounts.
Compare rates (and fees). A difference of 1% or 2% matters a lot over decades.
Avoid high-interest debt. Compounding works against borrowers. A credit card balance at about 20% can double in under 4 years if nothing is paid.
Think about inflation and risk. If prices rise faster than your savings grow, your money buys less. Investments that may earn more usually carry more risk.
Use tools to test "what if?" What if the rate drops? What if I save $10 more a month?

Spreadsheet tips

Every formula starts with =.
Use $B$1 for the rate so it doesn't shift when you fill down.
Format money cells to 2 decimal places (or as currency).
Make a line graph of Year vs. Balance to see compound growth curve upward.

Wonder and test

Mathematicians ask "What if...?" and test it. What if the rate were 1% higher? What if I started 5 years later? Make a prediction first, then change the spreadsheet and compare. Surprises are where learning happens.

Just for fun

A spreadsheet's favourite move is fill down. Thirty years of compound interest, one drag of the mouse, and it doesn't even need a snack break.

Hands-on activity

Build your own growth spreadsheet

You need: a computer or tablet with a spreadsheet app (with a grown-up's permission), or a calculator and paper.

Build the spreadsheet from the table above for $1 000 at 5% for 30 years.
Check year 1 (1 050), year 2 (1 102.50) and year 10 (about 1 628.89).
Add a column C for simple interest: in C4 type 1000 and in C5 type **=C4+1000$B$1*, then fill down. Compare columns B and C.
Try rates of 2%, 5% and 8%. Record year 30 for each.
Make a line graph of both columns.
Change the plan: add $100 each year (=B4*(1+$B$1)+100). How much more do you have after 30 years?

What to look for: correct year 1 and year 2 values, a compound curve that bends upward while simple interest is a straight line, and a sentence explaining what they noticed.

Your turn

Use the Rule of 72: about how long does money take to double at 4%?

The balance is in B2. Write a formula for next year's balance at 3%.

Use a calculator or spreadsheet: $1 000 at 4% compounded annually for 18 years. Was the Rule of 72 close?

Why does starting early matter so much in a long-term savings plan?

Find the amount for $500 at 7% compounded annually for 10 years.

In the spreadsheet, the rate is in B1. How can you see what happens at 6% instead of 5%?

A credit card balance of $1 000 at 20% compounded annually has no payments. About how long until it doubles?

More practice: warm-up

Rule of 72 at 6%: about how many years to double?

Rule of 72 at 9%: about how many years to double?

What does fill down do in a spreadsheet?

After 30 years, which grows more: $1 000 at 2% or $1 000 at 8%?

What does the cell name A1 mean?

More practice: on your own

Find the amount and the interest for $2 000 at 5% compounded annually for 20 years.

Compare $1 000 for 24 years at 3% and at 6%. What do you notice?

Saving $600 at the end of each year at 5%, the balance after 5 years is $3 315.38. How much was deposited, and how much is interest?

Use the Rule of 72 to estimate $1 000 at 9% after 16 years. Then calculate it exactly.

In this example, savings earn 1.5% a year and prices rise 2% a year (inflation). Is the saver's buying power going up or down? Explain.

Challenge

A $5 000 student loan charges 7% compounded annually, and no payments are made for 4 years (example). How much is owed, and how much is interest?

How much would you need to invest today at 5% compounded annually to have $10 000 in 10 years?

Plan A: invest $1 000 now at 5% for 10 years. Plan B: invest $100 at the end of each year for 10 years at 5%. Both put in $1 000 in total. Which grows more, and why?

Check your understanding

Quiz

Using the Rule of 72, about how long does money take to double at 12%?

The rate is in cell B1 and last year's balance is in B7. Which formula gives this year's balance?

Why do people add $ signs, as in $B$1?

Which change has the biggest effect on long-term growth of the same deposit at the same rate?

Savings earn 3% a year while inflation is 4%. What happens to buying power?

$1 000 at 8% compounded annually for 9 years is about:

For parents and teachers

Big idea: F1.4 asks students to determine interest growth using digital tools, and to explain the impact interest has on long-term financial planning. Students build a spreadsheet, compare rates, use the Rule of 72, and see how starting early, regular deposits, inflation and debt affect a plan. All rates are examples; real returns on investments are not guaranteed.

How to help at home:

Build the spreadsheet together. Google Sheets is free with a Google account; LibreOffice Calc is free to download; Excel or Numbers also work.
Ask "what if" questions and let your child test them.
Share, in general terms, why your family saves regularly or avoids carrying a credit card balance.

Common difficulties:

Formulas without =, or forgetting the $ signs so the rate cell shifts when filled down (the balance then becomes 0 or wrong).
Using =B4$B$1,* which gives only the interest, not the new balance.
Thinking the Rule of 72 is exact. It's an estimate that works best for rates from about 2% to 15%.