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


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. (
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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.
SurreyWebDesignerAuthor Commented:
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.

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

...which prints out "573.9260871455", which is rounded to 573.93$.
SurreyWebDesignerAuthor Commented:
Spot on!!! Thanks very much aib_42!!!

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
or with fv, future value
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.