Mathematics Advanced • Year 12 • Module 7 • Lesson 19
Financial Mathematics with Technology
Build fluency with Excel/Sheets TVM functions and the mental checks that verify their outputs.
1. Quick recall
Answer each question in the space provided. 1 mark each
Q1.1 Complete the Excel/Sheets function names:
Present value: =____________(rate, nper, pmt)
Future value: =____________(rate, nper, pmt, pv)
Payment: =____________(rate, nper, pv)
Number of periods: =____________(rate, pmt, pv)
Q1.2 For a $300,000 loan at 4.8% p.a. monthly over 25 years, state the rate and nper Excel arguments and the cash-flow sign convention.
rate = ____________ nper = ____________
Sign of PMT in =PMT(rate, nper, 300000): ____________ (positive/negative; explain in one line.)
Q1.3 Write the mental-check rule from the lesson that gives a rough first month's interest as a lower bound on monthly repayment.
2. Worked example — the lesson's 25-year loan in Excel
Follow every line. Each step has a short reason.
Problem. Use Excel to find the monthly repayment on a $350,000 loan at 5.2% p.a. compounded monthly over 25 years.
Step 1 — Choose the Excel function.
PMT — we know rate, nper and pv; we need the payment.
Step 2 — Convert arguments to periodic units.
rate = 0.052 / 12 = 0.004333
nper = 25 × 12 = 300
pv = 350,000 (loan received → positive cash in)
Reason: rate and nper must share the time unit (months); pv is the cash flow today.
Step 3 — Type and evaluate the formula.
=PMT(0.052/12, 300, 350000) → −$2,083.47
Reason: the negative result tells you the cash flows out of your account each month.
Step 4 — Sanity-check with M ≈ P × r.
P × r = 350,000 × 0.004333 = $1,517 (lower bound, ignores principal repayment)
Excel result $2,083 sits above this — plausible.
Conclusion. Monthly repayment = $2,083.47; mental check passes.
3. Faded example — fill in the missing steps
Find how many months it takes to pay off $20,000 with $400/month payments at 7.2% p.a. compounded monthly. 4 marks
Step 1 — Choose the Excel function: =______________(rate, pmt, pv)
Step 2 — Convert arguments:
rate = ______________ pmt = ______________ (sign?) pv = ______________
Step 3 — Type the formula:
=NPER(________, ________, ________) → ________ months
Step 4 — Convert to years:
______________ months ÷ 12 ≈ ______________ years.
Conclusion. The loan clears in approximately ______________ months (≈ ____ years).
4. Graduated practice — TVM functions and mental checks
Write the Excel/Sheets formula AND state the value the formula returns (sign included).
Foundation — single-function calls (4 questions)
| Q | Scenario | Formula & result |
|---|---|---|
| 4.1 1 | FV of $0 today with $300/month for 10 years at 5% p.a. monthly. | |
| 4.2 1 | PV that supports $500/month for 10 years (n = 120) at 6% p.a. monthly. | |
| 4.3 1 | PMT for a $250,000 loan at 4.8% p.a. monthly over 20 years. | |
| 4.4 1 | NPER for $20,000 paid down at $400/month at 6% p.a. monthly. |
Standard — typical HSC difficulty (6 questions)
Show working in the space below each part — the formula, the result, and a mental check.
4.5 Reproduce the lesson worked example. =PMT(0.052/12, 300, 350000). Verify the result with the formula M = Pr / [1 − (1+r)⁻ⁿ]. 2 marks
4.6 Reproduce the lesson Think First check. PMT on $500,000 at 4.8% p.a. monthly for 30 years. State the Excel result and the mental check M ≈ P × r. 2 marks
4.7 A super fund saves $300/month for 30 years at 6% p.a. monthly. Write =FV(0.06/12, 360, -300, 0) and state the result. Use 360 × 300 = $108,000 in contributions as one sanity check. 2 marks
4.8 Find the PV of $200/month received for 25 years at 4% p.a. monthly (an "annuity quote"). Write the Excel formula and state the value. 2 marks
4.9 Use =RATE(120, -400, 20000) to find the implied monthly rate on a 10-year, $20,000 loan with $400 payments. Convert to a nominal annual rate. 2 marks
4.10 Build the first three rows of an amortisation table for a $20,000 personal loan at 9% p.a. monthly over 5 years (M = $415.17). Show Start Balance, Interest = B × r, Principal = M − Interest, End Balance. 3 marks
Extension — combine concepts (2 questions)
4.11 A bank quotes PMT = $2,623.47 for a $500,000 30-year loan at 4.8% p.a. monthly. Run the mental check M ≈ P × r as a lower bound, then a tighter check: total paid ≈ M × n; total interest ≈ M × n − P. State whether the quoted PMT is plausible and why. 3 marks
4.12 A saver of $350/month over 10 years hits $50,000. Use trial values of rate in =FV(rate/12, 120, -350, 0) to find the rate to 0.1%. Show two trials that bracket the answer. 3 marks
5. Self-check the easy 3
Tick the first three once you have checked the method works.
How did this worksheet feel?
What I'll revisit before next class:
Q1.1 — Excel/Sheets function names
=PV(rate, nper, pmt). =FV(rate, nper, pmt, pv). =PMT(rate, nper, pv). =NPER(rate, pmt, pv).
Q1.2 — Loan arguments
rate = 0.048/12 = 0.004. nper = 25 × 12 = 300. PMT is returned as negative because the loan is an inflow (pv positive) and the repayments are outflows; Excel enforces opposite signs to keep the cash-flow equation balanced.
Q1.3 — Mental-check rule
M ≈ P × r is the first month's interest on the full principal. It is a strict lower bound on the monthly repayment (the true M must also reduce principal), so the actual PMT is always greater than P × r.
Q3 — Faded example: $20,000 at 7.2% paid down at $400/month
Function: NPER. rate = 0.072/12 = 0.006; pmt = −400 (outflow); pv = 20,000. =NPER(0.072/12, -400, 20000) → 59.7 months ≈ 5.0 years. (Lesson rounds to "≈ 60 months".)
Q4.1 — Save $300/month for 10 years at 5%
=FV(0.05/12, 120, -300, 0) → $46,585.22 (matches lesson Q9).
Q4.2 — PV of $500/month for 10 years at 6%
=PV(0.06/12, 120, -500) → $45,036.73 (lesson activity 1: $45,035 by formula).
Q4.3 — PMT on $250,000 at 4.8% for 20 years
=PMT(0.048/12, 240, 250000) → −$1,623.51. Monthly repayment = $1,623.51 (matches lesson Q8).
Q4.4 — NPER on $20,000 at 6% monthly with $400/month
=NPER(0.06/12, -400, 20000) → 61.2 months ≈ 5 yr 1 mo (matches lesson Q10).
Q4.5 — $350,000 at 5.2% for 25 years
=PMT(0.052/12, 300, 350000) → −$2,083.47. Formula check: r = 0.004333; (1.004333)³⁰⁰ = 3.6479; M = 350,000 × 0.004333 / (1 − 1/3.6479) = 1,516.67 / 0.72585 = $2,089.43; rounding error of $5–6 is normal due to mid-step rounding. Lesson states $2,083.47.
Q4.6 — Think First $500k loan
=PMT(0.048/12, 360, 500000) → −$2,623.33 (lesson states $2,623.47 with full precision). Mental check: M ≈ 500,000 × 0.004 = $2,000 — Excel result is 31% higher, which the lesson notes is normal because the estimate ignores principal repayment.
Q4.7 — Super: $300/month for 30 years at 6%
=FV(0.06/12, 360, -300, 0) → +$301,354.51. Sanity check: contributions only = 360 × 300 = $108,000, so the FV is roughly 2.8× contributions — plausible for 6% over 30 years.
Q4.8 — PV of $200/month for 25 years at 4%
=PV(0.04/12, 300, -200) → +$37,873.91. The annuity "buy in" needs about $37,874 today to fund $200/month for 25 years at 4%.
Q4.9 — Implied rate, $20,000 over 10 yr at $400/month
=RATE(120, -400, 20000) → 0.001485/month = 0.1485%/month. Nominal annual = 0.1485 × 12 ≈ 1.78% p.a. — a very low rate, consistent with a near-zero-interest family loan.
Q4.10 — Amortisation table (first 3 rows)
r = 0.09/12 = 0.0075. Row 1: Start $20,000.00; Interest $150.00; Principal $265.17; End $19,734.83. Row 2: Start $19,734.83; Interest $148.01; Principal $267.16; End $19,467.67. Row 3: Start $19,467.67; Interest $146.01; Principal $269.16; End $19,198.51. Note how Interest falls and Principal rises each month.
Q4.11 — Plausibility of $2,623.47 on $500,000
Lower bound: P × r = 500,000 × 0.004 = $2,000 (first month's interest only). Total paid = 2,623.47 × 360 = $944,449. Total interest = 944,449 − 500,000 = $444,449. Over 30 years at ≈ 4.8%, total interest roughly equals principal — passes the sanity check (lesson confirms exactly this reasoning).
Q4.12 — Saver of $350/month reaches $50,000 in 10 years
=FV(0.03/12, 120, -350, 0) → +$48,705 (too low). =FV(0.035/12, 120, -350, 0) → +$50,395 (a hair high). Linear-interpolating: rate ≈ 0.030 + (50,000 − 48,705)/(50,395 − 48,705) × 0.005 ≈ 0.030 + 0.00383 = 3.38% p.a. ≈ 3.4% (matches lesson activity 3).