How to use Rate Function in Excel

In this lesson you will learn how to use RATE function in Excel.

The RATE function in Excel calculates the interest rate per period that would be required to achieve a future value given the present value, number of periods, and periodic payments. It is a very versatile function that can be used in a variety of ways.

Rate function Parameters and Arguments

The syntax of the rate function is – RATE(nper;pmt;pv;fv;type;guess) – and they are working together to return with the interest rate.

  • Nper: This is crucial to the function, and would be the total number of the payment periods in the annuity.
  • Pmt: This is required part of the RATE function. It is the payment that is made each period, and cannot be changed throughout the life of the annuity.
  • PV: Present value is required, and is the total amount that the future payments are currently worth
  • FV: This is the future value, and is optional. It is the cash balance that the user wishes to attain when the final payment has been made. If it is omitted, then the rate would assume it to be 0.
  • Type: This is optional, and can only be either 0 or 1. It is an indication of when the payments are due.

How does the RATE function work?

The RATE function works by using a trial and error method to find the interest rate that would be required to achieve the desired future value. It starts with a guess value and then iteratively increases or decreases the interest rate until the future value is achieved.

See also  Double dash in Sumproduct formula

The optional argument can be used to specify whether the interest is compounded annually or not. If the argument is FALSE, the interest is compounded annually. If the argument is TRUE, the interest is compounded monthly.

Rate function examples

Interest rate of loan

The company offers installment sale. Installments are spread over 50 installments, the monthly repayment amount is $15000. Price is $1000000. What interest rate offers seller, per month and per year?

Syntax: =RATE(50,-15000,1000000,0,0)

As you see you should type PMT with minus sign. You pay money off so it cause that you spend that money.

Answer: The result is the monthly interest rate: 1.07%. Yearly interest rate is 12*1.07% = 12.90%.

Interest rate for weekly installments

What is interest rate for $10 000 loan. Weekly $600 payments are made for 7 years. You have to pay at the beginning of week.

Syntax: =RATE(7*52,-600,10000,,1)

Answer: The result is the weekly interest rate: 6.38%

Tips for using the RATE function in Excel

  • The RATE function is a volatile function, which means that it recalculates its value whenever the workbook is recalculated. This can be useful if you want to keep the interest rate up to date, but it can also be a problem if you are using the RATE function in a formula that should not be recalculated every time the workbook is opened.
  • The RATE function uses a trial and error method to find the interest rate that would be required to achieve the desired future value. This means that it may take some time for the function to calculate the interest rate, especially if the number of periods is large.
  • The RATE function can be used to calculate the interest rate for both simple and compound interest. If you are using compound interest, you need to specify the number of times per year that the interest is compounded.
  • The RATE function can be used to calculate the interest rate for any type of payment, including monthly payments, quarterly payments, and annual payments. You need to specify the amount of each payment in the periodic_payment argument.