RATE
RATE('Nper', 'Pmt', 'Pv' [, 'Fv' [, "Type" [, Guess]]])
The RATE function returns the interest rate per period of an annuity based on a number of periods, the amount of payments per period, a present value, a future value, a value indicating whether payments are due at the beginning or the end of the period (type) and a guess.
Use this function when the number of periods and payments are known and you want to solve for the implied interest rate.
Parameters
- NperNode referenceRequired
- Number of periods: the number of payments to be made.
- PmtNode referenceRequired
- Payment per period: the amount of each payment made. Payments/costs should ideally be entered as a negative number, for example
-500, but this is not required. - PvNode referenceRequired
- Present value: the current value of the annuity.
- FvNode referenceOptional
- Future value remaining after the final payment has been made. Default:
0. - TypeKeywordOptional
- Indicates when payments are due (
1= payment at beginning of period / annuity due,0= payment at end of period / ordinary annuity). Default:0. - GuessNumberOptional
- A first guess for what the rate will be. Default:
10%.
Watch out
'Pv'is the leading input node: its levels define the result’s shape, and every other input is aligned to it.- Every other input may use only levels that also exist on
'Pv'. It may use fewer levels, but not a level that'Pv'does not have. - On the levels it shares with
'Pv', each input must cover the same level values, otherwise the rows do not line up and the function returns an error. - Each input can also be a single number.
- Giving every input the same levels as
'Pv'skips an internal alignment step and improves performance. Guessand the result are entered and returned as decimals, for example10% = 0.1.- The rate is solved iteratively (up to 150 iterations). If it cannot converge, the function returns an error; a
Guesscloser to the expected rate helps it converge.
Example
Solve for the implied interest rate
This example shows a standard annuity setup with a fixed number of periods, a recurring payment, and a present value. It returns the implied interest rate per period for the setup.
| Parameter | Value |
|---|---|
Nper (Number of Periods) | 60 |
Pmt (Payment per Period - Amount) | -100 |
Pv (Present Value) | 5000 |
Fv (Future Value) | 0 |
TypePayment at Beginning of Period (Annuity Due) = 1 Payment at End of Period (Ordinary Annuity) = 0 | 0 |
| Guess | 0.1 |
Formula: RATE('Nper', 'Pmt', 'Pv', 'Fv', "Type", Guess)
| RATE Result |
|---|
| 0.0062 |