The idea, step by step
Make each column meaningful
Use A = period, B = opening balance, C = decimal rate, D = end deposit and E = closing balance. With first data in row 2, write E2 = B2*(1+C2)+D2.
The next opening is B3 = E2. Enter the next rate and deposit, then copy the closing formula to row 3 so it refers to B3, C3 and D3.
Check the first rows independently
Calculate at least the first two periods by hand. A wrong reference can create a neat but incorrect table, and currency formatting does not repair a formula.
Write that interest precedes the end deposit. The same model can be completed on paper; digital tools are useful only after the calculation is correctly defined.
| Period | Opening | Rate | End deposit | Closing |
|---|---|---|---|---|
| 1 | 1000 | 0.05 | 100 | 1150 |
| 2 | 1150 | 0.05 | 100 | 1307.50 |
A common trap
Copy the starting principal into every opening-balance cell.
Instead: That prevents compounding. Carry each previous closing balance into the new opening cell.
See the thinking
Write a row
Row 2 has B2 = 400, C2 = 0.02 and D2 = 100. State the closing formula and result.
- E2 = B2*(1+C2)+D2.
- .
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.
- The next opening must equal the previous close: B3 = E2 = 1150.
- The corrected close is .
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
1296 dollars
.
Your turn
6 questions · 14 marks · Work at your own pace.
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 ↗