Repayment Examples and Checklists
How to calculate home loan repayments: the formula, a full example and a spreadsheet method
Calculate Australian home loan repayments by hand or in a spreadsheet, see how interest and principal split each month, and check the balance you will owe after 5, 10 or 20 years.
To calculate a principal and interest home loan repayment, divide the annual rate by 12 to get the monthly rate r, multiply the loan amount by r, and divide by 1 minus (1 + r) to the power of minus the number of months. On an illustrative $600,000 loan at 6.00% over 30 years, that gives about $3,597.30 a month. In a spreadsheet, the same answer comes from =PMT(6%/12, 360, -600000). Once you can run this one formula, you can test fortnightly payments, extra repayments, offsets and refinancing yourself instead of relying on a lender's headline figure.
This is the starting guide for the Repayment Examples and Checklists section. All rates are illustrative.
The formula, step by step
- P = loan amount ($600,000)
- r = annual rate / 12 (6.00% / 12 = 0.5% = 0.005)
- n = number of monthly repayments (30 x 12 = 360)
Working:
- (1 + r)^n = 1.005^360 = about 6.0226
- (1 + r)^-n = 1 / 6.0226 = about 0.16604
- 1 - 0.16604 = 0.83396
- P x r = $600,000 x 0.005 = $3,000
- $3,000 / 0.83396 = about $3,597.30
Total repaid: $3,597.30 x 360 = about $1,295,028. Interest: about $695,029, more than the amount borrowed.
Where each repayment goes
Each month, interest is charged on the balance and the rest of the repayment reduces the balance:
| Month | Interest (balance x 0.5%) | Principal (repayment minus interest) | Balance after |
|---|---|---|---|
| 1 | $3,000.00 | $597.30 | $599,402.70 |
| 2 | $2,997.01 | $600.29 | $598,802.41 |
| 3 | $2,994.01 | $603.29 | $598,199.13 |
Early on, about 83% of the repayment is interest. In this example, the principal portion only overtakes the interest portion at about month 223, more than 18 years in.
Balance you will still owe
| After | Balance owing | Paid off so far |
|---|---|---|
| 5 years | about $558,300 | about $41,700 |
| 10 years | about $502,100 | about $97,900 |
| 15 years | about $426,300 | about $173,700 |
| 20 years | about $324,000 | about $276,000 |
| 25 years | about $186,100 | about $413,900 |
This matters when you plan to sell or refinance: after five years of full repayments, most of the loan is still there. The repayment calculator shows this year-by-year table for your own figures.
Recalculating after a rate change
When a variable rate moves, the lender recalculates the repayment on the balance you owe at that point and the months left, not on the original loan. Continuing the example, after five years you owe about $558,326 with 300 months left:
- If the rate rises to 6.50%: about $3,769.86 a month, $172.56 more than before
- If the rate falls to 5.50%: about $3,428.61 a month, $168.69 less, unless you ask the lender to keep the old repayment
The same method works for a refinance: use your current balance and remaining term, then compare the two rates. That is exactly what the refinance break-even calculator does.
What each dollar borrowed costs
A quick way to see the long-run price of a loan is total repaid divided by the amount borrowed. At 6.00% over 30 years, $1,295,029 / $600,000 is about 2.16: each dollar borrowed costs about $2.16 by the end. Over 25 years at the same rate the figure is about 1.93, and at 7.00% over 30 years it is about 2.40. Shorter terms and lower rates both pull it down, which is why the rest of this section focuses on paying ahead of schedule.
Spreadsheet method
In Excel, Google Sheets or Numbers:
- Repayment:
=PMT(annual_rate/12, years*12, -loan) - Balance after k months:
=FV(annual_rate/12, k, repayment, -loan)(the result shows as a negative or positive number depending on the sign convention; ignore the sign) - Months to repay with a higher repayment:
=NPER(annual_rate/12, -repayment, loan) - Interest-only repayment:
=loan*annual_rate/12
Build a row per month with columns for opening balance, interest, repayment, principal and closing balance, and you have a full amortisation schedule.
Why your lender's figure differs slightly
- Daily interest. Most lenders calculate interest daily on the balance (annual rate / 365 x balance) and charge it monthly. Months with 31 days cost more than months with 30. On $600,000 at 6%, a day's interest is about $98.63.
- Rounding. Lenders often round repayments up to the next dollar or cent.
- Fees. Monthly account fees may be debited with the repayment.
- Settlement date. The first repayment may cover a part month.
What to calculate next
Each guide in this section runs one decision through the same formula:
- Fortnightly vs monthly repayments: why half-monthly payments every fortnight cut years off the loan
- Offset account vs redraw: what $40,000 in an offset saves
- Interest only vs principal and interest: the repayment jump when an interest-only period ends
- Extra repayments worked example: $300 or $500 a month more
- Rate rise stress test: your repayment if rates rise 1, 2 or 3 points
- Refinance worked example and break-even: when switching pays
- Home loan comparison checklist: what to collect and ask before you choose
Quick reference table
Monthly repayment per $100,000 borrowed, 30 years, illustrative rates:
| Rate | Per $100,000 | On $500,000 | On $750,000 |
|---|---|---|---|
| 5.50% | $567.79 | $2,838.95 | $4,258.42 |
| 6.00% | $599.55 | $2,997.75 | $4,496.63 |
| 6.50% | $632.07 | $3,160.34 | $4,740.51 |
| 7.00% | $665.30 | $3,326.51 | $4,989.77 |
Multiply the per-$100,000 figure by your loan in hundreds of thousands for a quick estimate.
Frequently asked questions
Does a longer term mean less interest?
No. A longer term lowers the repayment but increases total interest, because the balance stays higher for longer.
How do I calculate repayments if my rate changes?
Recalculate using the balance at the date of change, the new rate and the months left. Lenders do the same when variable rates move, although some keep your repayment unchanged unless you ask.
Is my repayment the same as my minimum repayment?
Usually. The contract sets a minimum. You can pay more on a variable loan, which shortens the term; see extra repayments.
Can I use this formula for an investment loan?
Yes for principal and interest. For interest-only periods, the repayment is just balance x annual rate / 12.
Sources
- ASIC Moneysmart — mortgage calculator and home loan guidance
- Reserve Bank of Australia — interest rate statistics and decisions
- ASIC — key facts sheets and credit disclosure
