Coaching Centre
Loan spreadsheets, extra repayments and offsets
Building a loan schedule in a spreadsheet, the PMT function, and how extra repayments, fortnightly repayments and offset accounts reduce the time and interest on a home loan.
A home loan in Australia often runs for $30$ years: $360$ monthly rows. Nobody fills that in by hand. A spreadsheet does it in seconds, and once it is set up you can ask "what if?": what if I pay a bit extra, pay fortnightly, or keep my savings in an offset account? Those small choices can be worth hundreds of thousands of dollars.
A loan schedule in a spreadsheet
Each row is one month, built from the row above, exactly like the loan table in 3A.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rate p.a. | 0.06 | Repayment | 3597.30 | |
| 2 | Month | Opening | Interest | Repayment | Closing |
| 3 | 1 | 600000 | =B3*$B$1/12 | =$D$1 | =B3+C3-D3 |
| 4 | =A3+1 | =E3 | fill C3 to E3 down | ||
The rate and the repayment use absolute references ($B$1, $D$1) so that every row points back to them. The opening balance of each month is the closing balance of the month before.
The Nguyens borrow $\$600\,000$ for a home at $6\%$ p.a., with monthly repayments of $\$3597.30$ over $30$ years. Find the total repaid and the total interest.
Three ways to pay less interest
Every dollar that comes off the balance early stops being charged interest for every month that follows. That is why all three strategies below work.
- Extra repayments. Paying more than the minimum reduces the balance faster. Each extra dollar saves interest for the rest of the loan.
- Fortnightly repayments. Paying half the monthly amount every fortnight sounds the same, but there are $26$ fortnights in a year, so you make the equivalent of $13$ monthly repayments instead of $12$.
- Offset account. A savings account linked to the loan. Its balance is subtracted from the loan balance before interest is calculated, so savings of $\$50\,000$ on a $\$600\,000$ loan mean interest is charged on only $\$550\,000$. The money stays yours and you can still withdraw it.
For the Nguyens' loan, a spreadsheet gives these results (rounded). Compare each option with the standard loan.
| Option | Time to repay | Total interest |
|---|---|---|
| Standard, $\$3597.30$ a month | $30$ years | about $\$695\,000$ |
| Extra $\$500$ a month | $265$ months, about $22$ years | about $\$482\,300$ |
| Half the repayment fortnightly | $638$ fortnights, about $24.5$ years | about $\$546\,200$ |
| Offset account of $\$50\,000$ | $304$ months, about $25.3$ years | about $\$492\,900$ |
Start with the Nguyens' loan and check the standard total interest. Add $\$500$ a month extra and compare with Example 2. Then try $\$100$ extra. Is the saving five times smaller?
Switch to fortnightly, then add an offset balance. Which change saves the most for your budget?
Your turn
A loan of $\$400\,000$ at $6\%$ p.a. has an offset account with $\$30\,000$ in it. Find the interest charged in the first month.
Show the working
Interest is charged on $400\,000 - 30\,000 = \$370\,000$. Monthly rate $= 0.06 \div 12 = 0.005$. Interest $= 370\,000 \times 0.005 = \$1850$, instead of $\$2000$ without the offset.
In a loan spreadsheet the opening balance is in B3 and the annual rate is in B1. Which formula gives the interest for the month and can be filled down?
The balance must change row by row (relative B3), the rate must stay fixed (absolute B1), and a monthly rate is the annual rate divided by $12$.
A monthly repayment is $\$2400$. If instead half of it is paid every fortnight, how much more is repaid in a year?
Fortnightly: $1200 \times 26 = \$31\,200$. Monthly: $2400 \times 12 = \$28\,800$. Difference $= \$2400$, one extra monthly repayment.
A loan balance is $\$400\,000$ at $6\%$ p.a. calculated monthly, with $\$30\,000$ in an offset account. Find the interest charged for the month.
$(400\,000 - 30\,000) \times 0.005 = 370\,000 \times 0.005 = \$1850$.
Why does a small extra monthly repayment save so much interest over a long loan?
Interest is charged on the balance every month. Reducing the balance early cuts the interest for every remaining month, and those savings add up over decades.
A home loan is repaid with $360$ monthly repayments of $\$3597.30$. Find the total amount repaid.
$3597.30 \times 360 = \$1\,295\,028$.
The loan in the previous question was for $\$600\,000$. How much interest was paid?
$1\,295\,028 - 600\,000 = \$695\,028$.
A $360$-month loan is paid off in $265$ months by making extra repayments. How many months earlier is it paid off?
$360 - 265 = 95$ months, which is $7$ years and $11$ months.
Which statement about an offset account is correct?
An offset reduces the balance that interest is calculated on. The money is still yours and can be withdrawn, but then the saving stops.
Exam style: A loan of $\$450\,000$ is repaid over $25$ years with monthly repayments of $\$2736.58$. Find the total interest paid.
Number of repayments $= 25 \times 12 = 300$. Total $= 2736.58 \times 300 = \$820\,974$. Interest $= 820\,974 - 450\,000 = \$370\,974$.
Exam style: Spreadsheet output for a $\$600\,000$ loan shows total interest of $\$695\,028$ with standard monthly repayments, $\$546\,158$ when half the repayment is paid fortnightly, and $\$482\,317$ with an extra $\$500$ a month. Which statement is correct?
Extra $\$500$: $695\,028 - 482\,317 = \$212\,711$ saved. Fortnightly: $695\,028 - 546\,158 = \$148\,870$ saved. Difference: $212\,711 - 148\,870 = \$63\,841$.