how to calculate excel pmt using php
Solution 1
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)
Solution 2
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
Solution 3
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;
prawwe316
Updated on June 16, 2022Comments
-
prawwe316 almost 2 years
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>
-
Staysee almost 7 yearsBeen hacking at this for a while. How would I incorporate a variable frequency like Weekly, and Biweekly?