Mortgage interest

Forums › Off topic › Mortgage interest

rayonline

1734 posts

Uber Geek

#142430 12-Mar-2014 15:44

Newbie question on but not for me. I am compiling a spreadsheet like numbers myself ... let's say a fixed interest rate is that calculated monthly? Ie - you take the fixed % for the amount of the loan then per month ... subtract the payments you make and this cycle repeats for the next month?

Cheers

blackjack17

1705 posts

Uber Geek

#1004371 12-Mar-2014 16:01

This is what I did, when my wife and I were wanting to look at different options.

and the formulas

Electric kiwi signup credit Sharesies credit

mattwnz

20141 posts

Uber Geek

#1004395 12-Mar-2014 16:36

That reminds me that rates are likely to rise tomorrow.

nzkc

1571 posts

Uber Geek

#1004423 12-Mar-2014 16:55

If you're doing this in Excel, it has functions to help you. E.g.: http://office.microsoft.com/en-nz/excel-help/plan-payments-and-savings-with-excel-HA001056286.aspx

Edit: As does Google Docs: https://support.google.com/drive/table/25273?hl=en

Aredwood

3885 posts

Uber Geek

#1004565 12-Mar-2014 21:19

Just confirm how they calculate it. The banks normally calculate interest daily. But will only add it onto the amount owing monthly.

eracode

Smpl Mnmlst
8847 posts

Uber Geek

ID Verified

Trusted

Subscriber

#1004652 13-Mar-2014 03:28

It's not clear whether your loan payments will be 'principal & interest' or 'interest-only'. Whichever it is, unless you know how the bank calculates it, you may struggle to accurately predict or reconcile your payments to what the bank will charge for each payment period (if that's what you are trying to do).

As commented above, the PMT function in Excel can be a great help and may save you having to build much of the spreadsheet from scratch.

If the loan payments are P&I and the loan rate is fixed for a period, the periodic payments will all be constant for that period - but the split/allocation between interest payment and principal reduction will be different for each payment i.e. not a straight line reduction. The earlier payments are mainly interest and only a little in principal reduction. As the loan progresses and as the principal is reduced, proportionately more of each payment goes to principal and less to interest. The Excel PMT function or its near relatives can show you how much of each payment is going to interest and how much to principal.

You may find this helpful:

http://exceluser.com/formulas/term-loan-amortization.htm

'Amortizing' simply means 'principal reducing' or P&I.

Edit: All the above assumes a mortgage home loan from a mainstream bank. There are different types of loans and your OP does not say what you are looking at.

Sometimes I just sit and think. Other times I just sit.

eracode

Smpl Mnmlst
8847 posts

Uber Geek

ID Verified

Trusted

Subscriber

#1004654 13-Mar-2014 04:11

Aredwood: Just confirm how they calculate it. The banks normally calculate interest daily. But will only add it onto the amount owing monthly.

This is not correct for a normal mortgage home loan - see above. However it is correct for a 'revolving credit' type home loan eg BNZ's Rapid Repay Home Loan - which is basically an overdraft facility secured by a mortgage over your property and at a home loan interest rate rather than a standard OD rate.

Sometimes I just sit and think. Other times I just sit.

bazzer

3438 posts

Uber Geek

Trusted

#1004704 13-Mar-2014 09:43

eracode:
Aredwood: Just confirm how they calculate it. The banks normally calculate interest daily. But will only add it onto the amount owing monthly.

This is not correct for a normal mortgage home loan - see above. However it is correct for a 'revolving credit' type home loan eg BNZ's Rapid Repay Home Loan - which is basically an overdraft facility secured by a mortgage over your property and at a home loan interest rate rather than a standard OD rate.

Are you sure? This is exactly how ASB calculates their mortgage payments (at least my spreadsheet matches the payments I'm making).

Certainly the payment amounts will be constant (and probably calculated using a basic amortisation function) and the split will change as the balance of the loan decreases. In practice however, the interest charged each payment is a function of the interest rate, the loan balance and the number of days between payments. If you have monthly payments, check out February, you always pay much less interest (and therefore more principal) due to the lower number of days. I've never had a fortnightly mortgage so that might be different (i.e. there are always 14 days in a fortnight).

Edit: I may have misinterpreted both Aredwood's post and your response. They don't compound interest daily (I don't think they even do that on revolving credit loans) but they do calculate it daily. And they don't really "add it onto" a table loan, rather they "subtract it off" I guess, but the principle (haha) is the same.

Equinox IT

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.

ObidiahSlope

260 posts

Ultimate Geek

#1004748 13-Mar-2014 10:54

You can approximate the split between the interest and principal in each payment of a standard amortizing loan by sketching a simple diagram;

On a sheet of paper draw a square. Mark the left hand margin or Y axis a the amount of each instalment payment, and the bottom margin or X axis as the term of the loan broken up into payment dates.

Draw the arc of a circle with point of origin in the top left corner and finishing in the bottom right hand corner.

For each payment on the X axis the amount of the payment below the arc is allocated to the payment of interest and above is allocated to the reduction of the principal.

As you can see from the sketch at the start of the loan most of the payment is allocated to interest and at the end most of the payment is allocated to principal.

As a rule of thumb for a thirty year mortgage it takes twenty years to pay off half the loan.

Obsequious hypocrite

hangon

397 posts

Ultimate Geek

#1004799 13-Mar-2014 11:48

http://www.interest.co.nz/calculators/mortgage-calculator

for interest only repayment (revolving credit, or interest only mortgage), put the full amount in the balloon payment field.

bazzer

3438 posts

Uber Geek

Trusted

#1004826 13-Mar-2014 12:25

Or use the all-purpose: