Rate function in excel is used to calculate the rate levied on a period of a loan it is an inbuilt function in excel, it takes the number of payment periods, pmt, present value and future value as an input, the input provided to this function is in integers and the output is in percentage.
Rate Function Excel (Table of Contents)
RATE Excel Function
RATE function is an advanced build-in function in excel and one of the financial functions used to calculate the interest rate per payment period for an annuity. RATE Function in Excel calculates the payable interest rate to achieve the target amount on investment over a period of time and return a numeric value as output.
RATE Formula in Excel
Explanation of Rate Function in Excel
- Nper: Nper is the total number of period for the loan/investment amount is to be paid. Example considering the 5 years terms means 5*12=60.
- Pmt: The fixed amount paid per period and cannot be changed during the life of annuity.
- Pv: Pv is the total loan amount or present value.
- [Fv]: It is also called the future value and it is optional in RATE in Excel and if not passed in this function then it will be considered as zero.
- [Type]: It can be omitted from RATE in Excel and used as 1 in case if payments are due at the beginning of the period and considered as 0 in case the payments are due at the end of the period.
- [Guess]: It is also an optional parameter if omit it is assumed as 10%.
How to Use RATE Function in Excel?
RATE function in Excel is very simple and easy to use and can be used as worksheet function and VBA function as well. This function used as worksheet function.
The rate in Excel Example #1
Suppose the investment amount is 25,000 and the period is 5 years. Here the number of payments nper will be =5*12=60 payments in total and we have considered 500 as fixed monthly payment and then calculate the interest rate by using the RATE function in Excel.
Output will be: =RATE (C5, C4,-C3)*12 = 7.4%
Rate in Excel Example #2
Let consider one another example wherein the interest rate per payment period on a 5,000 investment amount where monthly payments of 250 are made for 2 years so period will be=12*2=24. Here we have considered that all payments are made at the beginning of the period so type will be 1 here.
Calculation of interest rate are as follows:
=RATE(C13,-C12, C11,, 1) output will be: 1.655%
Rate Function Example #3
Let take another example for weekly payment. This next example returns the interest rate on a loan amount of 8,000 where weekly payments of 700 are made for 4 years and here we have considered that all payments are made at the end of the period so type will be 0 here.
The output will be: =RATE(C20,-C19, C18,, 0) = 8.750%
Rate Excel Example #4
In the final example we have considered that the loan amount will be 8,000 and annual payments of 950 are made for 10 years. Here we considered that all the payments are made at the end of the period then type will be 0 here.
The output will be: =RATE(C28,-C27, C26) =3.253%
RATE in Excel used as VBA function.
Dim Rate_value As Double
Rate_value = Rate(10*1, -950, 800)
And output will be 0.3253
Things to remember about the RATE Function in Excel
Below are the few error details which can comes in RATE in excel as the result if wrong arguments passed in the RATE in Excel.
- Error handling #NUM!: If the interest RATE do not converge to 0.0000001 after 20 iterations, then RATE excel function returns the #NUM! Error value.
- Error handling #VALUE!: RATE excel function through a #VALUE! Error when any non-numeric value has been passed in the Rate formula in Excel argument.
- You need to convert the number of period to month or quarter as per your requirement.
- If the output of this function seems as 0 then there is no decimal value then you need to format the cell value to percentage format.
This has been a guide to Rate Function in Excel. Here we discuss the Rate Formula in excel and how to use Rate function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –