Category: Finance functions
Overview
Description | 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 when the number of periods and payments are known and you want to solve for the implied interest rate. |
|---|
Syntax | RATE('Nper', 'Pmt', 'Pv' [, 'Fv' [, "Type" [, Guess]]])
|
|---|
Parameters | Nper: Number of periods: the number of payments to be made. Pmt: Payment per period: the amount per period to be paid. Payments/costs should be entered as a negative number, for example -500 Pv: Present value: the current value of the annuity. Fv (optional): Future value: the future value remaining after the final payment has been made. If not entered, 'Fv' is set to 0. Type (optional): When the payments are due (1 = payment at beginning of period / annuity due, 0 = payment at end of period / ordinary annuity). By default, "Type" is set to 0. Guess (optional): A first guess for what the rate will be. By default, Guess is set to 10%.
|
|---|
Notes | Argument 'Pv' is the leading input node. A leading input node is a function argument for which we assume the levels to be correct. All other input nodes need to have the same dimensionality. Each input node can be a single number. Providing all inputs with the same dimensionality results in a noticeable performance improvement. Guess and result values are entered and returned as decimal numbers, for example 10% = 0.1. Guess - the function calculates the rate via trial and error. To find a result, it has 20 tries. Therefore, providing a guess helps to find a result faster. If no result is found within 20 tries, an error is returned.
|
|---|
Limitations | If the successive results for the rate do not converge, Valsight returns an error. All other inputs must not contain levels that are not in the leading input node 'Pv'. All level values that are in the leading input node 'Pv' must be in all the other input nodes.
|
|---|
Examples
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.
Nper (Number of Periods) | 60 |
Pmt (Payment per Period - Amount) | -100 |
Pv (Present Value) | 5000 |
Fv (Future Value) | 0 |
Type Payment 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)
Function | When to use instead |
|---|
NPER | When the rate is known and you want to calculate how many periods are needed instead. |
|---|
IRR | When you need the break-even discount rate for a cash flow series instead of the implied rate of an annuity-style setup. |
|---|
