Mortgage & offset explorer: maths & Excel

Back to calculator → · Read the learning guide →

Excel examples use English function names, comma separators and percentages entered with the % sign. Regional settings may require semicolons. Every input is hypothetical. Display two decimal places, but do not round intermediate calculations.

1. Calculate the base repayment

B2=600000 principal; B3=6% annual rate; B4=30 years; B5=30000 constant offset; B6=200 extra monthly payment. The monthly rate r=B3/12 and number of payments n=B4×12. Payment P=Lr/[1−(1+r)⁻ⁿ]. At r=0 use L/n.

B7: =IF(B3=0,B2/(B4*12),-PMT(B3/12,B4*12,B2))

The default base payment is approximately $3,597.30. Excel PMT uses a negative cash-flow sign, so negate it to display a positive payment.

2. Build the scenario ledger

Row 12: A12=1 month and B12=$B$2 opening loan. C is interest, D actual payment and E closing loan.

C12: =MAX(0,B12-$B$5)*$B$3/12
D12: =MIN(B12+C12,$B$7+$B$6)
E12: =MAX(0,B12+C12-D12)
A13: =A12+1
B13: =E12

Copy C12:E12 down for subsequent months. First-month scenario interest is $2,850, payment about $3,797.30 and closing balance about $599,052.70. Retain full precision between months. Stop when closing principal is at most $0.005. Cap the final payment at principal plus interest.

3. Create the baseline

Duplicate the ledger with offset and extra repayment both zero, keeping the same B7. Sum interest until payoff in both sheets; baseline interest minus scenario interest is the illustration of interest avoided. Count months to compare terms. Offset cash remains outside the loan: even when offset exceeds principal, principal must still be repaid and interest is floored at zero.

Match the assumptions

Monthly principal-and-interest repayments at a constant annual rate ÷ 12. The base repayment is calculated without offset. Scenario payments keep that repayment and add the extra amount. Interest applies to max(0, loan balance − constant offset). The offset remains separate cash; it does not repay principal. No fees, rate changes, daily lender rounding, redraw, partial offset or interest-only periods. Interest avoided excludes any extra product costs.

References and verification

Official references checked for this release on 1 November 2026. Rules and product terms can change.

Educational scenarios only. All amounts are AUD. These calculations do not assess your circumstances or recommend a product or strategy. Results are estimates, not forecasts or personal financial, credit or tax advice. Inputs stay in your browser and are not saved automatically. The optional Save control stores a scenario on this device; exported files contain your entered amounts. Check official rules and actual provider terms. Prepared by the Working Wealth editorial team, 1 November 2026; independent professional review is pending.

Loan annuity and offset amortisation