Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

rayonline

1613 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

874 posts

Ultimate 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

mattwnz

15029 posts

Uber Geek

# 1004395 12-Mar-2014 16:36

That reminds me that rates are likely to rise tomorrow.

nzkc

656 posts

Ultimate 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

Aredwood

3885 posts

Uber Geek

Subscriber

# 1004565 12-Mar-2014 21:19
One person supports this post

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

eracode

Underlying overtones
3667 posts

Uber Geek

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.

eracode

Underlying overtones
3667 posts

Uber Geek

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.

bazzer

3295 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.

ObidiahSlope

234 posts

Master 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

392 posts

Ultimate Geek

# 1004799 13-Mar-2014 11:48

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

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

bazzer

3295 posts

Uber Geek

Trusted

# 1004826 13-Mar-2014 12:25

Or use the all-purpose:

Follow us to receive Twitter updates when new discussions are posted in our forums:

Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:

Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:

News »

Intel expands 10th Gen Intel Core Mobile processor family
Posted 23-Aug-2019 10:22

Digital innovation drives new investment provider
Posted 23-Aug-2019 08:29

Catalyst Cloud becomes a Kubernetes Certified Service Provider (KCSP)
Posted 23-Aug-2019 08:21

New AI legaltech product launched in New Zealand
Posted 21-Aug-2019 17:01

Yubico launches first Lightning-compatible security key, the YubiKey 5Ci
Posted 21-Aug-2019 16:46

Disney+ streaming service confirmed launch in New Zealand
Posted 20-Aug-2019 09:29

Industry plan could create a billion dollar interactive games sector
Posted 19-Aug-2019 20:41

Personal cyber insurance a New Zealand first
Posted 19-Aug-2019 20:26

University of Waikato launches space for esports
Posted 19-Aug-2019 20:20

D-Link ANZ expands mydlink ecosystem with new mydlink Mini Wi-Fi Smart Plug
Posted 19-Aug-2019 20:14

Kiwi workers still falling victim to old cyber tricks
Posted 12-Aug-2019 20:47

Lightning Lab GovTech launches 2019 programme
Posted 12-Aug-2019 20:41

Epson launches portable laser projector
Posted 12-Aug-2019 20:27

Huawei launches new distributed HarmonyOS
Posted 12-Aug-2019 20:20

Lenovo introduces single-socket servers for edge and data-intensive workloads
Posted 9-Aug-2019 21:26

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.