How to calculate excel pmt using php
There have been attempts to implement the pmt function used in excel in php. I have a formula, but the calculations are not displayed correctly.
Its 6% interest rate over a period of 30 years, the final value is 833333.
The correct answer should be 10 541.
payments must be at the end of the period, so the type is zero and the current 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>
+3
source to share
3 answers
The formula I am using in PHPExcel to reflect the MS Excel 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 - future value
- $ pv - present value
- $ type type
Which returns the same result as MS Excel when I use
=PMT(6%/12, 360, 0, 833333, 0)
And it returns the result -10540.755358736 (same as MS Excel) when I use
=PMT(0.06,30,0,833333,0)
+7
source to share
Cleaner solutions are as follows.
* @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 the exact PMT just like in MS Excel.
Source: https://drastikbydesign.com/blog-entry/excel-pmt-php-cleaner
+4
source to share