Mathematics Advanced • Year 12 • Module 7 • Lesson 19
Financial Mathematics with Technology
Practise HSC-style writing on TVM functions, amortisation tables and algebra-vs-technology verification.
1. Short-answer questions
1.1 Write the Excel/Sheets formula to find the monthly repayment on a $250,000 loan at 4.8% p.a. compounded monthly over 20 years. State the value returned and the monthly repayment in plain dollars. 3 marks Band 3
1.2 A saver puts $300/month into an account paying 5% p.a. compounded monthly for 10 years. (a) Write the Excel/Sheets formula for the FV. (b) State the FV. (c) State whether a $45,000 goal is met. 3 marks Band 3-4
1.3 A $20,000 loan at 6% p.a. compounded monthly is repaid at $400/month. (a) Write =NPER(...) and state the number of months. (b) State whether the loan clears in under 5 years. (c) Write one sentence explaining why HSC questions still demand algebraic working even though the Excel call gives the same answer. 4 marks Band 4
Stuck on 1.3(c)? Refer to the lesson's misconception "Technology makes understanding the formulas unnecessary".2. Extended response
2.1 A homeowner has a $400,000 mortgage at 5.4% p.a. compounded monthly over 30 years. They want to use both algebra and Excel to verify the bank's monthly repayment quote and to model the impact of a 1.0 pp interest-rate rise.
Loan principal: $400,000.
Original rate: 5.4% p.a. compounded monthly, 30 years.
Rate-rise scenario: 6.4% p.a. compounded monthly (same term).
(a) Compute the monthly repayment at 5.4% using M = Pr / [1 − (1+r)⁻ⁿ]. Then write the matching Excel formula and confirm the values agree to the nearest dollar.
(b) Build the first 3 rows of an amortisation table at 5.4% and use them to verify the mental check M ≈ P × r is a lower bound only.
(c) Recompute M at 6.4% with both algebra and Excel. State the dollar increase in monthly repayment and write 2–3 sentences on (i) the mental-check rule from the lesson, (ii) why the bank's TVM solver and your formula agree exactly, and (iii) why the HSC still demands algebraic working. 8 marks Band 5-6
Explicit marking criteria
Part (a) — 3 marks
• 1 mark — correct r = 0.0045 and n = 360.
• 1 mark — correct algebraic M ≈ $2,245.22 with substitution shown.
• 1 mark — correct Excel formula =PMT(0.054/12, 360, 400000) returning −$2,245.22.
Part (b) — 2 marks
• 1 mark — correct first 3 rows (Interest $1,800, $1,799.80, $1,799.60 and corresponding balances).
• 1 mark — explicit comparison of M ($2,245) to P × r ($1,800) as a lower bound.
Part (c) — 3 marks
• 1 mark — correct M at 6.4% (≈ $2,502.30) and dollar increase (≈ $257/month).
• 1 mark — discussion connects mental check to first-month interest.
• 1 mark — explicit statement that HSC awards marks for formula + substitution, not the cell value.
Your response:
Stuck on (c)? Quote the 1 pp rate rise alongside the $257/month payment shock — that is the headline finding.How did this worksheet feel?
What I'll revisit before next class:
1.1 — $250,000 at 4.8% over 20 years (3 marks)
Sample response. =PMT(0.048/12, 240, 250000) returns −$1,623.51. The monthly repayment is $1,623.51 (the negative sign indicates a cash outflow from the borrower's account).
Marking notes. 1 mark — correct Excel formula including correct rate, nper, pv. 1 mark — correct numerical value. 1 mark — correct sign-handling commentary (cash outflow). Matches lesson Q8.
1.2 — FV of $300/month at 5% for 10 years (3 marks)
Sample response. (a) =FV(0.05/12, 120, -300, 0). (b) FV = +$46,585.22. (c) Goal of $45,000 is exceeded by $1,585.22 — yes, the saver meets the target.
Marking notes. 1 mark — correct formula. 1 mark — correct FV. 1 mark — explicit comparison to $45,000 goal. Matches lesson Q9.
1.3 — NPER on $20,000 at 6% with $400/month (4 marks)
Sample response. (a) =NPER(0.06/12, -400, 20000) → 61.22 months. (b) 61.22 months > 60 months, so the loan does not clear in under 5 years (it needs 5 years 1 month). (c) The HSC awards marks for the algebraic chain (formula → substitution → evaluation) because that is the evidence the student understands why n = 61, not just that it is — an Excel cell with the wrong sign or wrong rate-unit would still return a number, and only the algebra would catch the mistake.
Marking notes. 1 mark — correct Excel formula. 1 mark — correct n. 1 mark — explicit "no — needs 5 yr 1 mo". 1 mark — algebra-vs-Excel reasoning explicit. Matches lesson Q10.
2.1 — $400k mortgage at 5.4%, plus rate-rise scenario (8 marks): sample Band-6 response with annotations
Sample Band-6 response.
(a) Monthly repayment at 5.4%.
r = 0.054 / 12 = 0.0045 per month; n = 30 × 12 = 360. [1 mark]
(1.0045)³⁶⁰ = 5.04305. M = 400,000 × 0.0045 / (1 − 1/5.04305) = 1,800.00 / 0.80171 = $2,245.22/month. [1 mark]
Excel: =PMT(0.054/12, 360, 400000) → −$2,245.22. Agrees with algebra to the cent. [1 mark]
(b) First three rows of amortisation table.
| Period | Start | Interest | Principal | End |
|---|---|---|---|---|
| 1 | $400,000.00 | $1,800.00 | $445.22 | $399,554.78 |
| 2 | $399,554.78 | $1,797.99 | $447.23 | $399,107.55 |
| 3 | $399,107.55 | $1,795.98 | $449.24 | $398,658.31 |
Mental check vs M. P × r = 400,000 × 0.0045 = $1,800 (first month's interest only). Actual M is $2,245 — about 25% above this lower bound, exactly as the lesson predicts because the formula M ≈ P × r ignores principal repayment. [1 mark on table + 1 mark on mental-check connection]
(c) After a 1 pp rate rise to 6.4%.
r' = 0.064 / 12 = 0.005333; n = 360. (1.005333)³⁶⁰ = 6.80407. M' = 400,000 × 0.005333 / (1 − 1/6.80407) = 2,133.33 / 0.85303 = $2,501.51/month. Excel: =PMT(0.064/12, 360, 400000) → −$2,501.51. [1 mark]
Dollar increase = 2,501.51 − 2,245.22 = $256.29/month, or about $3,075/year extra on the household budget for a 1 pp rate move.
Discussion. (i) The mental check M ≈ P × r gives the first-month interest only — at 5.4% that is $1,800, far below actual M = $2,245; the gap is the principal-recovery component, which grows over time. [1 mark] (ii) Excel and the algebra agree to the cent because =PMT implements the same closed-form annuity formula M = Pr / [1 − (1+r)⁻ⁿ] — they cannot disagree when the inputs are typed correctly. (iii) The HSC still demands algebraic working because a TVM solver call can be wrong in silent ways (sign flip, rate-unit mismatch, wrong nper). Showing the formula plus substitution is the evidence of understanding the marker rewards, and it lets the marker award partial credit even if the final arithmetic slips. [1 mark]
Total: 8/8.
Band descriptors for marker.
Band 3: r and n correct, algebra shows M ≈ $2,245 but no Excel formula, no rate-rise calculation. ≈ 3 marks.
Band 4: Both M values correct, amortisation table shows correct first row only, discussion in (c) restates "Excel is faster" without naming the HSC rule. ≈ 5 marks.
Band 5: All calculations correct, three full rows of the table, mental-check connection made but only one of the three (c)-discussion points addressed. ≈ 6–7 marks.
Band 6: Full algebra + Excel + amortisation, mental check applied as a lower bound, and the three (c) points each addressed with a specific numeric or methodological reference. 8/8.