Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Excel PMT in PHP and JavaScript
/**
* Copy of Excel's PMT function.
* Credit: http://stackoverflow.com/questions/2094967/excel-pmt-function-in-js
*
* @param rate_per_period The interest rate for the loan.
* @param number_of_payments The total number of payments for the loan in months.
* @param present_value The present value, or the total amount that a series of future payments is worth now;
* Also known as the principal.
* @param future_value The future value, or a cash balance you want to attain after the last payment is made.
* If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
* @param type Optional, defaults to 0. The number 0 (zero) or 1 and indicates when payments are due.
* 0 = At the end of period
* 1 = At the beginning of the period
* @returns {number}
*/
function pmt(rate_per_period, number_of_payments, present_value, future_value, type){
future_value = typeof future_value !== 'undefined' ? future_value : 0;
type = typeof type !== 'undefined' ? type : 0;
if(rate_per_period != 0.0){
// Interest rate exists
var q = Math.pow(1 + rate_per_period, number_of_payments);
return -(rate_per_period * (future_value + (q * present_value))) / ((-1 + q) * (1 + rate_per_period * (type)));
} else if(number_of_payments != 0.0){
// No interest rate, but number of payments exists
return -(future_value + present_value) / number_of_payments;
}
return 0;
}
/**
* Copy of Excel's PMT function.
* Credit: http://thoughts-of-laszlo.blogspot.nl/2012/08/complete-formula-behind-excels-pmt.html
*
* @param double $interest The interest rate for the loan.
* @param int $num_of_payments The total number of payments for the loan in months.
* @param double $PV The present value, or the total amount that a series of future payments is worth now;
* Also known as the principal.
* @param double $FV The future value, or a cash balance you want to attain after the last payment is made.
* If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
* @param int $Type Optional, defaults to 0. The number 0 (zero) or 1 and indicates when payments are due.
* 0 = At the end of period
* 1 = At the beginning of the period
*
* @return float
*/
function PMT($interest,$num_of_payments,$PV,$FV = 0.00, $Type = 0){
$xp=pow((1+$interest),$num_of_payments);
return
($PV* $interest*$xp/($xp-1)+$interest/($xp-1)*$FV)*
($Type==0 ? 1 : 1/($interest+1));
}
@arisn374

This comment has been minimized.

Copy link

@arisn374 arisn374 commented May 7, 2018

Thank you very much.
This's key of my project. ><!

@sslgeorge

This comment has been minimized.

Copy link

@sslgeorge sslgeorge commented May 8, 2018

Works perfectly, well done

@suit0r

This comment has been minimized.

Copy link

@suit0r suit0r commented Aug 22, 2019

thank you. this is it.

@meincms

This comment has been minimized.

Copy link

@meincms meincms commented Apr 15, 2020

@maarten00: Great one! Thanks for your work.
Do you know if there is an issue with the two functions and the round-options?
I do get two slightly different results in several use cases, when comparing the function's result.

Do you have any idea where that issue comes from? Probably a matter of decimal places? I'm talking about like 1 up to 3 $ difference... it's not much, but still, it seems to be a different value.

Thanks for your feedback.

@maarten00

This comment has been minimized.

Copy link
Owner Author

@maarten00 maarten00 commented Apr 15, 2020

@maarten00: Great one! Thanks for your work.
Do you know if there is an issue with the two functions and the round-options?
I do get two slightly different results in several use cases, when comparing the function's result.

Do you have any idea where that issue comes from? Probably a matter of decimal places? I'm talking about like 1 up to 3 $ difference... it's not much, but still, it seems to be a different value.

Thanks for your feedback.

I have experienced some very slight differences in the output when comparing it to the output from Excel. In my case the differnce was negligible because I was only displaying prices rounded to 2 decimals. I don't remember if I looked into the issue or not, but I did add the original links where I found the code. Maybe someone there had the same issue and added an updated version :)

http://stackoverflow.com/questions/2094967/excel-pmt-function-in-js
http://thoughts-of-laszlo.blogspot.nl/2012/08/complete-formula-behind-excels-pmt.html

@chrismuiruriz

This comment has been minimized.

Copy link

@chrismuiruriz chrismuiruriz commented Aug 4, 2020

This just saved me a lot of time. Thanks Man

@laiwf94

This comment has been minimized.

Copy link

@laiwf94 laiwf94 commented Aug 20, 2020

for pmt.js - line 23, you are dividing the number with base using variable type, and type by default is 0 which lead to number/0 and become infinity number. It looks like a different formula between js and php.

@Naicah

This comment has been minimized.

Copy link

@Naicah Naicah commented Jan 30, 2021

Hi!
This function is great, thank you!
I am comparing this to a function in an excel sheet, and it seems like if the fv is not 0, the results differ from excel and this function.
Do you know what causes this?

Example:
Formula Excel
r 0 0
n 10 10
pv 8000 8000
fv 1000 1000
type 0 0

Result -900 700

If fv is 0:
Result -800 800

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment