How do I convert this Excel formula into something I can use in PHP?

Posted on 2006-03-23
Last Modified: 2011-09-20

I'm creating a mortgage calculator for a client and he's given me a spreadsheet with the formulas in. The problem is that the formula uses an Excel function called PMT. This is the formula:

= - PMT ( R/12 , T*12 , A , 0 , 0 )

where R is the interest Rate (e.g. 10%)
T is the Term of the loan (e.g. 30 years)
and A is the Amount of the loan (e.g. 100,000)

The question is, how do I convert this into something I can put in PHP code to do the work on a webpage instead of in a spreadsheet??


Please note: I had already asked this question but incorrectly put it in the Excel forum - it has since been moved to the PHP forum but I was concerned that it was getting the visibility that it would if I'd submitted it here straight away. I have requested that the existing question be removed. (
Question by:SurreyWebDesigner
    LVL 11

    Expert Comment


    This will probably give you what you want, but slightly different from the Excel equivalent, so you'll have to play with it a little:

       T = Term of the loan in years
       R = Annual interest rate expressed as a Decimal (10% = 0.1)
       A = Amount of the loan.

       function PMT ($T, $R, $A ) {

          $x = 1 / (1 + ( $R / 12 ) );

          return ( ( 1 - $x ) * $A) / ($x * (1 - pow( $x, $T ) ) );


    Note that I haven't tested this against the Excel PMT, I've just re-written an old C++ function I have to achive the same in PHP.

    Author Comment

    Hi siliconbrit - thanks for posting ...

    This is what I now have:

    $varA = $_REQUEST['Amount'];
    $varR = $_REQUEST['Rate'];
    $varT = $_REQUEST['Term'];
    $x = 1 / (1 + ( $varR / 12 ) );
    $RepaymentAmount = round(( ( 1 - $x ) * $varA) / ($x * (1 - pow( $x, $varT ) ) ),2);

    But when I display $RepaymentAmount it comes out at £46042.59 (when I use 85,000 as the amount, 6.5 as the rate and 25 as the term).

    I actually use another formula to calculate the repayments on an interest only mortgage:

    $InterestOnlyAmount = round((($varA * $varR / 100) / 12), 2);

    Given the same values (85,000 - 6.5 and 25) it comes out to £460.42 per month (1/100th of the amount given for $RepaymentAmount).

    From what I'm told, using the PMT function in Excel to calculate the repayment amount would give a figure of £573.93 per month - so i'm a bit out at the moment.

    Thanks for your help.
    LVL 7

    Accepted Solution


    function PMT($rate, $terms, $amount)
          return $amount * ( $rate / (1 - pow((1 + $rate), -$terms)));

    echo PMT((6.5/100)/12, 25*12, 85000);
    LVL 7

    Expert Comment

    ...which prints out "573.9260871455", which is rounded to 573.93$.

    Author Comment

    Spot on!!! Thanks very much aib_42!!!

    LVL 1

    Expert Comment

    aib_42: this is perfect, what if there is a balance at the end of the period, how would it work. very similar to the one on excel
    LVL 1

    Expert Comment

    or with fv, future value

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to count occurrences of each item in an array.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now