Time Value of Money – Excel PMT in JavaScript and PHP
I’m currently working on a project that happens to need a JavaScript and a PHP implementation of the Excel PMT function. I won’t bore you with the details (but ask if you’re intrigued), but it has to do with Loan Modifications that are all the rage nowadays thanks to Obama Administration initiatives that will hopefully help Americans save their mortgages, prevent foreclosures, support real estate prices and generally speaking save the world from sure doom.
So after being handed an Excel spreadsheet that made heavy use of the PMT Excel function (and never having used said function), I proceeded to research this beast. Let me tell you, there are lots of very confused people searching for solutions on how to implement it in various languages, trying to figure out how the function works, etc, etc, etc. I even came across some folks that think Excel / Microsoft is behind a sinister plot to undermine the world’s economy via a wrong implementation of the function. Rest assured, I think Microsoft understands the Time Value of Money – TMV for those who still remember anything from their Finance class in college. So I figured I’d do a little writeup of what I figured out to save somebody else the headache.
The basic formula is easy, in Javascript:
pmt = i * p * Math.pow((1 + i), n) / (1 – Math.pow((1 + i), n));
where i is the interest rate, p is the principal and n is the number of periods. Some things to watch out for – typically you want the monthly payment value, and the interest rate is typically an annual rate. Additionally, remember that the interest rate is typically specified in percent, so for a value like 3.5%, you’ll need to feed it to the function as 0.035. Also, multiplying the principal by -1 will save you the trouble of trying to figure out why the function gives you negative values, which makes sense in several ways (the mortgage is a debt or perhaps the payment is subtracted from the principal, maybe something to do with the fact that I’m not including the future value in my equation – I honestly don’t know the math well enough to explain that well). The complete function is below. Also with some jQuery help, here’s a function that takes the DOM ids of 3 form fields that contain the loan amount, the interest rate and the number of months over which the payments are to be made. The last id is the field to place the result in:
function PMT(i, n, p) {
return i * p * Math.pow((1 + i), n) / (1 - Math.pow((1 + i), n));
}
function CalculatePMTFromForm(idLoanAmount, idAnnualInterestRate, idMonths, idResult) {
var i = $('#' + idAnnualInterestRate).val() / 1200;
var n = $('#' + idMonths).val();
var p = $('#' + idLoanAmount).val();
var pmt = PMT(i, n, -p);
$('#' + idResult).val(pmt.toFixed(2));
}
And here’s a little calculator for you:
In PHP the function is:
function PMT($i, $n, $p) {
return $i * $p * pow((1 + $i), $n) / (1 - pow((1 + $i), $n));
}
echo(number_format(PMT(2.1 / 1200, 360, -100000),2)); // displays 374.64
Tags: excel, javascript, loan, mod, PHP, pmt, tvm
August 15th, 2009 at 10:09 am
thanks for sharing. how about PV and Fv function to calculate annuity.
September 22nd, 2009 at 11:39 am
Hi, i spend a lot of time trying to make this (in php) and many hours on google looking for this. Thankssssssss
September 27th, 2009 at 4:27 am
Thnaks alot!
January 27th, 2010 at 4:04 am
In php –
return $i * ($p * pow((1 + $i), $n) + $fv) / (1 – pow((1 + $i), $n));
will allow for “future value” functionality – Don’t forget to add the $fv variable into your function call
These equations works for payment in arrears only.. Still scratching my head as to how to formulate the PMT as a payment in advance.
Thanks for the article. It’s been the backbone of calculator I’ve been trying to complete.
March 4th, 2010 at 10:28 am
This is really helpful – thank you.
May 15th, 2010 at 11:10 am
just determined how to get payment at beginning of period …
intPayment = -(intInterestRate * intPrincipal * Math.pow((1 + intInterestRate), intMonthCount – intFlagPmtBegPeriod) / (1 – Math.pow((1 + intInterestRate), intMonthCount)));
I changed the variable names, and added the negative sign, but otherwise same as original, except for the flag.
// intInterestRate = Interest rate per month (.05 / 12, .06 / 12, .07 / 12, …).
// intPrincipal = Amount to borrow.
// intYearCount = Number of years to pay back loan.
// intFlagPmtBegPeriod = Whether payment occurs at beginning of period (month). 1 = Yes. 0 = No.
August 30th, 2010 at 10:18 am
Hi
i think there is something wrong with the formula
40000
7.99
60
=810.86
this is right..
30000
7.99
60
=608.15
this is wrong it should be 675
in excel
=pmt(7.99/12;60;50000-10000;-1000;0)*1
gr.
August 31st, 2010 at 1:21 am
Weird, I get 608.15 in Numbers on Mac OSX
50000 – 10000 = 40000 btw, looks like the example you pasted above has 30000.
August 31st, 2010 at 3:32 am
hi ivan
nop it isn’t 40000 it’s 30 0000
it’s 50000-10000… and then -10000.. it’s 30 000 but it’s diffrent in excel
the second -10000 that’s the problem i think
gr.
August 31st, 2010 at 3:37 am
Yes, the code above isn’t meant to handle all the parameters, just the three I needed. Sorry about that. What do the other two parameters represent, perhaps I can help you code an alternative.
August 31st, 2010 at 3:53 am
ps. if you email me
i can send you the excel
gr.
August 31st, 2010 at 4:48 am
if i look at the excel (send it to you info at tumanov.com)
it’s the 4de parameter [fv] what the problem is..
gr.
August 31st, 2010 at 8:02 am
hi
i have it working now
function PMT(i, n, p, fn) {
return (i * (p * Math.pow((1 + i), n) + fn))/ (1 – Math.pow((1 + i), n)) ;
}
gr.
October 25th, 2010 at 5:47 am
INT * ((1 + INT) ** PAYNO)
PMT = PV * ——————————–
((1 + INT) ** PAYNO) – 1
or, PMT = PV * ($int * pow((1 + $int), $payno)) / (pow((1 + $int), $payno) – 1)
im using this formula…its easy to work.
please try it
October 17th, 2011 at 3:57 pm
Thanks man, this function helped me heaps.
November 20th, 2011 at 3:45 pm
Hi,
Has anyone got a full working copy of the php version. I’m trying to return out the result as a variable to send to a results page.
thank in advance anyone…
February 3rd, 2012 at 9:13 am
function PMT ($Rate, $Nper, $Pv, $Fv, $myType)
{
$gd_i = $Rate/12;
$gd_i100 = $gd_i/100;
$gd_i1 = $gd_i100+1;
$gd_ipow = 1/pow( $gd_i1, $Nper );
$gd_p0 = -$Pv-$Fv*$gd_ipow;
$gd_p100 = $gd_p0*$gd_i100;
$nachschuss = $gd_p100/(1-$gd_ipow);
return $nachschuss/(1+$gd_i100*$myType);
}
January 27th, 2023 at 9:03 am
phoenix seo scottsdale…
Hire a top phoenix marketing agencies currently now available and on sale now only!…
May 10th, 2023 at 5:39 am
criminal lawyers near me…
https://sites.google.com/view/criminallawyermissoula/defense attorney that is this week available in addition currently available this week only!…
May 11th, 2023 at 3:02 pm
dui lawyer near me…
Save now the best shoplifting attorney…
May 14th, 2023 at 10:27 am
criminal lawyer…
Save now the top federal defense lawyer…
May 29th, 2023 at 7:11 am
best criminal defense attorney near me…
https://sites.google.com/view/criminallawyermissoula/theft attorneys that are this week available and at the best price this week only!…
June 7th, 2023 at 2:32 pm
click to read more…
Locate now the top dogs in london that’s this week available in addition reasonably priced this week only!…