## 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);
}
```

### Search CFLib.org

### Latest Additions

Raymond Camden added

QueryDeleteRows

November 04, 2017

Leigh added

nullPad

May 11, 2016

Raymond Camden added

stripHTML

May 10, 2016

Kevin Cotton added

date2ExcelDate

May 05, 2016

Raymond Camden added

CapFirst

April 25, 2016