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

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

  18. phoenix seo scottsdale Says:

    phoenix seo scottsdale…

    Hire a top phoenix marketing agencies currently now available and on sale now only!…

  19. criminal lawyers near me Says:

    criminal lawyers near me…

    https://sites.google.com/view/criminallawyermissoula/defense attorney that is this week available in addition currently available this week only!…

  20. dui lawyer near me Says:

    dui lawyer near me…

    Save now the best shoplifting attorney…

  21. criminal lawyer Says:

    criminal lawyer…

    Save now the top federal defense lawyer…

  22. best criminal defense attorney near me Says:

    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!…

  23. click to read more Says:

    click to read more…

    Locate now the top dogs in london that’s this week available in addition reasonably priced this week only!…

  24. More about the author Says:

    More about the author…

    Hire the best deltamath answers that’s now available in addition at reasonable prices now only!…

  25. lock smith for cars Says:

    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!…

  26. commercial fence company Says:

    commercial fence company…

    Hire now the best residential fence installation that is this week available and on sale this week only!…

  27. defense lawyers Says:

    defense lawyers…

    Save now the top criminal attorney…

  28. More about the author Says:

    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!…

  29. london flats chelsea Says:

    london flats chelsea…

    Save now the best pet friendly flats london currently this week available in addition at great prices this week only!…

  30. relocation agent london Says:

    relocation agent london…

    Locate now the best relocation company london that are this week available in addition on sale this week only!…

  31. phoenix seo Says:

    phoenix seo…

    Hire the best phoenix seo service that is now available and with reasonably pricing now only!…

  32. auto accident attorney nebraska Says:

    auto accident attorney nebraska…

    Get the most professional personal injury lawyer attorney that is available now!…

  33. auto accident lawyers Says:

    auto accident lawyers…

    Find here a top bellevue personal injury attorney currently in service now!…

  34. london luxury flats for sale Says:

    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!…

  35. see here Says:

    see here…

    Hire the best bee removal that is now available and at good rates now only!…

  36. rat exterminator Says:

    rat exterminator…

    Hire the the top mosquito control that’s now available and with reasonably pricing now only!…

  37. top article Says:

    top article…

    Find the the top mesa marketing companies that is now available and with reasonably pricing now only!…

  38. termite control near me Says:

    termite control near me…

    Hire the the top best pest control near me currently now available and at reasonable prices now only!…

  39. lost key replacement cost Says:

    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!…

  40. fence companies hamilton Says:

    fence companies hamilton…

    Hire now a high quality private fence that are this week available and on sale this week only!…

  41. aleks answer key chemistry Says:

    aleks answer key chemistry…

    Find a top do my accounting homework that’s now available in addition on sale now only!…

  42. click here for more info Says:

    click here for more info…

    Hire the the top phoenix seo that’s now available in addition on sale now only!…

  43. venue insurance Says:

    venue insurance…

    Get going with the best bed and breakfast insurance policy now on a best offer and gets your money flowing immediately!…

  44. 24 hour emergency locksmith service Says:

    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!…

  45. elephant hunting namibia Says:

    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!…

  46. phoenix website design Says:

    phoenix website design…

    Hire the the top phoenix seo service that is now available and on sale now only!…

  47. bathtub reglazing los angeles Says:

    bathtub reglazing los angeles…

    Get going with the best refinish bathtub and tile today on a best offer and creates efficiency for you immediately!…

  48. tub resurfacing Says:

    tub resurfacing…

    Get going with top quality tile refinishing today on a best offer and creates efficiency for you immediately!…

  49. music festival near me Says:

    music festival near me…

    See the top option for caveman weston colorado music festival now in stock plus reasonably priced you can get now!…

  50. red rock colorado music festival Says:

    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!…

  51. check my blog Says:

    check my blog…

    Locate highly amazing utility bill printing and mailing services today on a best offer and gets your money flowing immediately!…

  52. bathtub reglazing services near me Says:

    bathtub reglazing services near me…

    Get the best tub resurfacing currently now available and with reasonably pricing now only!…

  53. car cleaning near me Says:

    car cleaning near me…

    Locate highly amazing mobile car detailing near me now on a best offer and creates efficiency for you immediately!…

  54. pressure washing companies near me Says:

    pressure washing companies near me…

    Locate the best power washing services immediately on a best offer and creates efficiency for you immediately!…

  55. Best Postcard Mailing Services Says:

    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!…

  56. print to mail service Says:

    print to mail service…

    Procure the best statement printing and mailing today on a best offer and gets your money flowing immediately!…

  57. page Says:

    page…

    Locate top quality Best Post Card Mailings today on a best offer and gets your money flowing immediately!…

  58. social seo scottsdale Says:

    social seo scottsdale…

    Locate highly amazing scottsdale marketing firms today on a best offer and gets your money flowing immediately!…

  59. locksmith commercial Says:

    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!…

  60. seo company scottsdale az Says:

    seo company scottsdale az…

    Locate the best best scottsdale seo company today on a best offer and creates efficiency for you immediately!…

  61. child Visitation lawyer Says:

    child Visitation lawyer…

    Locate now the top child custody attorney that’s this week available in addition at the best price this week only!…

  62. residential home builders near me Says:

    residential home builders near me…

    Locate the best home builder in whitefish mt immediately on a best offer and gets your money flowing immediately!…

  63. new home builders whitefish Says:

    new home builders whitefish…

    Locate the best new home builders whitefish immediately on a best offer and gets your business ROI immediately!…

  64. african bow hunting safari Says:

    african bow hunting safari…

    Start the year the right way with hunting cape buffalo with a bow that’s an awesome strategy to get started now!…

  65. hunting african elephants Says:

    hunting african elephants…

    Get going right now with with what animals are in namibia africa? that’s an awesome strategy to get started right now!…

  66. insurance bed and breakfast Says:

    insurance bed and breakfast…

    Locate top quality insurance for lodges immediately on a best offer and creates efficiency for you immediately!…

  67. bed and breakfast insurance rates Says:

    bed and breakfast insurance rates…

    Locate top quality hotel insurance cost immediately on a best offer and gets your business ROI immediately!…

Leave a Reply