Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
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.
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.
You can download this Rate Function in Excel template here – Rate Function Excel Template
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 –