?
Solved

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

Posted on 2006-03-23
7
Medium Priority
?
578 Views
Last Modified: 2011-09-20
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
Comment
Question by:SurreyWebDesigner
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 11

Expert Comment

by:siliconbrit
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

by:SurreyWebDesigner
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.

Thanks for your help.
SWD
0
 
LVL 7

Accepted Solution

by:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Expert Comment

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

Author Comment

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

Cheers
SWD
0
 
LVL 1

Expert Comment

by:mugsinc
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

by:mugsinc
ID: 34487657
or with fv, future value
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question