Al Arafat Siddique
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:
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:
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.
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
- How to Use Excel PRICE Function (3 Appropriate Examples)
- Use IPMT Function in Excel (8 Examples)
- How to Use EFFECT Function in Excel (2 Examples)
- Use FV Function in Excel (4 Easy Examples)
- How to Use Excel PPMT Function (3 Suitable Examples)
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.
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
