asked on

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. (https://www.experts-exchange.com/questions/21785434/Converting-Excel's-PMT-function-to-use-in-PHP.html)

siliconbrit

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.

SurreyWebDesigner

ASKER

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

ASKER CERTIFIED SOLUTION

aib_42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aib_42

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

SurreyWebDesigner

ASKER

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

Cheers
SWD

mugsinc

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

mugsinc

or with fv, future value