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

to B7.**=RATE(B3*12,B4,-B2)** - Press
**Enter**.

*Figure 3. Applying the Formula*

This will show the interest rate in cell B7 which is **4%**.

**Notes**

- 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.

## Leave a Comment