by Svetlana Cheusheva, updated on
This tutorial explains how to calculate interest rate on recurring deposit in Excel by using the RATE function.
Financial decisions are an important element of business strategy and planning. In everyday life, we also have quite a lot of financial decisions to make. For instance, you are going to apply for a loan to buy a new car. It will surely be helpful to know exactly what interest rate you will have to pay to your bank. For such scenarios, Excel provides the RATE function that is specially designed for calculating interest rate for a specific period.
RATE is an Excel financial function that finds an interest rate per a given period of an annuity. The function calculates by iteration and can have no or more than one solution.
The function is available in all versions Excel 365 - 2007.
The syntax is as follows:
Where:
To efficiently use RATE formulas in your worksheets, please pay attention to these usage notes:
In this example, we'll look at how to make a RATE formula in its simplest form to calculate interest rate in Excel.
Let's say you've borrowed $10,000 that should be paid in full over the next three years. You are planning to pay 3 yearly installments of $3,800 each. What will the annual interest rate be?
To find it out, we define the following arguments for the Excel RATE function:
Please notice that we specify annual payment (pmt) as a negative number because it's outgoing cash.
It's assumed that the payment is to be made at the end of each year, so we can omit the [type] argument or set it to the default value (0). The other two optional arguments [fv] and [guess] are also omitted.
As the result, we get this simple formula:
=RATE(C2, C3, C4)
If it is required that the payment be entered as a positive number, then put the minus sign before the pmt argument directly in the formula:
=RATE(C2, -C3, C4)
Now that you know the essentials of using RATE in Excel, let's explore a couple of specific use cases.
Since most installment loans are paid monthly, it may be helpful to know a monthly interest rate, right? For this, you just need to supply an appropriate number of payment periods to the RATE function.
Suppose the loan is to be paid over 3 years in monthly installments. To get the total number of payments, we multiply 3 years by 12 months (3*12=36).
The other parameters are shown below:
Assuming the payment is due at the end of each month, you can find a monthly interest rate by using the already familiar formula:
=RATE(C2, C3, C4)
Compared to the previous example, the difference is only in the values used for the RATE arguments. Because the function returns an interest rate is for a given payment period, we get a monthly interest rate as the result:
If your source data includes the number of years over which the loan must be repaid, you can do the multiplication inside the nper argument:
=RATE(C2*12, C3, C4)
Taking our example a little further, how do you find annual interest rate for monthly payments? Simply by multiplying the RATE result by the number of periods per year, which is 12 in our case:
=RATE(C2, C3, C4) * 12
The below screenshot lets you compare the monthly interest rate in C7 and the annual interest rate in C9:
What if the payments are to be made at the end of each quarter?
First, you convert the total number of periods into quarterly:
Nper: 3 (years) * 4 (quarters per year) = 12
Then, use the RATE function to calculate the quarterly interest rate (C7):
=RATE(C2, C3, C4)
And multiply the result by 4 to get the annual interest rate (C9):
=RATE(C2, C3, C4) * 4
In the above examples, we were dealing with loans and calculated the interest rate based on three primary components: loan term, payment amount per period, and loan amount.
Another common scenario is finding an interest rate on a series of periodic cash flows where we know the future value, not the present value.
As an example, let's calculate an interest rate required to save up $100,000 in 5 years, provided you make the $1,500 payment at the end of each month with zero initial investment.
To have it done, we define the following variables:
To calculate monthly interest rate, the formula in C6 is:
=RATE(C2*12, C3, ,C4)
Please note that C2 contains the number of years. To get the total number of payment periods, we multiply it by 12.
To get annual interest rate, we multiply the monthly rate by 12. So, the formula in C8 is:
=RATE(C2*12, C3, ,C4) * 12
The RATE function in Excel can also be used for calculating the compound annual growth rate (CAGR) on an investment over a given period of time.
Supposing you want to invest $100,000 for 5 years and receive $200,000 in the end. How will your investment grow in terms of CAGR? To find that out, you set up the following arguments for the RATE function:
Please pay attention that the pmt argument is not used in this case, so we leave it blank in the formula:
=RATE(C2, ,C3, C4)
As the result, the Excel RATE function tells us that our investment has earned the 14.87% compound annual growth rate over 5 years.
As you may have noticed, the previous examples focused on solving specific tasks. This time, our goal is to create a universal interest rate calculator for annuity, which is a series of equal payments made at regular intervals.
Since we will be using an Excel RANK formula in its full form, we need to provide cells for all the arguments, including the optional ones:
To test our calculator in practice, let's try to find a monthly and annual interest on a saving account that will ensure $100,000 at the end of 5 years with a monthly payment of $1,500 made at the beginning of each period.
We input the variables in corresponding cells like shown in the image below, and enter the following formulas:
In C10, return a periodic interest rate:
=RATE(C2, C3, C4, C5, C6, C7)
In C11, output an annual interest rate:
=RATE(C2, C3, C4, C5, C6, C7) * C8
For our sample data, the results look as follows:
Please note that:
The more complex the function, the greater chance of an error. The RATE syntax is quite simple, but it still leaves room for mistakes, especially if you have little experience with Excel financial functions. Below, we will point out a few common errors and how to fix them.
Reason: occurs when the RANK function fails to find a solution.
Most often, this happens because positive numbers are used to represent outgoing cash flows. Please remember to put the minus sign before any amount that is paid out:
In some cases, you may need to help the RANK function to converge to a solution by providing an initial guess:
When calculating an interest rate with an undefined or zero present value (pv), be certain to specify the future value (fv):
Reason: occurs when one or more arguments are non-numeric.
To fix the error, double check the values used for the RANK arguments and make sure your numbers are not formatted as text.
Symptom: The result of your RANK formula is a negative percentage, or much lower or higher than expected.
Reason: When calculating monthly or quarterly payments, you forgot to convert the number of years to the total number of payment periods. Or a periodic interest rate is not converted to an annual interest rate.
To resolve this issue, use the following calculations to express the nper argument in appropriate units:
Monthly payments: nper = years * 12
Quarterly payments: nper = years * 4
To get an annual interest rate, multiply a periodic interest rate returned by the function by the number of periods per year.
Monthly payments: annual interest rate = RATE() * 12
Quarterly payments: annual interest rate = RATE() * 4
Symptom: The result of the formula appears as zero percentage with no decimal places (0%).
Reason: The calculated interest rate is less than 1%. Because the formula cell is formatted to show no decimal places, the displayed value is "rounded" to zero.
To solve this problem, simply apply the Percentage format with two or more decimal places to the cell containing your formula.
That's how to use RATE function in Excel to calculate interest rate. I thank you for reading and hope to see you on our blog next week!
Examples of RATE formula in Excel (.xlsx file)
Table of contents
