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.33.
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
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
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
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, matching the quote the lesson gives. There is nothing to round here: 0.048/12 = 0.004 exactly, so the periodic rate is exact and PMT and the formula agree to the cent. 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."