RATE
The interest rate per period of an annuity, solved from a guess.
Group: Financial. Compatible with DAX.
Syntax
RATE ( <nper>, <pmt>, <pv> [, <fv>] [, <type>] [, <guess>] )| Parameter | Type | Description |
|---|---|---|
nper | Number | How many payment periods there are, all told. |
pmt | Number | The amount paid every period, the same each time. |
pv | Number | The amount at the start: what was borrowed, or what was put in. |
fv | Number optional | The balance wanted once the last payment is made; 0 when left out. |
type | Number optional | When the payments fall: 0 (when left out) at the end of each period, 1 at its start. |
guess | Number optional | Where the search for the rate starts; 0.1 when left out. |
Returns
Number. The rate for one period, as a decimal (0.004 for 0.4 %).
Remarks
- There is no formula for the rate, so it is found by trial from the guess. When no rate fits, the result is an error; a better guess can help.
- Cash going out is negative, cash coming in positive. A loan you take is a positive pv, so the payments on it come out negative; a deposit you make is negative. Among pv, pmt and fv some must be positive and some negative, or no rate exists.
- The result is per period: multiply a monthly rate by 12 for the yearly nominal rate.
Examples
The rate hidden in a repayment plan
2,000,000 repaid 21,000 a month for 10 years.
EVALUATE
ROW (
"Monthly", RATE ( 120, -21000, 2000000 ),
"Yearly", RATE ( 120, -21000, 2000000 ) * 12
)| Monthly | Yearly |
|---|---|
| 0.00398446009068217 | 0.047813521088186 |