Financial Mathematics with Technology
In the real world, nobody solves loan repayments by hand — they use TVM solvers, spreadsheets, and apps. But technology is only as good as the person using it. Master the tools professionals use, learn to verify outputs with mental estimates, and understand why the HSC still demands the algebra.
Practise this lesson
Three printable worksheets that build from foundations to mastery — or build your own from any module’s questions.
A financial calculator says the monthly repayment on a $500,000 loan at 4.8% over 30 years is $2,623.47. Without recalculating — how would you quickly verify this is in the right ballpark? What mental estimate could you use?
Two tools dominate financial mathematics in practice: TVM solvers (in calculators and Excel) and spreadsheet amortisation tables. Both compute the same mathematics — the difference is in what you see and how you verify.
A TVM solver has five variables: PV, FV, PMT, N, I/Y. Enter any four, solve for the fifth. Every function in Excel maps onto one of these five — it's just a different interface for the same algebra.
Key facts
- Excel, calculator, and Python TVM functions
- How to enter cash flow signs correctly
- The limitations of each tool
Concepts
- Why technology cannot replace understanding
- How to sanity-check calculator outputs
- The importance of consistent time units
Skills
- Use Excel/Sheets for financial calculations
- Operate a financial calculator TVM solver
- Verify technology outputs with estimates
- Build simple amortisation spreadsheets
Time Value of Money solvers are built into financial calculators and spreadsheet functions. They solve for any one variable given the other four. The key rule: cash flows must have opposite signs for money in vs money out. In Excel, payments are negative (outflow) and future values are positive (inflow).
The TVM framework is universal. Use periodic rate and total periods — not annual rate and years.
where $k$ is the number of compounding periods per year. For monthly: $k=12$. For quarterly: $k=4$. This is the single most common source of errors — entering annual rate into a monthly calculation.
numpy_financial library mirrors Excel exactly: np.pmt(rate, nper, pv), np.fv(rate, nper, pmt, pv). Same sign convention, same order of arguments — if you know one you know all three.
TVM solver variables: PV, FV, PMT, N, I/Y. Enter 4, solve for 1.; Excel: =PMT(rate,nper,pv), =PV(rate,nper,pmt), =FV(rate,nper,pmt,pv)
Pause — copy the TVM variable map (PV, FV, PMT, N, I/Y — enter 4, solve for 1) and the Excel equivalents: =PMT(rate,nper,pv), =PV(rate,nper,pmt), =FV(rate,nper,pmt,pv) into your book.
Quick check: To find the monthly repayment on a $300,000 loan at 4.8% p.a. compounded monthly over 25 years, which Excel formula is correct?
Worked examples · 3 in a row, reveal as you go
Find the monthly repayment for a $350,000 loan at 5.2% p.a. compounded monthly over 25 years using Excel.
=PMT(0.052/12, 300, 350000)Set up the first two rows of an amortisation table for a $50,000 loan at 6% p.a. monthly, repayment $966.10/month.
How many months to pay off $20,000 with $400/month payments at 7.2% p.a. compounded monthly?
=NPER(0.072/12, -400, 20000)Did you get this? True or false: in the early months of a loan, the majority of the repayment reduces principal (not interest).
Common errors · the 3 traps that cost marks
Your turn to teach: A friend says "I entered =PMT(0.06, 10, 100000) to find the monthly repayment on a 10-year loan." What two errors did they make?
TVM activity · three solver challenges
Find PV. PMT = $500/month, rate = 6% p.a. compounded monthly, N = 120 months. Use Excel or your calculator to find PV. Then verify with the annuity formula.
Find PMT. PV = $300,000, rate = 4.8% p.a. compounded monthly, N = 360. Calculate PMT. Compare to the mental estimate $300,000 × 0.004 = $1,200. How much higher is the actual payment?
Find the rate. You want $50,000 in 10 years by saving $350/month. Use RATE or trial-and-error with FV to find the required annual interest rate. Show your verification.
Complete the sentence: In an amortisation table, as the loan balance decreases over time, the ______ component of each payment ______, and the ______ component ______.
Match each Excel function to its purpose:
- =PMT(rate,nper,pv)
- =PV(rate,nper,pmt)
- =FV(rate,nper,pmt,pv)
- =NPER(rate,pmt,pv)
- =RATE(nper,pmt,pv)
- Find the interest rate
- Find the number of periods
- Find the future value
- Find the present value
- Find the regular payment
Earlier you were asked: how would you verify a $2,623 monthly repayment on a $500,000 loan at 4.8% over 30 years?
Mental estimate: $500,000 × 0.004 = $2,000/month. The actual answer ($2,623) is higher because the formula accounts for paying down principal — early payments are mostly interest. The estimate $P \times r$ is just the first month's interest (a lower bound). Better check: $2,623 × 360 ≈ $944,000 total. Minus $500,000 principal = $444,000 interest. Over 30 years at ~5%, interest ≈ principal — this passes the sanity check.
Pick your answer, then rate your confidence — that tells the system what to drill next. Each retry pulls a fresh mix from the bank.
Q1. Write the Excel formula to find the monthly repayment on a $250,000 loan at 4.8% p.a. compounded monthly over 20 years. State the result and interpret it in context. (3 marks)
Q2. An investment plan requires saving $300/month at 5% p.a. compounded monthly. Use Excel's FV function to find the balance after 10 years. Does the fund reach $45,000? Show your formula. (3 marks)
Q3. A student uses =NPER(0.06/12, -400, 20000) and gets 61.2 months. (a) State what each argument represents. (b) Explain what 61.2 months means in practical terms. (c) Why does the HSC still require algebraic working even though technology can solve this instantly? (4 marks)
Comprehensive answers (click to reveal)
Drill 1 (PV): =PV(0.005, 120, -500) = $45,035. Verify: $500 × [1 − (1.005)^{−120}]/0.005 = $500 × 90.07 = $45,035. Match.
Drill 2 (PMT): =PMT(0.004, 360, 300000) = −$1,572.35/month. Mental estimate $1,200 is 23% lower — the formula accounts for principal repayment which the estimate ignores.
Drill 3 (Rate): At 3.4% p.a. (0.2833%/month): FV = $350 × [(1.002833)^{120} − 1]/0.002833 ≈ $50,050. Rate ≈ 3.4% p.a.
Q1 (3 marks): =PMT(0.048/12, 240, 250000) returns −$1,623.51 [2]. Monthly repayment is $1,623.51 — the borrower pays this each month for 20 years to clear the debt [1].
Q2 (3 marks): =FV(0.05/12, 120, −300) returns $46,585.22 [2]. Yes, it exceeds $45,000 by $1,585 [1].
Q3 (4 marks): (a) 0.06/12 = monthly rate; −400 = monthly payment (outflow); 20000 = loan balance (present value) [2]. (b) 61.2 months means 61 full payments plus a smaller final payment — round up to 62 months [1]. (c) HSC markers reward formula selection and algebraic reasoning; technology output without working earns zero marks [1].
Five timed questions on TVM solvers, Excel formulas, and technology verification. Beat the boss to bank a tier — gold (90% + speed), silver (75%), or bronze (50%). Replays welcome.
Enter the arenaClimb platforms by answering TVM and spreadsheet questions. Pool: lessons 1–19.
Mark lesson as complete
Tick when you've finished the practice and review.