Mathematics Advanced • Year 12 • Module 7 • Lesson 19
Financial Mathematics with Technology
Apply TVM functions and amortisation tables to mortgage, savings and verification scenarios.
Problem 1 — Verify the bank's mortgage quote (the "Think First" scenario)
A bank says the monthly repayment on a $500,000 loan at 4.8% p.a. over 30 years is $2,623.47.
Set up: What are we solving for?
(i) Write the Excel/Sheets formula and state the result it returns. 1 mark
(ii) Apply the mental check M ≈ P × r as a lower bound, then a second check using "total interest ≈ principal over 30 years at ≈ 5%". 3 marks
(iii) A friend mis-enters the formula as =PMT(0.048, 30, 500000). State the result and explain in one sentence why it is nonsense. 2 marks
Stuck? Revisit lesson § Think First and § Misconceptions to Fix.Problem 2 — Build a 6-month amortisation table
A $30,000 car loan at 6.5% p.a. compounded monthly over 5 years has monthly repayment M = $585.69.
Set up: What are we solving for?
(i) Build the first 6 months of the amortisation table. Columns: Period, Start Balance, Interest = B × r, Principal = M − Interest, End Balance. 4 marks
(ii) State the total interest paid in the first 6 months and the principal reduction over the same period. 2 marks
(iii) Write one sentence describing how the interest column and the principal column change over time, and why. 1 mark
Problem 3 — Reverse-engineer the dealer's rate
A dealer offers a $25,000 car as $0 down, $475/month for 60 months, "0% interest". Use Excel to find the actual rate.
Set up: What are we solving for?
(i) Write =RATE(60, -475, 25000) and state the result as a monthly rate. 2 marks
(ii) Convert to a nominal annual rate (rate × 12) and compare with the lesson's worked example of "0%" finance on $23,500 cash vs $475/month. 2 marks
(iii) The dealer's "cash price" is actually $23,500 (sticker $25,000 inflated). Recompute =RATE(60, -475, 23500). Which rate does the customer actually pay if they sign without negotiating to the cash price? 2 marks
Stuck? Revisit lesson § Car Loans (back-reference to Lesson 17) and the Excel TVM table.Problem 4 — Hit a savings target by tuning the rate
A saver puts $350/month into a high-interest account for 10 years and hits exactly $50,000. They want to know what rate the account was actually paying.
Set up: What are we solving for?
(i) Use =RATE(120, -350, 0, 50000) to find the monthly rate. 2 marks
(ii) Quote as a nominal annual rate and verify by writing the FV formula and showing it lands near $50,000. 2 marks
(iii) If the saver could lift contributions to $400/month at the same rate, how much would the FV grow to in the same 10 years? Use =FV with your rate from (i). 2 marks
Problem 5 — Technology versus HSC algebraic working
An HSC question asks: "A $300,000 loan at 5% p.a. compounded monthly is to be repaid in 20 years. Calculate the monthly repayment."
Set up: What are we solving for?
(i) Show the algebraic working using M = Pr / [1 − (1+r)⁻ⁿ]. 3 marks
(ii) Write the Excel formula and confirm it returns the same value. 1 mark
(iii) Write 2–3 sentences explaining (per the lesson) why the HSC awards marks for the algebraic working, not the Excel cell, even though both give the right answer. 2 marks
Stuck? Revisit lesson § Misconceptions to Fix — "Technology makes understanding the formulas unnecessary".How did this worksheet feel?
What I'll revisit before next class:
Problem 1 — Verifying the bank's $500k quote
Set up. We are confirming a bank-quoted PMT with Excel and two mental checks, and diagnosing a mis-entered formula.
(i) =PMT(0.048/12, 360, 500000) → −$2,623.33 (lesson states $2,623.47 with full precision; the small discrepancy is just rounding of the periodic rate). The negative sign means cash flows out of the borrower's account each month.
(ii) Lower-bound check: M ≈ P × r = 500,000 × 0.004 = $2,000 (first-month interest, no principal reduction). The actual $2,623 sits about 31% above this — plausible. Long-run check: total paid ≈ 2,623 × 360 ≈ $944,500; total interest ≈ $444,500 ≈ 89% of principal, consistent with "over 30 years at ≈ 5%, interest roughly equals principal".
(iii) =PMT(0.048, 30, 500000) → ≈ −$32,043. This is "annual" interest of 4.8% applied to a 30-period loan in years, not 360 months — the rate and nper are mismatched units. The answer is nonsense because $32,000/month over 30 months would repay only $960,000, but the formula assumes 30 annual payments of that size — almost $1 million in payments over only 30 years for a $500,000 loan.
Problem 2 — 6-month amortisation table
Set up. We are building the table month by month, then summarising interest paid vs principal recovered in the first half-year.
(i) r = 0.065/12 = 0.005417. M = $585.69.
| Period | Start | Interest | Principal | End |
|---|---|---|---|---|
| 1 | $30,000.00 | $162.50 | $423.19 | $29,576.81 |
| 2 | $29,576.81 | $160.20 | $425.49 | $29,151.32 |
| 3 | $29,151.32 | $157.90 | $427.79 | $28,723.53 |
| 4 | $28,723.53 | $155.58 | $430.11 | $28,293.42 |
| 5 | $28,293.42 | $153.25 | $432.44 | $27,860.98 |
| 6 | $27,860.98 | $150.91 | $434.78 | $27,426.20 |
(ii) Total interest (6 mo) = 162.50 + 160.20 + 157.90 + 155.58 + 153.25 + 150.91 = $940.34. Principal reduction = 30,000 − 27,426.20 = $2,573.80.
(iii) Interest falls each month because the balance it is computed on shrinks; principal correspondingly rises because M is constant. Early payments are mostly interest; later payments are mostly principal.
Problem 3 — Dealer rate from $475 on $25,000
Set up. We are computing the implied monthly rate two ways: assuming the customer paid the sticker $25,000 vs the negotiated cash price $23,500.
(i) =RATE(60, -475, 25000) → +0.00425/month = 0.425%/month.
(ii) Nominal annual = 0.00425 × 12 = 5.10% p.a. — the "0%" pitch on the sticker price is actually 5.1% when amortised. Lesson finds 7.7% on the $23,500 cash price.
(iii) =RATE(60, -475, 23500) → +0.00641/month = 0.641%/month → 7.69% p.a. — matches the lesson exactly. A customer who fails to negotiate to the $23,500 cash price is effectively paying 5.1% on inflated principal; the customer who negotiates the cash price then finances pays 7.7% on the lower principal — pick the option with the lower total cost.
Problem 4 — Implied rate on $350/month → $50,000 in 10 yr
Set up. We are reverse-engineering the savings rate, then re-using it to project a higher-contribution scenario.
(i) =RATE(120, -350, 0, 50000) → +0.002817/month = 0.2817%/month.
(ii) Nominal annual = 0.002817 × 12 = 3.38% p.a. ≈ 3.4% (matches lesson activity 3). Verification: =FV(0.002817, 120, -350, 0) → +$49,990, just $10 under target — within rounding.
(iii) =FV(0.002817, 120, -400, 0) → +$57,131. Lifting the monthly saving by $50 grows the 10-year FV by about $7,140 — the contribution scales linearly because the same rate operates on a proportionally larger annuity factor.
Problem 5 — Algebra vs Excel for $300,000 over 20 yr at 5%
Set up. We are demonstrating that algebraic working and Excel agree, but explaining why the HSC scores the algebra.
(i) r = 0.05/12 = 0.004167; n = 240. (1.004167)²⁴⁰ = 2.71264. M = 300,000 × 0.004167 / (1 − 1/2.71264) = 1,250.00 / 0.63139 = $1,979.87/month.
(ii) =PMT(0.05/12, 240, 300000) → −$1,979.87. Identical to algebra.
(iii) Sample answer: "The HSC marker awards marks for showing the formula M = Pr / [1 − (1+r)⁻ⁿ], the substitution, and at least one intermediate evaluation — that is the evidence that you understand why the answer is $1,979.87, not just that it is. Excel can be mis-entered (wrong sign, wrong nper, wrong rate units) and the student would have no way to detect the error without the algebra; conversely, a candidate who shows substitution and arithmetic gets partial marks even if a button press goes wrong."