Solved

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

Posted on 2006-03-23
Medium Priority
578 Views
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)
0
Question by:SurreyWebDesigner
• 2
• 2
• 2
• +1

LVL 11

Expert Comment

ID: 16271591

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

Author Comment

ID: 16271917
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
0

LVL 7

Accepted Solution

aib_42 earned 2000 total points
ID: 16279189
Try:

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

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

LVL 7

Expert Comment

ID: 16279190
...which prints out "573.9260871455", which is rounded to 573.93\$.
0

Author Comment

ID: 16279227
Spot on!!! Thanks very much aib_42!!!

Cheers
SWD
0

LVL 1

Expert Comment

ID: 34487649
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
0

LVL 1

Expert Comment

ID: 34487657
or with fv, future value
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
###### Suggested Courses
Course of the Month15 days, 16 hours left to enroll