# CFLib.org – Common Function Library Project

## excelRate(nper, pmt, pv[, fv][, type][, guess])

##### Last updated February 23, 2012

Version: 1 | Requires: CF6 | Library: FinancialLib

Description:
A translation of Microsoft Excel's RATE() formula. This function returns the interest rate per period of an annuity.

Return Values:
Returns a number.

Example:

``````<cfset rate = excelRate(72,-309,15743) * 12 />
<cfoutput>#NumberFormat((rate * 100), "0.00")#%</cfoutput>
``````

Parameters:

Name Description Required
nper Total number of payment periods in an annuity. Yes
pmt The payment made each period and cannot change over the life of the annuity. Yes
pv The present value; the total amount that a series of future payments is worth now. Yes
fv The future value, or a cash balance you want to attain after the last payment is made. No
type The number 0 or 1, indicating when payments are due. No
guess The guess for what the rate will be. No

Full UDF Source:

``````/**
* Returns the interest rate per period of an annuity.
*
* @param nper      Total number of payment periods in an annuity. (Required)
* @param pmt      The payment made each period and cannot change over the life of the annuity. (Required)
* @param pv      The present value; the total amount that a series of future payments is worth now. (Required)
* @param fv      The future value, or a cash balance you want to attain after the last payment is made. (Optional)
* @param type      The number 0 or 1, indicating when payments are due. (Optional)
* @param guess      The guess for what the rate will be. (Optional)
* @return Returns a number.
* @author Bret Feddern (bret@bricecheddarn.com)
* @version 1, February 23, 2012
*/
/**
* Returns the interest rate per period of an annuity.
* RATE is calculated by iteration and can have zero or more solutions.
*
* excelRate(nper,pmt,pv,fv,type,guess)
* @param nper  |
* @param pmt  |
* @param pv  |
* @param fv  |    OPTIONAL
* @param type  |    OPTIONAL
* @param guess  |    OPTIONAL
* @return  |  A numeric value.
* @author  |  Bret Feddern (bret@bricecheddarn.com)
* @version 1 - February 19, 2012
*/

function excelRate(nper, pmt, pv) {

var financialPrecision = 1.0e-08;
var maxIterations = 128;
var fv = 0.0;
var type = 0;
var guess = 0.1;
var rate = '';
var f = '';
var i = '';
var y = '';
var y0 = '';
var y1 = '';
var x0 = '';
var x1 = '';

if (ArrayLen(arguments) GT 3) {
fv = arguments;
}

if (ArrayLen(arguments) GT 4) {
type = arguments;
}

if (ArrayLen(arguments) GT 5) {
guess = arguments;
}

rate = guess;

if (abs(rate) lt financialPrecision) {
y = arguments.pv * (1 + arguments.nper * rate) + arguments.pmt * (1 + rate * type) * arguments.nper + fv;
} else {
f = exp(arguments.nper * log(1 + rate));
y = arguments.pv * f + arguments.pmt * (1 / rate + type) * (f - 1) + fv;
}

y0 = arguments.pv + arguments.pmt * arguments.nper + fv;
y1 = arguments.pv * f + arguments.pmt * (1 / rate + type) * (f - 1) + fv;

i = 0.0;
x0 = 0.0;
x1 = rate;

while ((abs(y0 - y1) GT financialPrecision) AND (i LT maxIterations)) {
rate = (y1 * x0 - y0 * x1) / (y1 - y0);
x0 = x1;
x1 = rate;

if (abs(rate) LT financialPrecision) {
y = arguments.pv * (1 + arguments.nper * rate) + arguments.pmt * (1 + rate * type) * arguments.nper + fv;
} else {
f = exp(arguments.nper * log(1 + rate));
y = arguments.pv * f + arguments.pmt * (1 / rate + type) * (f - 1) + fv;
}

y0 = y1;
y1 = y;
i = i++;
}

return(rate);
}
``````

### Search CFLib.org Raymond Camden added
QueryDeleteRows
November 04, 2017 Leigh added
May 11, 2016 Raymond Camden added
stripHTML
May 10, 2016 Kevin Cotton added
date2ExcelDate
May 05, 2016 Raymond Camden added
CapFirst
April 25, 2016

Created by Raymond Camden / Design by Justin Johnson