Created
March 19, 2015 09:56
-
-
Save maarten00/23400873d51bf2ec4eeb to your computer and use it in GitHub Desktop.
Excel PMT in PHP and JavaScript
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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)); | |
} |
This just saved me a lot of time. Thanks Man
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.
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
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