Mortgage repayment calculation

P

Pexus1976

Registered User
Messages
189
Hello,

Does anybody know how to calculate mortgage repayments for capital & interest and interest only?

I know that there are 100's of calulators on the net but I would like to know the formula for these calculations. i.e. a term, b capital, c rate etc..
 
=pmt function in excel for the repayment including principal. You'll need to adjust the rate of interest to reflect monthly payments. Not just a case of dividing by 12. There's a formula for it.

(1 + r/12)12 -1 , r being the annual rate.


Interest only is fairly simple arithmetic.
 
cbyr1983 said:
=pmt function in excel for the repayment including principal. You'll need to adjust the rate of interest to reflect monthly payments. Not just a case of dividing by 12. There's a formula for it.

(1 + r/12)12 -1 , r being the annual rate.


Interest only is fairly simple arithmetic.
Click to expand...

Thanks for your replies.

So lets say for example the capital is €100k and the term is 30 years and the rate is 5%.

How does that fit into the above calculation for capital and interest?
 
Pexus1976 said:
So lets say for example the capital is €100k and the term is 30 years and the rate is 5%.
Click to expand...

Go into Excel, click into any cell, then in the formula bar on top put in, =pmt(5%/12,360,-100000) press enter and your monthly payment €536.82 appears in the selected cell.

For fortnightly repayments, =pmt(5%/26,780,-100000)

Lots of other ways to play around with it.
 
Is this based on interest acrued daily or monthly ? I put in the exact figures of my mortgage and the amount I pay v the amount the excel spreadsheet shows has a differance of about €19 per month i.e. I'm paying €19 a month more than the spreadsheet shows. Before I go storming into my bank about this I want to see whether this is a mistake in my calculations or in the banks.
 
elcato said:
Is this based on interest acrued daily or monthly ? I put in the exact figures of my mortgage and the amount I pay v the amount the excel spreadsheet shows has a differance of about €19 per month i.e. I'm paying €19 a month more than the spreadsheet shows. Before I go storming into my bank about this I want to see whether this is a mistake in my calculations or in the banks.
Click to expand...

Ah but are you entering the correct interest rate though! [broken link removed]
 
Can you use the link icon to enter the url as its truncated and is not finding it ?
 
elcato said:
Is this based on interest acrued daily or monthly ? I put in the exact figures of my mortgage and the amount I pay v the amount the excel spreadsheet shows has a differance of about €19 per month i.e. I'm paying €19 a month more than the spreadsheet shows. Before I go storming into my bank about this I want to see whether this is a mistake in my calculations or in the banks.
Click to expand...

I have no financial background, afaik it assumes interest is calculated daily, the same figures put into Jeacle's calculator give an identical result. Anytime a new interest rate was applied to my mortgage the excel amount would usually be slightly lower than the banks figure, because interest is charged daily, applied to the account monthly and my payment date is somewhere in between, I assume this is the reason for the difference.

A possible reason for your large difference could be when rates fall some apply the new rate but leave the payment the same, or recalculate on an annual basis or only change the payment if by not doing so it would extend the agreed term, I would certainly ask.
 
You must log in or register to reply here.
Back
Top