Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to calculate excel pmt using php

Tags:

html

php

excel

Have been trying to implement the pmt function used in excel into php. I have the formula but the calculations are showing incorrect.

Its 6% interest rate for period of 30 years, final value being 833333.

The right answer should be 10,541.

payments are due at the end of the period so the type is zero and present value is zero.

<pre>
    $pv = 0;
    $fv = 833333;
    $i = 0.06/12;
    $n = 360;

    $pmt = (($pv - $fv) * $i )/ (1 - pow((1 + $i), (-$n))); 

    echo $pmt;
</pre>

Using this link as reference for formula

like image 250
prawwe316 Avatar asked Jun 27 '15 11:06

prawwe316


People also ask

How PMT formula is calculated?

The PMT function calculates loan payments. Since most loan payments are monthly, the function needs to be modified by dividing the interest rate by 12, but multiplying the number of payment periods by 12.


3 Answers

The formula that I use in PHPExcel to reflect MS Excel's formula is:

$PMT = (-$fv - $pv * pow(1 + $rate, $nper)) /
    (1 + $rate * $type) /
    ((pow(1 + $rate, $nper) - 1) / $rate);

where

  • $rate = interest rate
  • $nper = number of periods
  • $fv is future value
  • $pv is present value
  • $type is type

Which returns the same result as MS Excel when I use

=PMT(6%/12, 360, 0, 833333, 0)

And which returns a result of -10540.755358736 (the same as MS Excel) when I use

=PMT(0.06,30,0,833333,0)
like image 140
Mark Baker Avatar answered Oct 04 '22 13:10

Mark Baker


A cleaner solutions is as below.

* @param float $apr   Interest rate.
* @param integer $term  Loan length in years. 
* @param float $loan   The loan amount.

function calPMT($apr, $term, $loan)
{
  $term = $term * 12;
  $apr = $apr / 1200;
  $amount = $apr * -$loan * pow((1 + $apr), $term) / (1 - pow((1 + $apr), $term));
  return round($amount);
}

function loanEMI()
{
 echo $this->calPMT(16, 1, 1020000);
}

This will give you exact PMT as in MS Excel.

Source : https://drastikbydesign.com/blog-entry/excel-pmt-php-cleaner

like image 22
Anon30 Avatar answered Oct 04 '22 15:10

Anon30


Heres another one which I came across. Might be of use to someone in future.

$pv = 0;
$fv = 833333;
$i = 0.06;
$n = 30;
$pmt = ((($pv *( pow((1+$i), $n)  )) + $fv) * $i ) / (1 - ( pow((1+$i), $n) ));
echo $pmt;
like image 32
prawwe316 Avatar answered Oct 04 '22 15:10

prawwe316