How to use the RATE function
What is the RATE function?
The RATE function returns the interest rate per period of an annuity.
Table of Contents
1. Introduction
What is an investment?
An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.
What are periods?
A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.
What is present value?
The present value is the initial amount that will earn interest/dividend.
What is future value?
The compounded amount after the calculated periods based on the given rate. It measures what a current capital (present value) amount will be worth at a designated future date.
What are periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is a constant interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is the number of compounding periods per year?
The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.
Some common compounding periods:
- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year
What is compounding?
Compounding refers to the process of generating more interest from interest that was previously earned. It causes interest to grow exponentially over time.
How is the RATE Function calculated?
Rate = (Fv/Pv)(1/nper)
Fv - future value
Pv - present value
nper - periods
Related functions
| Function | Description |
|---|---|
| RATE(nper, pmt, pv, [fv], [type]) | Returns the interest rate per period of an annuity |
| PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. |
| FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. |
| PDURATION(rate, pv, fv) | Returns the periods needed for an investment to reach a future value. |
2. Syntax
RATE(nper, pmt, pv, [fv], [type], [guess])
| nper | Required. The total number of payment periods in an annuity. |
| pmt | Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument. |
| pv | Required. The present value — the total amount that a series of future payments is worth now. |
| [fv] | Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). If fv is omitted, you must include the pmt argument. |
| [type] | Optional. The number 0 or 1 and indicates when payments are due. |
| [guess] | Optional. Your guess for what the rate will be. |
3. Example 1
What is the annual interest rate for a $250,000 mortgage loan with a term of 30 years and monthly payments of $1,500?
The arguments are:
- nper: 30 (years)
- pmt: -1,500 (monthly payment)
- pv: $250,000
The nper argument is in years and the pmt argument is in months, we must solve this difference in the formula.
Formula in cell C21:
The formula above multiples the payment (pmt) by 12 to get the yearly amount. The formula in cell C21 calculates the annual rate to 5.92% (0.0592) based on an annual payment of $18,000, nper equal to 20, and the pv (present value) argument is $250,000.
The table below the arguments shows the year-by-year calculation of the loan balance using the CUMPRINC function in Excel. It displays the balance for each year from 1 to 30.
Formula in cell D26:
The math formula for solving the rate is: pmt = (rate * pv) / (1 - (1 + rate)^-nper)
However, this is an implicit equation and requires iterative techniques to solve for r given the values of pmt, pv, and n.
4. Example 2
If you want to save $50,000 in 10 years by making monthly payments, what would be the required periodic interest rate if you can afford to pay $400 per month?
The arguments are:
- nper: 10 (years)
- pmt: -400 (monthly payment)
- [fv]: $50,000
The nper argument is in years and the pmt argument is in months, we must solve this difference in the formula.
Formula in cell C21:
The formula above multiples the number of years (nper) by 12 to get monthly intervals. The formula in cell C21 calculates the annual rate to 0.07% (0.0007) based on an monthly payment of $400, nper equal to 120, and the [fv] (future value) argument is $50,000.
By saving $400 per month for 120 months (10 years), you accumulate $48,000 through your principal deposits alone. To reach the desired future value of $50,000, you only need an additional $2,000 in interest earnings over that 10-year period. This relatively small interest amount of $2,000 equates to a monthly interest rate of just 0.07%, or an annual interest rate of 0.82%.
The low interest rate needed is because the bulk of the $50,000 target ($48,000) comes directly from your monthly savings deposits rather than interest earnings. However, if you wanted to accumulate $50,000 faster while contributing less from principal deposits, you would need a higher interest rate to have the interest earnings make up a larger portion of the future value goal.
The table below the arguments shows the month-by-month calculation of the interest earned and the savings balance. It displays the balance for each month from 1 to 120.
Cell D26 contains a 0 (zero). The formula in cell D27:
Copy cell D27 to cells below as far as needed.
Formula in cell E26:
This formula calculate the accumulated saving based on payments and interest earned.
5. Why is the function not working?
The RATE function uses an iterative process (repeated calculations) to find the interest rate result, this means it may have more than one possible solution for the rate.
The RATE function will display the #NUM! error value if after trying 20 times at narrowing down the rate via repeated guesses and the results are still not consistent within 0.0000001
Functions in 'Financial' category
The RATE function function is one of 27 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form