Documentation/Calc Functions/RATE
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
RATE
Category:
Financial Analysis
Summary:
Calculates the constant interest rate per period of an investment or loan. RATE calculates the fixed interest rate needed, so that the value today (Present Value), followed by a regular payment at the end of each of a defined number of periods, results in a specified value at the end of the term (Future Value). RATE uses an iterative root-finding algorithm to solve the relevant equation, with the option of providing an initial starting value as one of the function's arguments.
RATE assumes an investment or loan with the following general characteristics:
- An initial lump sum.
- A number of regular payments/deposits.
- A final sum.
- A constant interest rate.
Some of these characteristics can be ignored by setting relevant function arguments to zero.
Syntax:
RATE(NPER; PMT; PV[; [FV][; [Type] [; Guess] ] ])
Returns:
Returns a real number, which is the constant interest rate per period for the specified investment or loan. The number returned is a percentage and, by default, is formatted as a percentage.
Arguments:
NPER is a positive real number, or a reference to a cell containing that number, which is the number of payment periods in the term.
PMT is a real number, or a reference to a cell containing that number, which is the constant payment or deposit made in each period.
PV is a real number, or a reference to a cell containing that number, which is the present value of the investment or loan. PV must be in the same currency units as PMT.
FV is a real number, or a reference to a cell containing that number, which is the future value of the cash balance desired at the end of the term. If FV is omitted, the function uses the value 0.0. FV must be in the same currency units as PMT and PV.
Type is a logical value, or a reference to a cell containing a logical value, which determines whether the due date is at the beginning or end of a payment period. If Type is set to 0 or FALSE, or is omitted, then the function assumes the end of the payment period. If Type is set to any other value, then the function assumes the start of the payment period.
Guess is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is an initial guess for the periodic interest rate. This can be entered if you have some idea of the interest rate but, if omitted, 0.1 (10%) is assumed. It is common for interest rates to be quoted annually and, where necessary, care should be taken to convert such values to be compatible with the length of your payment period.
- If any argument is non-numeric, then RATE reports a #VALUE! error.
- If NPER is less than or equal to 0.0, then RATE reports an invalid argument error (Err:502).
- Because of the iterative method used, it is possible for the calculation to fail to converge to a result. In such cases, RATE reports a calculation does not converge error (Err:523). It may be possible to obtain a result by specifying a different value for the Guess argument because a significant error in this initial guess can lead to non-convergence of the algorithm. It may also be worth rechecking the arguments.
Additional details:
- In common with several of Calc’s financial functions, RATE uses a general model to describe both loans and annuities. This model assumes that an initial lump sum is borrowed or invested, a number of regular payments are made or received, and a final sum is paid or received. A constant interest rate is applied and compounded throughout the term of the investment. This model is expressed by the following equations:
- In the case of a zero interest rate:
- [math]\displaystyle{ PV ~+~ PMT \times n~+~FV~=~0 }[/math]
- In the case of a non-zero interest rate:
- [math]\displaystyle{ PV \times (1+r)^{n} ~+~ PMT \times \frac{\left ( \left ( 1+r \right )^{n} -1\right )}{r}\times \left ( 1+ \left (t \times r \right ) \right )~+~FV~=~0 }[/math]
- where:
- FV = future value
- n = number of payment periods
- PMT = periodic payment value
- PV = present value
- r = periodic interest rate
- t = due date flag (0 when the due date is at the end of each payment period; 1 when the due date is at the beginning of each payment period).
- In the case of a zero interest rate:
- RATE assumes the convention that money received is positive, while money paid out is negative.
- RATE uses the Newton–Raphson method for root finding (there is no general theoretical alternative to this iterative approach). For more information about this method, visit Wikipedia's Newton's method page.
Examples:
Note that the formatting of the return values given in the following examples assume the English (USA) locale.
| Formula | Description | Returns |
|---|---|---|
| =RATE(A1; A2; A3; A4) where cells A1 to A4 contain the values 48, 500, -35019.37, and 20000 respectively. | Here the function calculates the constant interest rate per period of an investment that initially costs 35,019.37 currency units but pays the investor 500 currency units every month and an extra sum of 20,000 currency units at the end of the four-year investment period. The periodic payments are made at the end of each month (this is the default setting for the omitted Type argument) and the Guess argument assumes its default value of 10%. Note that payments to the investor have a positive value, while payments by the investor are negative. RATE returns the value 0.67% per period which is equivalent to approximately 8% per year. | 0.67% |
| =RATE(48; 500; −35019.37; 20000; 1) | Here the function calculates the constant interest rate per period of the same investment as the previous example, except that the non-zero value for the Type argument implies that the periodic payments of 500 currency units occur at the beginning of each month (rather than at the end). RATE returns the value 0.68% per period, only marginally different to the result of the previous example, with this variance due to numerical inaccuracies of the two calculations. | 0.68% |
| =RATE(20; 1500; −15891.02) | Here the function calculates the constant interest rate per period of an annuity which originally costs 15,891.02 currency units but pays the investor 1,500 currency units at the end of each year, for the next 20 years. The FV, Type, and Guess arguments are omitted and assume their default values of 0.0, 0, and 10% respectively. RATE returns the value 7.00%, which is the annual interest rate. | 7.00% |
| =RATE(60; −300; 15705.85) | Here the function calculates the constant interest rate per period for a loan of 15,705.85 currency units. The loan is to be repaid at the rate of 300 currency units each month for 5 years. The FV, Type, and Guess arguments are again omitted and assume their default values of 0.0, 0, and 10% respectively. RATE returns the value 0.46% per period which is equivalent to approximately 5.5% per year. | 0.46% |
| =RATE(18*12; −100; −3785.21; 60000; ; 20%) | An investment is required that will provide a cash sum of 60,000 currency units in 18 years time. An initial sum of 3,785.21 currency units is to be invested at the start of the investment and 100 currency units are to be invested at the end of each month. The Guess argument is set to 20%. The Type argument is omitted and assumes its default values of 0. RATE returns the value 0.62% per period which is equivalent to approximately 7.5% per year. Note that the formula =RATE(18*12; -100; -3785.21; 60000) which uses the default value of 10% for the Guess argument returns the same value (when displayed to two decimal places). |
0.62% |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
RATE