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