How to Use RATE Function in Excel (3 Examples)

The RATE function in Excel is a financial function that calculates the interest rate per period of an annuity to pay off a loan or the rate of return needed to reach an amount of investment. There’s an overview:

RATE function in Excel


Download the Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the RATE Function

Objective: To calculate the rate of interest.
Syntax: =RATE(nper, pmt, pv, [fv], [type], [guess])
Arguments: Imagine a scenario that you are going to take a $5000 loan. Now you are supposed to pay a fixed amount of $125 monthly (pmt) to pay the total amount with a fixed rate of interest annually within a certain period of time, say 5 years.
In another case, you have a plan to raise a fund of $25,000 for your children’s education in the next 15 years by paying 1200 dollars on a yearly basis.

Argument Required/Optional Explanation
nper-number of payment periods in an annuity Required 1st Scenario: 5*12=60 (12 months a year) periods in 5 years

2nd Scenario: 15 periods

pmt-the amount of payment made each period and cannot change over the life of the annuity Required 1st Scenario: $125 monthly

2nd Scenario: $1200 yearly

pv- the present value Required 1st Scenario: 5000 dollars

2nd Scenario: 0 dollar

fv– the future value or a cash balance expected after the last payment Optional 0 if omitted

2nd Scenario, the future value is $25,000

type-payment type Optional 0 or omit– payments are due at the end of the period

1-payments are due at the beginning of the period

guess– guess on the rate of interest Optional Default is 10%

3 Suitable Examples of Using the RATE Function in Excel

1. Using the Rate Function in Excel to Calculate the Annuity Interest Rate

In this example, we’ll find out the annuity interest rate for 25,000 dollars of funds raised in 15 years. We configured the cell C12 with the RATE function and hit Enter:

the RATE function in Excel

Explanation:
The formula in cell C12 is  =RATE(C5,-C6,C7,C8,C9,C10)
C5 represents nper    : 15 for 15 years(no of payment per year is 1) 
C6 represents pmt      :1200 dollars cash paid which is negative in the formula
C7 represents pv        : 0
C8 represents fv         : 25000 dollars
C9 represents type     : 0 as payments are due at the end of the period
C10 represents guess: 10% as default

Results:
The formula returns 4.53% as the rate of interest. 

Note:
By default, the result shows as a round number (5%), with no decimal places. We can add decimal places following the steps below:

  • Go to the Home Tab
  • Navigate to the Number panel
  • Choose the Increase Decimal

Here in this example, we added two decimal places.


2. Find the Interest Rate for a Loan Using the Rate Function

Here at first, we are going to calculate the periodic interest rate for a loan. Then by multiplying it with the compounding periods of a year, we’ll get the annual interest rate.

the RATE function in Excel

Formula Breakdown:
In cell C12 formula is =RATE(60,-100,5000,0,0,10%)
Let’s compare it with =RATE(nper, pmt, pv, [fv],[type], [guess])
Here,
nper    : 60=5*12        // 5 years is multiplied by 12(No of payments per year)
pmt     : 100                // 100 dollars cash paid. In annuity functions, cash paid is negative.
pv        : 5000              // total amount loan to be paid within 5 years
fv         : 0
type     : 0                    //payments are due at the end of the period
guess  : 10%               //default value

Results:
All the inputs in C12 return 0.62% as the periodic interest rate which is then multiplied by 12 to get 7.42% as the annual interest rate from cell C13.


Similar Readings


3. The Rate Function in Excel to Solve for the Compound Annual Growth Rate on Investment

The compound annual growth (CAGR) rate can be calculated by using the RATE function in Excel. It calculates the value of an investment over a certain period of time. Say, we need to know the CAGR of an investment of 1000 dollars/year for 10 years when we want to accumulate 5000 dollars at the end.

Formula Breakdown:
In cell C12 formula is =RATE(10,,-1000,5000,0,10%)
Let’s compare it with =RATE(nper, pmt, pv, [fv],[type], [guess])
Here,
nper: 10                      // 10 payments in 10 years
pmt:                            // not used in this example, left blank
pv: -1000                     // total amount loan to be paid within 5 years
fv: 5000
type: 0                         //payments are due at the end of the period
guess: 10%                //default value

Results:
All the inputs in C12 return 17.46% as the compound annual growth rate (CAGR).


Things to Remember

  •  #NUM? Error: The RATE function shows #NUM? error when it fails to calculate the rate. It happens usually when we put the outgoing cash payment as positive instead of negative. Don’t forget to put a negative sign before the pmt value in the formula.
  •  #NAME? Error: It also shows #NAME? error if there is a non-numeric value as an argument in the formula.
  • In some calculations, we need to put an initial Guess value to help the function iteration the result although we can leave it blank.
  • Sometimes the RATE function may return 0% as interest rate if the result is a fraction number under 1. To solve this problem, click here to see the solution.
  • We need to make sure that all the argument values are consistent. For example, to calculate the monthly interest rate, the number of payment periods should also be in months.

Conclusion

Now, we know how to use the RATE function in Excel. Hopefully, it would encourage you to use this function more confidently. Any questions or suggestions don’t forget to put them in the comment box below


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo