Time Value of Money – Excel PMT in JavaScript and PHP

PMTI’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:

$

%

months

$

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: , , , , , ,

17 Responses to “Time Value of Money – Excel PMT in JavaScript and PHP”

  1. brokencode Says:

    thanks for sharing. how about PV and Fv function to calculate annuity.

  2. BeN Says:

    Hi, i spend a lot of time trying to make this (in php) and many hours on google looking for this. Thankssssssss

  3. ????????? Says:

    Thnaks alot!

  4. Stuart Says:

    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.

  5. Rupert Millard Says:

    This is really helpful – thank you.

  6. David C Says:

    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.

  7. dicker Says:

    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.

  8. Ivan Tumanov Says:

    Weird, I get 608.15 in Numbers on Mac OSX

    50000 – 10000 = 40000 btw, looks like the example you pasted above has 30000.

  9. dicker Says:

    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.

  10. Ivan Tumanov Says:

    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.

  11. dicker Says:

    ps. if you email me :)
    i can send you the excel

    gr.

  12. dicker Says:

    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.

  13. dicker Says:

    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.

  14. Kawsar Ahmad Says:

    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

  15. Anriëtte Says:

    Thanks man, this function helped me heaps.

  16. Richard Says:

    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…

  17. Mahesh Chimankar Says:

    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);
    }

Leave a Reply