the function given at simple financial rate function in javascript is not giving me same answers as excel rate function some time. It works perfectly for the problem given at http://allfinancialmatters.com/2009/11/03/how-to-use-the-rate-function-in-excel/ but for my test cases. its results are different from excel rate. this is strange behaviour. i am unable to sort this out. my test cases (with excel output) are
RATE(360,-665.3, 99000) = 0.0059 RATE(360,-958.63, 192000) =0.0036 RATE(180,-1302.96,192000) = 0.0023 RATE(360, -889.19, 192000) =0.00312 RATE(360, -1145.8, 240000) = 0.0033
my code.js is
function rate(paymentsPerYear, paymentAmount, presentValue, futureValue, dueEndOrBeginning, interest)
{
//If interest, futureValue, dueEndorBeginning was not set, set now
//if (interest == null) // not working here :D
if (isNaN(interest))
interest = 0.1;
//interest = 0.1;
if (isNaN(futureValue))
futureValue = 0;
if (isNaN(dueEndOrBeginning))
dueEndOrBeginning = 0;
var FINANCIAL_MAX_ITERATIONS = 128;//Bet accuracy with 128
var FINANCIAL_PRECISION = 0.0000001;//1.0e-8
var y, y0, y1, x0, x1 = 0, f = 0, i = 0;
var rate = interest; // initiallizing rate to our guess interest
if (Math.abs(rate) < FINANCIAL_PRECISION)
{
y = presentValue * (1 + paymentsPerYear * rate) + paymentAmount * (1 + rate * dueEndOrBeginning) * paymentsPerYear + futureValue;
}
else
{
f = Math.exp(paymentsPerYear * Math.log(1 + rate));
y = presentValue * f + paymentAmount * (1 / rate + dueEndOrBeginning) * (f - 1) + futureValue;
}
y0 = presentValue + paymentAmount * paymentsPerYear + futureValue;
y1 = presentValue * f + paymentAmount * (1 / rate + dueEndOrBeginning) * (f - 1) + futureValue;
// find root by Newton secant method
i = x0 = 0.0;
x1 = rate;
while ((Math.abs(y0 - y1) > FINANCIAL_PRECISION)
&& (i < FINANCIAL_MAX_ITERATIONS))
{
rate = (y1 * x0 - y0 * x1) / (y1 - y0);
x0 = x1;
x1 = rate;
if (Math.abs(rate) < FINANCIAL_PRECISION)
{
y = presentValue * (1 + paymentsPerYear * rate) + paymentAmount * (1 + rate * dueEndOrBeginning) * paymentsPerYear + futureValue;
}
else
{
f = Math.exp(paymentsPerYear * Math.log(1 + rate));
y = presentValue * f + paymentAmount * (1 / rate + dueEndOrBeginning) * (f - 1) + futureValue;
}
y0 = y1;
y1 = y;
++i;
}
return rate;
//return String(parseFloat(rate).toFixed(3)); // rounding it to 3 decimal places
//return parseFloat(rate).toFixed(3);
}
and my HTML file is
<head><title>JavaScript Loan Calculator</title>
<script src="code.js"></script>
</head>
<body bgcolor="white">
<form name="loandata">
<table>
<tr>
<td>1)</td>
<td>paymentsPerYear:</td>
<td><input type="text" name="paymentsPerYear" size="12"
onchange="calculate();"></td>
</tr>
<tr>
<td>2)</td>
<td>paymentAmount:</td>
<td><input type="text" name="paymentAmount" size="12"
onchange="calculate();"></td>
</tr>
<tr>
<td>3)</td>
<td>presentValue:</td>
<td><input type="text" name="presentValue" size="12"
onchange="calculate();"></td>
</tr>
<tr>
<td>4)</td>
<td>futureValue:</td>
<td><input type="text" name="futureValue" size="12"></td>
</tr>
<tr>
<td>5)</td>
<td>dueEndOrBeginning:</td>
<td><input type="text" name="dueEndOrBeginning" size="12"></td>
</tr>
<tr>
<td>6)</td>
<td>interest:</td>
<td><input type="text" name="interest" size="12"></td>
</tr>
<tr><td colspan="3">
<input type="button" value="Compute" onClick="calculate();">
</td></tr>
<tr>
<td>APR:</td>
<td><input type="text" name="APR" id="APR" size="12"></td>
</tr>
</table>
</form>
<script language="JavaScript">
function calculate() {
var paymentsPerYear = document.loandata.paymentsPerYear.value;
var paymentAmount = document.loandata.paymentAmount.value;
var presentValue = document.loandata.presentValue.value;
var futureValue = document.loandata.futureValue.value;
var dueEndOrBeginning = document.loandata.dueEndOrBeginning.value ;
var interest = document.loandata.interest.value ;
var ans = rate(parseFloat(paymentsPerYear), parseFloat(paymentAmount), parseFloat(presentValue), parseFloat(futureValue), parseFloat(dueEndOrBeginning), parseFloat(interest));
document.loandata.APR.value=ans;
//alert(futureValue);
}
</script>
</body>
</html>
Advertisement
Answer
In case anyone is still looking for a javascript implementation of the Excel’s Rate function, this is what I came up with:
var rate = function(nper, pmt, pv, fv, type, guess) {
// Sets default values for missing parameters
fv = typeof fv !== 'undefined' ? fv : 0;
type = typeof type !== 'undefined' ? type : 0;
guess = typeof guess !== 'undefined' ? guess : 0.1;
// Sets the limits for possible guesses to any
// number between 0% and 100%
var lowLimit = 0;
var highLimit = 1;
// Defines a tolerance of up to +/- 0.00005% of pmt, to accept
// the solution as valid.
var tolerance = Math.abs(0.00000005 * pmt);
// Tries at most 40 times to find a solution within the tolerance.
for (var i = 0; i < 40; i++) {
// Resets the balance to the original pv.
var balance = pv;
// Calculates the balance at the end of the loan, based
// on loan conditions.
for (var j = 0; j < nper; j++ ) {
if (type == 0) {
// Interests applied before payment
balance = balance * (1 + guess) + pmt;
} else {
// Payments applied before insterests
balance = (balance + pmt) * (1 + guess);
}
}
// Returns the guess if balance is within tolerance. If not, adjusts
// the limits and starts with a new guess.
if (Math.abs(balance + fv) < tolerance) {
return guess;
} else if (balance + fv > 0) {
// Sets a new highLimit knowing that
// the current guess was too big.
highLimit = guess;
} else {
// Sets a new lowLimit knowing that
// the current guess was too small.
lowLimit = guess;
}
// Calculates the new guess.
guess = (highLimit + lowLimit) / 2;
}
// Returns null if no acceptable result was found after 40 tries.
return null;
};
Testing the function with Abdul’s test cases give the following results:
rate(360,-665.3, 99000); 0.005916521358085446 rate(360,-958.63, 192000); 0.0036458502960158515 rate(180,-1302.96,192000); 0.0022917255526408564 rate(360, -889.19, 192000); 0.0031250616819306744 rate(360, -1145.8, 240000); 0.003333353153720964