• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

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)
0
SurreyWebDesigner
Asked:
SurreyWebDesigner
  • 2
  • 2
  • 2
  • +1
1 Solution
 
siliconbritCommented:

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
 
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.
SWD
0
 
aib_42Commented:
Try:

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

echo PMT((6.5/100)/12, 25*12, 85000);
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

Cheers
SWD
0
 
mugsincCommented:
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
 
mugsincCommented:
or with fv, future value
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now