Calculating Mortgage Payments

paulocon

Hi,

Just wondering if anyone out there knows the formula for calculating Mortgage Payments (Principal & Interest) given an interest rate, loan amount, and repayment term?

Need this for a project I am working on and can't find it anywhere on the web...

daveirl

This post has been deleted.

paulocon

Dave,

Thanks for that but came across it before. I am more looking for the methodology of working out the repayments.

Regards,

Paul

none

Sorry for dragging it up but I'm also interested in the same thing and couldn't seem to find an answer here. What is the Mortgage Payments formula?? Anyone?
The compound interest is not the one used here, even the U.S. monthly mortgage payments formula appear to be producing slightly different results (for AIB it's about 4 Euro off the mark).

ferga_com

(1000*A/12)/(1-(1/(1+A/12)^(B*12)))*(C/1000)

The above is a formula for calculating the monthly repayment on a repayment mortgage where A is the rate, B is the term in years and C is the amount of the mortgage.

none

That seems like that American formula. Asked it AIB and BoI mortgage advisors and they all seemed stunned. And I'm even more when such an essential question knocks them down. They all used to have a computer that yields cross-reference tables but have no idea what's driving those tables. I really can't understand how people can commit to such huge expenses without know where these figures are coming from. :eek:

none

OK, got some info on this and now I can even see where banks are lying when they put so-called Mortgage Calculators on their sites. Here's HTML/JavaScript I'm using (basically, it's Period Rate fed into the standard Excel PMT function):

[PHP]
<script language="javascript">

var interest, term, amount, frequency;

function validate(){
var elem = document.getElementById("txtAmt");
if(elem && eval(elem.value)){
amount = eval(elem.value);
//alert(amount);
}else{
return false;
}
elem = document.getElementById("txtRate");
if((elem && eval(elem.value))){
interest = eval(elem.value);
//alert(interest);
}else{
return false;
}
elem = document.getElementById("txtTerm");
if((elem && eval(elem.value))){
term = eval(elem.value);
//alert(term);
}else{
return false;
}
elem = document.getElementById("txtFreq");
if((elem && eval(elem.value))){
frequency = eval(elem.value);
//alert(frequency);
return true;
}else{
return false;
}
}

function PMT(prate, term, amount){
return amount * prate / (1 - (1 / Math.pow( 1 + prate, term)));
//(1-(1/(1+ (0.12/12)) ^ 12))
}

function doCalc(){
var prate, repayment;
var elem;
if (validate()){
prate= Math.pow((1 + interest/term), (term/frequency)) - 1;
//prate= Math.pow((1 + interest/4), (4/frequency)) - 1;
elem = document.getElementById("txtPRate");
if(elem){
elem.value = prate;
//alert("Period rate:\t" + prate);
}
elem = document.getElementById("txtPMTRepmt");
if(elem){
elem.value = PMT(interest, term, amount);
//elem.value = PMT(prate, term, amount);
}

repayment = PMT(prate, term * frequency, amount)
elem = document.getElementById("txtRepmt");
if(elem){
elem.value = repayment;
//alert("Repayment:\t" + repayment);
}
}else{
alert("NOT VALID");
}
}

</script>

<div style="font-weight:bold;font-size:18;text-align:center">Mortgage repayment calculator</div>
<hr/>
<table class="menuColFrameTab" cellspacing="2" cellpadding="2">
<tr><td style="font-weight:bold;width:200">Amount</td><td style="font-weight:bold"><input id="txtAmt" type="text"/></td></tr>
<tr><td style="font-weight:bold">Rate</td><td style="font-weight:bold"><input id="txtRate" type="text"/></td></tr>
<tr><td style="font-weight:bold">Term (years)</td><td style="font-weight:bold"><input id="txtTerm" type="text"/></td></tr>
<tr><td style="font-weight:bold">Frequency (per annum)</td><td style="font-weight:bold"><input id="txtFreq" type="text"/></td></tr>
<tr><td style="font-weight:bold">Period rate</td><td style="font-weight:bold"><input id="txtPRate" type="text"/></td></tr>
<tr><td style="font-weight:bold">PMT Repayment</td><td style="font-weight:bold"><input id="txtPMTRepmt" type="text"/></td></tr>
<tr><td style="font-weight:bold">Mortgage Repayment</td><td style="font-weight:bold"><input id="txtRepmt" type="text"/></td></tr>
<tr><td style="font-weight:bold"> </td><td style="font-weight:bold"> </td></tr>
<tr><td style="font-weight:bold"><input type="submit" value="Calculate" onclick="javascript:doCalc();"/></td><td style="font-weight:bold"> </td></tr>
</table>

[/PHP]

I checked both AIB and BoI and found some mistakes. But first, I still have one unanswered question - How Period Rate is calculated?

[PHP]
prate= Math.pow((1 + interest/term), (term/frequency)) - 1;
//prate= Math.pow((1 + interest/4), (4/frequency)) - 1;
[/PHP]

The second formula is what I got from BoI and it seems to be unconditionally using 4. I did some guesswork and realised that it might in fact be the real term of the loan rather than hardcoded value 4. But I have no proof it should be the term and not hardcoded 4. Actually, for AIB Calculator the "4" version is 100% (with rounding) while for BoI both versions give a slight error (usually less than 1 Euro). This is strange as I got this formula from BoI and their own rates show some discrepancies. Yes, I didn't really get it officially, it was just a sample screenshot from the internal application used for Mortgages so I had to do all the math (coding+testing) myself.

Anyway, this formula helped me to figure out that AIB's rates are actually wrong on their site. When I talked to an AIB advisor, I remember their internal rates were 0.03% less than advertised online, e.g., 2.65% instead of 2.68%, 2.45% instead of 2.48%, 2.25% instead of 2.28%. The formula just proved it. Not sure why they show wrong interest while behind the scenes use a different one for calculations. Moreover, what they show is actually worse than what they offer.

For BoI, there also seems to be one advertising mistake where in reality the rate is 2.25% instead of 2.3%. Again, strange they show the worse rather than better rate. Also, as I said, for BoI it's not clear which version of Period Rate formula ("4" or "term") is used as they both produce marginally different results.

p.s.These are the figures I got for 300K from AIB and BoI online Calculators.
[PHP]

---

|

---

|

---

|

---

|

---

AIB for 300000 EUR | 20yrs | 25yrs | 30yrs | 35yrs

---

|

---

|

---

|

---

|

---

APR (LTV > 80%) | 2.68% | 2.68% | 2.68% | 2.68%
Monthly Repayment | 1611.00 EUR | 1368.00 EUR | 1208.00 EUR | 1096.00 EUR
Total paid | 386640.00 EUR | 410400.00 EUR | 434880.00 EUR | 460320.00 EUR

---

|

---

|

---

|

---

|

---

APR (80% > LTV > 50%) | 2.48% | 2.48% | 2.48% | 2.48%
Monthly Repayment | 1582.00 EUR | 1338.00 EUR | 1177.00 EUR | 1064.00 EUR
Total paid | 379680.00 EUR | 401400.00 EUR | 423720.00 EUR | 446880.00 EUR

---

|

---

|

---

|

---

|

---

APR (50% > LTV) | 2.28% | 2.27% | 2.27% | 2.27%
Monthly Repayment | 1553.00 EUR | 1308.00 EUR | 1146.00 EUR | 1032.00 EUR
Total paid | 372720.00 EUR | 392400.00 EUR | 412560.00 EUR | 433440.00 EUR

---

|

---

|

---

|

---

|

---

---

|

---

|

---

|

---

|

---

BoI for 300000 EUR | 20yrs | 25yrs | 30yrs | 35yrs

---

|

---

|

---

|

---

|

---

APR (LTV > 80%) | 2.6% | 2.6% | 2.6% | 2.6%
Monthly Repayment | 1604.36 EUR | 1361.00 EUR | 1201.01 EUR | 1088.63 EUR
Total paid | 385046.40 EUR | 408300.00 EUR | 432363.60 EUR | 457224.60 EUR

---

|

---

|

---

|

---

|

---

APR (80% > LTV > 50%) | 2.4% | 2.4% | 2.4% | 2.4%
Monthly Repayment | 1575.13 EUR | 1330.79 EUR | 1169.82 EUR | 1056.47 EUR
Total paid | 378031.20 EUR | 399237.00 EUR | 421135.20 EUR | 443717.40 EUR

---

|

---

|

---

|

---

|

---

APR (50% > LTV) | 2.3% | 2.3% | 2.3% | 2.3%
Monthly Repayment | 1553.42 EUR | 1308.39 EUR | 1146.73 EUR | 1032.70 EUR
Total paid | 372820.80 EUR | 392517.00 EUR | 412822.80 EUR | 433734.00 EUR

---

|

---

|

---

|

---

|

---

[/PHP]

addisonadam

ya none this program is working good thanks for the post....

__________________
Compound Interest Formula

none

So can anyone tell me what "4" means in the below formula as I suspect it is the one used for Mortgage calculations rather than the "term" version?
[PHP]prate= Math.pow((1 + interest/4), (4/frequency)) - 1; [/PHP]
[PHP]prate= Math.pow((1 + interest/term), (term/frequency)) - 1;[/PHP]