给出的函数simple financial rate function in javascript在某些情况下与Excel中的利率函数计算结果不同。它对于http://allfinancialmatters.com/2009/11/03/how-to-use-the-rate-function-in-excel/中给出的问题完美地工作,但是对于我的测试用例,它的结果与Excel中的利率计算结果不同。这种行为很奇怪,我无法解决这个问题。我的测试用例(包括Excel的输出)如下:
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
我的code.js文件是:
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);
}
我的 HTML 文件是
<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>
var FINANCIAL_MAX_ITERATIONS = 128;//Bet accuracy with 128
是错误的:“如果RATE函数的连续结果在20次迭代后不能收敛到0.0000001以内”http://office.microsoft.com/en-gb/excel-help/rate-function-HA102752889.aspx - SheetJS