Rate Function in Excel

RATE Excel Function

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 formula is in integers and the output is in percentage.

RATE Formula in Excel

Rate Formula


Compulsory Parameter:

  • Nper: Nper is the total number of the period for the loan/investment amount is to be paid. For 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 the annuity.
  • Pv: Pv is the total loan amount or present value.

Optional Parameter:

  • [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 omitted, it is assumed as 10%.

How to Use RATE Function in Excel?

The RATE function in Excel is very simple and easy to use and can be used as a worksheet function and VBA function as well. This function is used as a worksheet function.

You can download this Rate Function Excel Template here – Rate Function Excel Template

Example #1

Suppose the investment amount is 25,000, and the period is 5 years. Here the number of payments nperNperNPER, commonly known as the number of payment periods for a loan, is a financial term and an inbuilt financial function in Excel that can be used to calculate NPER for any loan. This formula takes rate, payment made, present value and future value as input from a user.read more 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.

RATE Function Example 1

Output will be:  =RATE (C5, C4,-C3)*12 = 7.4%

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 Function Example 2

=RATE(C13,-C12, C11,, 1) output will be: 1.655%

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.

RATE Function Example 3

The output will be: =RATE(C20,-C19, C18,, 0) = 8.750%

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 the type will be 0 here.

RATE Function Example 4

The output will be: =RATE(C28,-C27, C26) =3.253%

RATE in Excel is used as a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more.

Dim Rate_value As Double

Rate_value = Rate(10*1, -950, 800)

Msgbox Rate_value

And output will be 0.3253

Things to Remember

Below are the few error details that can come in RATE in excel as the result if wrong arguments passed in the RATE in Excel.

  1. Error handling #NUM!: If the interest RATE does not converge to 0.0000001 after 20 iterations, then the RATE excel function returns the #NUM! Error value.
RATE Function Example 5
  1. 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.
RATE Function Example 6
  1. You need to convert the number of period to month or quarter as per your requirement.
  2. If the output of this function seems like 0, then there is no decimal value, then you need to format the cell value to percentage format.

Recommended Articles

This has been a guide to Rate Function in Excel. Here we discuss the Rate Formula in excel and how to use the Rate function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion