Excel offers some great tools to work with interest. The Excel RATE function is one such tool. It is a financial function. It calculates the rate of interest for a given period of an investment or loan. In this tutorial, we will learn how to use the function RATE in Excel.
Figure 1. Example of How to Use the Excel Rate Function
Syntax & Arguments
=RATE(nper, pmt, pv, [fv], [type], [guess])
- nper – This argument is required. It is the count of period in total that we need to pay the amount.
- pmt – This argument is also required. It is the amount paid in each period. This value can not be changed during its course.
- pv – It is a required value as well. It represents the total amount of the investment or loan.
- fv – This value is optional. It represents the cash balance we want to achieve after last payment. Default is 0.
- type – This value is also optional. It indicates the time payments are due. 0 represents amounts are scheduled in the end of the period. 1 indicates amounts are due at the start. The default value is 0.
- guess – It is an optional value as well. It is our guess at the rate. The default is 10%.
Setting Up Data
The following example contains some sample values for a loan. Column A has the descriptions of the values. Column B has the years of the loan, the monthly payment and the amount of the loan.
Figure 2. The Sample Data
To calculate the interest rate using the Excel RATE function in cell B5, we need to:
- Go to cell B7. Click on it with the mouse.
- Assign the formula
- Press Enter.
Figure 3. Applying the Formula
This will show the interest rate in cell B7 which is 4%.
- Excel calculates RATE by iterations. If the results of RATE do not converge to 0.0000001 after 20 iterations, it returns a #NUM! error. To prevent this, we may provide an initial ‘guess’ argument to the function.
- Excel returns a #VALUE error if any of the arguments provided for RATE are non-numeric.
- We need to change the number of periods or interest rate to months or quarters. We need to do this before calculating quarterly or monthly payments. Hence, we need to make sure nper has the correct units, i.e., quarters = 4*years, months = 12*years.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.