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

Hi,

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??

Thanks
SWD

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. (http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_21785434.html)
###### Who is Participating?

x
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.

Commented:

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 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.

SWD
Commented:
Try:

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

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

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

Cheers
SWD
Commented:
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
Commented:
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
PHP

From novice to tech pro — start learning today.