Rate formula in excel is an inbuilt function which is used to calculate the rate of interest for a given loan, in excel the formula calculates the rate as follows, rate equals to interest multiplied to number of payments which is divided to loan principal, we need to make data series for the respective data to calculate the rate in excel.
Rate Formula in Excel (Table of Contents)
Excel RATE Formula
The Formula of RATE function in Excel.
The Syntax consists of totally 6 parameters.
- Nper: Number of payment or period over a period of the loan or an investment.
- PMT: What is the payment per period to clear the loan or make an investment?
- PV: What is the present value an investment?
- FV: What is the future value of investment i.e. expected amount from the investment.
- Type: What is the type of investment, whether it is at the beginning of the period or at the end of the period?
If it is beginning 1 is the argument, if it is at the end of the period 0 zero is the argument.
What Does RATE Formula do in Excel?
Assume you are making an investment of USD 5000 for 5 years and you are expecting 1000 in return. Yeah very good you are expecting 100% growth on your investment. Do you know at what should be the rate of interest to get that USD 1000 in return?
I know you don’t have the rate of interest to compound your investment value. Nothing to worry about it, because in excel we have a function called RATE which will return the required rate of interest to get the desired amount in a specific period of time.
It will calculate the required rate of return to get the estimated amount. Rate function in excel calculates based on investment period, total investment, and what is the future value you are expecting form the investment.
It is also very helpful in calculating the compound annual growth rate of an investment based on the return given by the investment.
How to Use RATE Formula in Excel? (with examples)
Following are the examples of Rate formula in Excel.
Rate Formula Excel Example #1
Assume you are making an investment of USD 7500 for 2 years. From the investment, you are expecting 12500 in return.
Step 1: Open RATE function in excel. Now you need to at what rate your investment need to return to get the amount of USD 12500 against the investment of USD 7500 for 3 years.
Step 2: For NPER argument select the period of investment i.e. B3 cell.
Step 3: We are not making any periodic payment so leave this argument blank or enter zero.
Step 4: Present value of the investment is 7500, so select B2 cell. Since it is an outflow payment supply as a negative value.
Step 5: Future value is nothing but our required amount i.e. 12500 (B4 cell). Since this is an inflow amount no need of mentioning with a negative value.
Step 6: Close the bracket and hit enter to have a rate of return.
Step 7: If the result showing decimal value or zero, apply the percentage format to get an accurate result.
So, in order to get an amount of USD 12500 for USD 7500 for 3 years rate of interest or return should be 18.56%.
Rate Formula Excel Example #2
Assume one of your friends came to you and says he has invested Rs. 1.5 Lakh 10 years ago and now he got Rs. 5 Lakhs in return. He doesn’t know anything about the investment return percentage he got, he asks you to tell him that at what rate he got the investment return back.
Enter these details in the excel sheet first.
Step 1: Select the first argument as no. of years i.e. E3 cell.
Step 2: Since it is the one-time investment enter zero for PMT argument.
Step 3: Present Value of the investment is 1.5 Lakh i.e. E2 cell. It is an investment, so outflow of money so enter in a negative value.
Step 4: Future Value is nothing but how much return he got for his investment of 1.5 Lakh.
Step 5: Close the bracket and hit enter to have a rate of return.
So, your friend got a return on investment at 12.79% per anum for 10 years. Please note this is a compound interest rate.
We can also call this rate as (12.79%) “Compound Annual Growth Rate”.
Rate Formula Excel Example #3
Assume you had taken a loan of 2.5 lakh for 2 years and you are paying 15000 per month as the EMI amount. You are not aware of the interest rate you are paying right now. Let’s calculate the interest rate by using the Excel RATE function.
Step 1: Open RATE function in C8 cell.
Step 2: NPER is your total number payments i.e. C5 cell.
Step 3: Payment id monthly payment so select C6 cell. It is an outflow amount so mention in negative.
Step 4: Present Values is loan value i.e. C2 cell.
Step 5: Since this is a loan no question of future value, leave blank.
Step 6: Type zero i.e. end of the periodic payment.
Close the bracket and hit the enter key to have a rate of return.
Things to Remember
- PMT argument is applicable only if the payments are made regularly.
- Excel RATE function takes constant payment and loan or investment amount.
- All the values should be numeric if the non-numeric value supplied we will get #VALUE!.
- If the rate result is not in percentage then change the cell format to a percentage.
This has been a guide to Rate Formula in Excel. Here we discuss how to use Rate formula in Excel with some examples and downloadable excel template. You may learn more about excel from the following articles –