How to Use the RATE function to calculate interest rate in Excel
Let’s say a bank advertises that if you deposit $500 each month for 12 years, you will have $100,000 at the end of the period. What is the interest rate the bank is paying? To answer this question, use the RATE function, which returns the interest rate per period of an annuity. Here is the syntax that is used:
RATE(nper, pmt, pv, fv, type, guess)
nper: The total number of payment periods in an annuity.
pmt: The payment made each period, which is a constant value.
pv: The present value. This is the amount that a series of future payments is worth right now.
fv: The future value. This is the amount you want after the last payment is made.
type: A number that indicates when payments are due. 0 or omitted indicates the end of the period, and 1 indicates the beginning of the period.
guess: A guess for what the interest rate will be. If omitted, Excel uses 10%.
To calculate the interest rate:
- In cell B1 enter the number of periods in years.
- In cell B2 enter the monthly amount to deposit.
- In cell B3 enter the final value the bank has advertised.
- In cell B5 type the following formula: =RATE(B1*12,-B2,0,B3,0)*12.
- Press Enter.
- How to use the WORKDAY function to calculate workdays in Excel
- How to Use the SYD function to calculate depreciation in Excel
- How to Use the QUARTILE function to calculate the quartiles
- How to Use the YEARFRAC function to calculate employees ages
- How to Use the DATEDIF function to calculate the ages of employees
