RATE( ) function
Returns the interest rate per period.
Syntax
RATE(periods, payment, amount)
Parameters
| Name | Type | Description |
|---|---|---|
| periods | numeric |
The total number of payment periods. |
| payment | numeric |
The payment per period. |
| amount | numeric |
The principal amount of the loan. |
Note
The RATE( ) function assumes that payments are made at the end of each period.
Output
Numeric. The rate is calculated to eight decimals places.
Examples
Basic examples
Returns 0.00541667 (0.54%), the monthly interest rate implied by a twenty-five year, $275,000 loan with monthly payments of $1,856.82:
RATE(12*25, 1856.82, 275000)
Returns 0.06500004 (6.5%), the annual interest rate implied by the same loan:
RATE(12*25, 1856.82, 275000)*12
Advanced examples
Converting the nominal rate to the effective rate
The RATE( ) function calculates the nominal interest rate. You can use the EFFECTIVE( ) function to convert the result of RATE( ) to the effective interest rate.
Returns 0.06715155 (6.7%), the effective annual interest rate implied by the loan in the examples above:
EFFECTIVE((RATE(12*25, 1856.82, 275000)*12), 12*25)
Annuity calculations
Annuity calculations involve four variables:
- present value, or future value $21,243.39 and $ 26,973.46 in the examples below
- payment amount per period $1,000.00 in the examples below
- interest rate per period 1% per month in the examples below
- number of periods 24 months in the examples below
If you know the value of three of the variables, you can use an Analytics function to calculate the fourth.
| I want to find: | Analytics function to use: |
|---|---|
| Present value |
PVANNUITY( ) Returns 21243.39: PVANNUITY(0.01, 24, 1000) |
| Future value |
FVANNUITY( ) Returns 26973.46: FVANNUITY(0.01, 24, 1000) |
| Payment amount per period |
PMT( ) Returns 1000: PMT(0.01, 24, 21243.39) |
| Interest rate per period |
RATE( ) Returns 0.00999999 (1%): RATE(24, 1000, 21243.39) |
| Number of periods |
NPER( ) Returns 24.00: NPER(0.01, 1000, 21243.39) |
Annuity formulas
The formula for calculating the present value of an ordinary annuity (payment at the end of a period):
The formula for calculating the future value of an ordinary annuity (payment at the end of a period):
ACL Scripting Guide 14.1