Understand the idea.
Make each column meaningful
Give each spreadsheet column a clear role: A is period, B opening balance, C decimal rate, D end deposit and E closing balance. With interest before the deposit, the row-2 formula is =B2*(1+C2)+D2 in E2.
The next opening must link to the previous close: enter =E2 in B3. Copying the closing formula down should change its references to =B3*(1+C3)+D3, using that row’s rate and deposit.
Check the first rows independently
Check the first two rows by hand before filling a long table. Confirm that percentages are stored as decimal rates and that each new opening equals the preceding close. Currency formatting changes appearance, not the calculation itself.
Record the event order beside the model: interest first, end deposit second. A formula with the deposit inside the interest multiplication represents a different timing assumption, even if the resulting table looks orderly.
| Period | Opening | Rate | End deposit | Closing |
|---|---|---|---|---|
| 1 | 1000 | 0.05 | 100 | 1150 |
| 2 | 1150 | 0.05 | 100 | 1307.50 |
See the method in action.
Write a row
Row 2 has B2 = 400, C2 = 0.02 and D2 = 100. State the closing formula and result.
Worked solution
- 1
Write the closing formula in E2.
Enter =B2*(1+C2)+D2; the brackets form the interest factor before the end deposit is added.
- 2
Substitute the row values.
The rate represents for this period.
- 3
Calculate the closing balance.
The opening earns dollars interest, then receives the dollar end deposit.
E2 = B2*(1+C2)+D2; 508 dollars.
Audit a copy
A saving table starts at 1000 dollars with 5% annual interest and 100-dollar end deposits. Its first close is 1150, but row 3 starts at 1000 again. Correct it.
Worked solution
- 1
Correct the next opening reference.
Enter =E2 in B3 so the previous closing balance carries forward.
- 2
Apply the second-year interest.
The balance earning interest includes the first year’s interest and deposit.
- 3
Add the next end deposit.
The correct row-3 closing formula is =B3*(1+C3)+D3; the end deposit is added after interest.
B3 = E2; next close 1307.50 dollars.
Try it yourself.
Check your understanding
Change only the second-row rate in the table from 0.05 to 0.04. Find the new closing value.
Show answer & reasoning
1296 dollars
The opening for the second period remains . Change only that period’s rate: dollars.
Keep practising
6 worksheet questions · 14 marks
Read the questions online
- Write the closing formula for row 7 using columns B = opening, C = decimal rate and D = end deposit.2 marks
- A row has opening 600, rate 0.03 and end deposit 40. Find the close.2 marks
- Carry that close to the next opening and use the same rate and deposit. Find the next close.2 marks
- For a loan with interest first and end repayment in column D, write the row-2 close formula.2 marks
- Why is B2*(1+C2+D2) wrong when D2 is a deposit in dollars?3 marks
- A spreadsheet shows two decimal places, so its author assumes it is correct. Describe a stronger check.3 marks
Read the worked solutions
Equivalent correct methods earn the corresponding marks. Include units where asked.
- Write the closing formula for row 7 using columns B = opening, C = decimal rate and D = end deposit.2 marks
All inputs must use row 7.
E7 = B7*(1+C7)+D7 - A row has opening 600, rate 0.03 and end deposit 40. Find the close.2 marks
.
658 dollars - Carry that close to the next opening and use the same rate and deposit. Find the next close.2 marks
.
717.74 dollars - For a loan with interest first and end repayment in column D, write the row-2 close formula.2 marks
Add the interest, then subtract the repayment.
E2 = B2*(1+C2)-D2 - Why is B2*(1+C2+D2) wrong when D2 is a deposit in dollars?3 marks
The deposit belongs outside: B2*(1+C2)+D2.
It adds an amount of money to a dimensionless multiplier. - A spreadsheet shows two decimal places, so its author assumes it is correct. Describe a stronger check.3 marks
Number formatting only changes the display; the formula still needs independent verification.
Recalculate a row and check references and timing.
Curriculum reference & lesson scope
Australian Curriculum v9 and SA Curriculum Mathematics
Simple versus compound growth; compound interest; depreciation; repeated percentage change; savings/loan scenarios with supplied models; changing assumptions and checking results; spreadsheets/digital models.
Official curriculum source ↗