## 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[4];
}
if (ArrayLen(arguments) GT 4) {
type = arguments[5];
}
if (ArrayLen(arguments) GT 5) {
guess = arguments[6];
}
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);
}
```

blog comments powered by Disqus
### Search CFLib.org

### Latest Additions

Kevin Cotton added

date2ExcelDate

May 5, 2016

Raymond Camden added

CapFirst

April 25, 2016

Chris Wigginton added

loremIpsum

January 18, 2016

Gary Stanton added

calculateArrival...

November 19, 2015

Sebastiaan Naafs - van Dijk added

getDaysInQuarter

November 13, 2015