# BSP First Home Owner Scheme Mortgage Calculator — Excel

## Spreadsheet calculator for mortgage and amortization

Jan 31, 2019 · 7 min read

Disclaimer: I am not a banker or loan officer. Consult a finance professional, BSP, or other banks for detailed advice.

I used this Wikihow article to build a mortgage calculator in Microsoft Excel which can be used to go through mortgage payment options.

It has two parts:

1. Mortgage Calculator which calculates the total payment you will be making on a home loan. For a K400,000 home loan with 4% interest per annum over 40 years, how much will I be paying in total? This calculator answers that question.
2. Payment Schedules (Amortization) showing a list of all the fortnightly payments you will be making for the loan’s period. One is without extra, early payments. For how many fortnights will I be paying off my home loan and does early payment cut short my home loan in any way? This part should answer that question.

We will run through some fictional home loans and payment amounts to demonstrate some strategies you can take to reduce the cost of your home loan.

Most of the examples are based on Bank South Pacific’s First Home Owner Scheme (BSP FHOS) mortgage package.

For simplicity, I have not included bank fees in these calculations. BSP charges a 1% fee for its FHOS offer. Getting a K300,000 loan would incur a K3000 bank fee. There could be other fees which you have to inquire with the bank to find out.

# How to Use — General

The yellow highlighted cells are free for you to change and play around with. The rest of the cells have formulas behind them to automatically calculate values for you.

From the preview above, you can change these yellow highlighted cells:

• Loan Amount
• Annual Interest Rate
• Life Duration in Years
• Extra Payment

# Part 1 — Mortgage Calculator

The first part of this calculator is a mortgage calculator. It conveniently calculates all payment components of the loan for you. You can change the values in yellow depending on your home loan options. BSP FHOS offers max loan of K400,000 at 4% interest rate, over 40 year period. These are the values above.

The payment per fortnight is calculated to be K771.30. You will be paying a total of K802,150.67 over 40 years.

We will compare other scenarios later.

# Part 2 — Payment Schedules

The second part is the payment schedule. There are two payment schedules. The one under the blue title shows straight forward payments. That is, if you just choose to pay the minimum deduction every fortnight towards the loan.

The other payment schedule on the right, under orange title, includes extra payments shown in the yellow column. This is if you wanted to make extra payments to pay off the loan quickly.

We will compare these two later.

If you scroll to the bottom of these schedules you will also see the sum of all payments. We we use this to make comparisons.

# Comparing Interest Rates

Now let’s start doing some comparisons. Let’s use the mortgage calculator to compare interest rates. We will keep other values constant, and just change interest rate to see its effect.

Let’s say you wish to get a K400,000 home loan amount over 40 year period. We’ll compare interest rates of 4%, 8.5%, and 10%.

Loan Amount = K400,000
Time = 40 years
Interest Rate = ???

4% Interest Rate (BSP FHOS)

8.5% Interest Rate (Kina Bank)

10% Interest Rate (Fictional)

Results

4% pa: Total Cost = K802,150.67
8.5% pa: Total Cost = K1,407,224.86
10% pa: Total Cost = K1,630,086.02

Conclusion

Just by looking at the above three examples, especially the cell for Total Cost of Loan, it is easy to conclude. Go for the home-loan with the lowest interest rate.

# Comparing Duration of Loan

So now we will look at a loan’s time in years, keeping other values constant. Again, let’s say we are looking at a K400,000 home loan at 4% interest rate. We will compare 40, 30, and 20 years duration of loans.

Loan Amount = K400,000
Interest Rate = 4% pa
Time in years = ????

40-year Mortgage

30-year Mortgage

20-year Mortgage

Results

40 years: Total Cost = K802,150.67
30 years: Total Cost = K687,159.36
20 years: Total Cost = K581,400.92

You will see that Time has an exponential effect on the total cost of loan. This is because in the compound interest formula, time is an exponent. So here it would be best to choose the least time for your loan.

However, payment per fortnight is the inverse.

40 years: Payment per fortnight = K771.30
30 years: Payment per fortnight = K880.97
20 years: Payment per fortnight = K1118.08

So the 40 year loans actually mean you pay less per fortnight than 20 year loans.

Conclusion

It is best to go for the loan with the shortest time duration. 20 or 30 years if possible. But if the fortnightly payments are beyond your budget, then just go for the longer 40 year terms.

# Comparing Extra Payments

I think this part of our discussion will be the longest and most interesting.

Some loans do not allow you to make payments to pay them off earlier. Sometimes you are penalized for paying off too early. Other loans allow you to do extra payments apart from the usual fortnightly deductions.

Note: I do not know what the correct term for this is, I’m just calling them extra payments. Lol. But you should get the point.

We will use the payment schedules to compare these now.

Again, let’s keep all other factors constant. But let’s just add in extra payments.

Loan Amount: K400,000
Interest Rate: 4% pa
Time: 40 years
Extra Payments: ????

We’ve already seen that this loan gives the following:

The payment schedule shows you the list of payments you will be making. You can add dates to them. It also shows you your loan balance as you pay off fortnightly. Interest and principal also change every fortnight on your outstanding loan balance.

Notice in the next screenshot, at the very last payment No 1040, Interest = K1.18 and Principal = K770.11. This is the last fortnightly payment of K771.30 that brings the loan balance to K0.00 and ends your mortgage.

Back to the question: What effect does extra payment have on my mortgage?

We will compare zero extra payment with payments of K5000, K20,000, and K50,000.

Zero Extra Payments

This is shown in the two previous screenshots.

Total Cost: K802,150.67
Fortnights: 1040

K5000 Extra Payment

Say you paid K5000 extra in the 2nd fortnight and no more extra.

We will use the 2nd payment schedule under orange title.

You will notice from below that payments stop earlier at 1009 fortnights.

With K5,000 extra payment, total cost of loan will be K778,060.71.

K20,000 Extra Payment

Assume you paid an extra K5,000 over the first 4 fortnights. That is K20,000 total extra payments.

With this, payments will stop at 924 fortnights.

With K20,000 extra payment made, total cost of loan will be K712,102.16

K50,000 Extra Payment

Now let’s assume you paid K50,000 extra on one of the fortnights.

Your payments will now end at 781 fortnights.

Total cost of loan will be K602,210.82

Summarized Results for Extra Payments

Our home loan details again:

Loan Amount = K400,000
Interest Rate = 4% pa
Time = 40 years (1040 fortnights)
Extra payments????

Results

No Extra Payments: Total Cost = K802,150.67, Fortnights = 1040
K5,000 Extra Payment: Total Cost = K778,060.71, Fortnights = 1009
K20,000 Extra Payment: Total Cost = K712,102.16, Fortnights = 924
K50,000 Extra Payment: Total Cost = K602,210.82, Fortnights = 781

Conclusion

Choose a loan that allows extra payments. Make as much of these payments as possible to cut down the cost and time of your mortgage.

About the author: I work at the University of Papua New Guinea’s ICT Division and also develop Messenger Chatbots at Eternal Realm Software.

Written by

Written by