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!…
June 26th, 2023 at 6:13 pm
More about the author…
Hire the best deltamath answers that’s now available in addition at reasonable prices now only!…
June 26th, 2023 at 11:26 pm
lock smith for cars…
Buy the top quality deals for lock smith for cars available for you and ready to s and plus at a reasonabe value for today only!…
July 2nd, 2023 at 4:50 pm
commercial fence company…
Hire now the best residential fence installation that is this week available and on sale this week only!…
July 6th, 2023 at 11:07 am
defense lawyers…
Save now the top criminal attorney…
July 10th, 2023 at 2:03 pm
More about the author…
Get the top deals for key smiths near me available for you and for fast deliver and plus at a good value for today only!…
July 21st, 2023 at 12:12 pm
london flats chelsea…
Save now the best pet friendly flats london currently this week available in addition at great prices this week only!…
July 28th, 2023 at 11:39 pm
relocation agent london…
Locate now the best relocation company london that are this week available in addition on sale this week only!…
July 29th, 2023 at 12:08 pm
phoenix seo…
Hire the best phoenix seo service that is now available and with reasonably pricing now only!…
August 1st, 2023 at 3:53 am
auto accident attorney nebraska…
Get the most professional personal injury lawyer attorney that is available now!…
August 1st, 2023 at 4:14 am
auto accident lawyers…
Find here a top bellevue personal injury attorney currently in service now!…
August 1st, 2023 at 9:29 am
london luxury flats for sale…
Locate now a high quality moving to london with a dog currently this week available in addition on sale this week only!…
August 5th, 2023 at 1:24 pm
see here…
Hire the best bee removal that is now available and at good rates now only!…
August 6th, 2023 at 3:14 am
rat exterminator…
Hire the the top mosquito control that’s now available and with reasonably pricing now only!…
August 9th, 2023 at 10:04 pm
top article…
Find the the top mesa marketing companies that is now available and with reasonably pricing now only!…
August 12th, 2023 at 7:16 am
termite control near me…
Hire the the top best pest control near me currently now available and at reasonable prices now only!…
August 17th, 2023 at 10:45 pm
lost key replacement cost…
Find the top quality deals for locksmith missoula montana available for you and for fast deliver and at a good value for today only!…
August 18th, 2023 at 2:56 am
fence companies hamilton…
Hire now a high quality private fence that are this week available and on sale this week only!…
August 28th, 2023 at 4:03 pm
aleks answer key chemistry…
Find a top do my accounting homework that’s now available in addition on sale now only!…
September 10th, 2023 at 11:15 pm
click here for more info…
Hire the the top phoenix seo that’s now available in addition on sale now only!…
September 19th, 2023 at 4:52 pm
venue insurance…
Get going with the best bed and breakfast insurance policy now on a best offer and gets your money flowing immediately!…
October 13th, 2023 at 2:45 pm
24 hour emergency locksmith service…
Buy amazing deals for residential locksmiths available for you and for fast deliver and at a great value for today only!…
October 15th, 2023 at 1:34 pm
elephant hunting namibia…
Start the year the right way with what animals are in namibia africa? that’s providing a fantastic ROI to get started now!…
October 20th, 2023 at 10:16 pm
phoenix website design…
Hire the the top phoenix seo service that is now available and on sale now only!…
October 27th, 2023 at 12:22 pm
bathtub reglazing los angeles…
Get going with the best refinish bathtub and tile today on a best offer and creates efficiency for you immediately!…
October 27th, 2023 at 1:13 pm
tub resurfacing…
Get going with top quality tile refinishing today on a best offer and creates efficiency for you immediately!…
November 2nd, 2023 at 8:20 am
music festival near me…
See the top option for caveman weston colorado music festival now in stock plus reasonably priced you can get now!…
November 5th, 2023 at 12:13 am
red rock colorado music festival…
See the brightest new weston colorado live music americana festival now in stock in addition reasonably priced you can get now!…
November 21st, 2023 at 4:21 am
check my blog…
Locate highly amazing utility bill printing and mailing services today on a best offer and gets your money flowing immediately!…
November 22nd, 2023 at 4:39 pm
bathtub reglazing services near me…
Get the best tub resurfacing currently now available and with reasonably pricing now only!…
November 25th, 2023 at 12:51 am
car cleaning near me…
Locate highly amazing mobile car detailing near me now on a best offer and creates efficiency for you immediately!…
November 27th, 2023 at 1:16 am
pressure washing companies near me…
Locate the best power washing services immediately on a best offer and creates efficiency for you immediately!…
November 27th, 2023 at 6:32 am
Best Postcard Mailing Services…
Get going with top quality letter printing and mailing services today on a best offer and creates efficiency for you immediately!…
November 27th, 2023 at 5:02 pm
print to mail service…
Procure the best statement printing and mailing today on a best offer and gets your money flowing immediately!…
November 30th, 2023 at 9:34 pm
page…
Locate top quality Best Post Card Mailings today on a best offer and gets your money flowing immediately!…
December 2nd, 2023 at 8:11 am
social seo scottsdale…
Locate highly amazing scottsdale marketing firms today on a best offer and gets your money flowing immediately!…
December 7th, 2023 at 6:29 am
locksmith commercial…
Find the top deals for lock smiths near me available for you and ready to s and plus at a great value for today only!…