How to Use the RATE Formula in Excel

The RATE formula in Excel is used to calculate the interest rate per period of an annuity.

The Rate formula can also come in handy when you are trying to really identify that interest rate or rate of return when you have several variables available within a loaner investment but not the interest rate.

Formula:

Formula Explanation:

• Nper (required): The total number of payment periods in an annuity.
• Pmt (required): The payment made each period and cannot change over the life of the annuity. This includes principal and interest but no other fees or taxes.
• Pv (required): The present value is the total amount that a series of future payments is worth today.
• Fv (optional): The future value is the value of the annuity after the last payment is made. If FV is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
• Type (optional): The number 0 (zero) or 1 and indicates when payments are due. 0 (zero) for when “at the end of the period” and 1 (one) for “the beginning of the period”

Example #1:

A company is considering taking a loan to finance working capital. Three options are available to them as noted in the below table.

However, the loan amount, payments per month, and a number of payments vary between the loans. The company would like to make a decision with all the facts and wants to know what the interest rate is, both monthly and annually, under each loan scenario. Payments are made at the end of the period.

Solution:

In the scenario above, RATE formula was used to calculate the interest rate of the loan using the loan amount, payments per month and payments per year inputs.

Example #2:

The CFO of a tech company regularly has monthly profits of $20,000 available to invest. The CFO wants to save 500,000 over an 18 month period.

What rate of interest is required for an investment to hit this goal if profits are invested at the end of each month?

Solution:

• These are just a couple of examples in which the rate formula can be used and applied within Excel. We have gone over an example with a loan scenario and also gone through an example with an investment scenario.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!