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.

Excel RATE function

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 , Excel , Excel , Excel , Excel and Excel

The syntax is as follows:

Where:

• Nper (required) - the total number of payment periods such as years, months, quarters, etc.
• Pmt (required) - the fixed payment amount per period that cannot be changed over the life of the annuity. Usually, it includes principal and interest, but no taxes.
• Pv (required) - the present value, i.e. the current value of the loan or investment.
• Fv (optional) - the future value, i.e. the cash balance you wish to have after the last payment. If omitted, it defaults to 0.
• Type (optional) - indicates when the payments are made:
  • 0 or omitted (default) - payment is due at the end of the period
  • 1 - payment is due at the beginning of the period
• Guess (optional) - your assumption for what the rate might be. If omitted, it defaults to 10%.

6 things you should know about Excel RATE function

To efficiently use RATE formulas in your worksheets, please pay attention to these usage notes:

1. The RATE function calculates through trial and error. If it fails to converge to a solution after 20 iterations, a #NUM! error is returned.
2. By default, an interest rate is calculated per payment period. But you can derive an annual interest rate by multiplication as shown in this example.
3. Use positive numbers to represent cash that you receive (inflows) and negative numbers to represent cash that you pay out (outflows).
4. Although the RATE syntax describes pv as the required argument, it can actually be omitted if you include the fv argument. Such syntax is typically used for calculating interest rate on a saving account.
5. The guess argument can be omitted in most cases because it's just a starting value for an iterative procedure.
6. When calculating RATE for different periods, make sure you are consistent with the values supplied for nper and guess. For example, if you are to make annual payments on a 3-year loan at 8% annual interest, use 3 for nper and 8% for guess. If you are going to make monthly payments on the same loan, then use 3*12 for nper and 8%/12 for guess.

Basic RATE formula in Excel

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, that should be paid in full over the next three years. You are planning to pay 3 yearly installments of $3, each. What will the annual interest rate be?

To find it out, we define the following arguments for the Excel RATE function:

• Nper in C2 (number of payments): 3
• Pmt in C3 (payment amount): -3,
• Pv in C4 (loan amount): 10,

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:

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:

How to calculate interest rate in Excel - formula examples

Now that you know the essentials of using RATE in Excel, let's explore a couple of specific use cases.

How to calculate monthly interest rate on a loan

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:

• Nper in C2 (number of periods): 36
• Pmt in C3 (monthly payment):
• Pv in C4 (loan amount): 10,

Assuming the payment is due at the end of each month, you can find a monthly interest rate by using the already familiar formula:

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:

How to calculate annual interest rate in Excel

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:

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):

And multiply the result by 4 to get the annual interest rate (C9):

How to find interest rate on saving account

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 $, in 5 years, provided you make the $1, payment at the end of each month with zero initial investment.

To have it done, we define the following variables:

• Nper in C2 (total number of payments): 5*12
• Pmt in C3 (monthly payment): -1,
• Fv in C4 (desired future value): ,

To calculate monthly interest rate, the formula in C6 is:

Please note that C2 contains the number of years. To get the total number of payment periods, we multiply it by

To get annual interest rate, we multiply the monthly rate by So, the formula in C8 is:

How to find compound annual growth rate on investment

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 $, for 5 years and receive $, 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:

• Nper (C2): 5
• Pv (C3): ,
• Fv (C4): ,

Please pay attention that the pmt argument is not used in this case, so we leave it blank in the formula:

As the result, the Excel RATE function tells us that our investment has earned the % compound annual growth rate over 5 years.

Create interest rate calculator in Excel

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:

• Total number of payments (nper) - C2
• Payment amount (pmt) - C3
• Annuity present value (pv) - C4
• Annuity future value (fv) - C5
• Annuity type (type) - C6
• Estimated interest rate (guess) - C7
• Number of periods per year - C8

To test our calculator in practice, let's try to find a monthly and annual interest on a saving account that will ensure $, at the end of 5 years with a monthly payment of $1, 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:

In C11, output an annual interest rate:

For our sample data, the results look as follows:

Please note that:

• For nper, we input 60 (5 years * 12 months = 60 payment periods).
• For type, we input 1 (payment is due at the beginning of the period). To prevent mistakes, it makes sense to create a drop-down list in C6 to only allow 0 and 1 values for the type argument.
• If pv is 0 or not defined (like in this example), be sure to specify the fv argument.

Excel RATE function not working

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.

#NUM! error

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 initialguess:

When calculating an interest rate with an undefined or zero present value (pv), be certain to specify the future value (fv):

#VALUE! error

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.

RATE function returns incorrect result

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

RATE formula returns zero percentage

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!

Practice workbook for download

Examples of RATE formula in Excel (.xlsx file)

You may also be interested in:

Managing personal finances can be a challenge, especially when trying to plan your payments and savings. Excel formulas and budgeting templates can help you calculate the future value of your debts and investments, making it easier to figure out how long it will take for you to reach your goals. Use the following functions:

• PMT calculates the payment for a loan based on constant payments and a constant interest rate.

• NPER calculates the number of payment periods for an investment based on regular, constant payments and a constant interest rate.

• PV returns the present value of an investment. The present value is the total amount that a series of future payments is worth now.

• FV returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Figure out the monthly payments to pay off a credit card debt

Assume that the balance due is $5, at a 17% annual interest rate. Nothing else will be purchased on the card while the debt is being paid off.

Using the function PMT(rate,NPER,PV)

=PMT(17%/12,2*12,)

the result is a monthly payment of $ to pay the debt off in two years.

• The rate argument is the interest rate per period for the loan. For example, in this formula the 17% annual interest rate is divided by 12, the number of months in a year.

• The NPER argument of 2*12 is the total number of payment periods for the loan.

• The PV or present value argument is

Figure out monthly mortgage payments

Imagine a $, home at 5% interest, with a year mortgage.

Using the function PMT(rate,NPER,PV)

=PMT(5%/12,30*12,)

the result is a monthly payment (not including insurance and taxes) of $

• The rate argument is 5% divided by the 12 months in a year.

• The NPER argument is 30*12 for a 30 year mortgage with 12 monthly payments made each year.

• The PV argument is (the present value of the loan).

Find out how to save each month for a dream vacation

You’d like to save for a vacation three years from now that will cost $8, The annual interest rate for saving is %.

Using the function PMT(rate,NPER,PV,FV)

=PMT(%/12,3*12,0,)

to save $8, in three years would require a savings of $ each month for three years.

• The rate argument is % divided by 12, the number of months in a year.

• The NPER argument is 3*12 for twelve monthly payments over three years.

• The PV (present value) is 0 because the account is starting from zero.

• The FV (future value) that you want to save is $8,

Now imagine that you are saving for an $8, vacation over three years, and wonder how much you would need to deposit in your account to keep monthly savings at $ per month. The PV function will calculate how much of a starting deposit will yield a future value.

Using the function PV(rate,NPER,PMT,FV)

=PV(%/12,3*12,,)

an initial deposit of $1, would be required in order to be able to pay $ per month and end up with $ in three years.

• The rate argument is %/

• The NPER argument is 3*12 (or twelve monthly payments for three years).

• The PMT is (you would pay $ per month).

• The FV (future value) is

Find out how long it will take to pay off a personal loan

Imagine that you have a $2, personal loan, and have agreed to pay $ a month at 3% annual interest.

Using the function NPER(rate,PMT,PV)

=NPER(3%/12,,)

it would take 17 months and some days to pay off the loan.

• The rate argument is 3%/12 monthly payments per year.

• The PMT argument is

• The PV (present value) argument is

Figure out a down payment

Say that you’d like to buy a $19, car at a % interest rate over three years. You want to keep the monthly payments at $ a month, so you need to figure out your down payment. In this formula the result of the PV function is the loan amount, which is then subtracted from the purchase price to get the down payment.

Using the function PV(rate,NPER,PMT)

=PV(%/12, 3*12,)

the down payment required would be $6,

• The $19, purchase price is listed first in the formula. The result of the PV function will be subtracted from the purchase price.

• The rate argument is % divided by

• The NPER argument is 3*12 (or twelve monthly payments over three years).

• The PMT is (you would pay $ per month).

See how much your savings will add up to over time

Starting with $ in your account, how much will you have in 10 months if you deposit $ a month at % interest?

Using the function FV(rate,NPER,PMT,PV)

=FV(%/12,10,,)

in 10 months you would have $2, in savings.

• The rate argument is %/

• The NPER argument is 10 (months).

• The PMT argument is

• The PV (present value) argument is

Are you ready to kick it up a notch?

In this article, we will calculate the average and geometric average return of a stock, using Excel.

Suppose you are interested in buying Apple stock. Your first task is to calculate the stock return of Apple in the last 2 years.

First, we are going to obtain stock prices.

For this, we will use Yahoo Finance. We already know a thing or two about it, right?

We begin by typing the stock ticker. Do you remember what it is? “AAPL”!

Afterward, we select historical data. We download the stock prices over the last 2 years from July 1^st, to July 1^st,

Then, we need to set the data frequency.

There are 3 options to choose from &#; daily, weekly, or monthly.

Basically, the choice of data frequency depends on the return you want to calculate.

When you estimate daily returns, you go for daily frequency. Weekly returns require weekly frequency. And so on.

Let’s say we are interested in calculating monthly returns. This gives us 24 data points.

We press ‘apply’ and then ‘download’, in order to retrieve the needed numbers.

Time to check out what we’ve got…

We open the downloaded file and come across the date and a whole bunch of information about the stock price, such as the opening, as well as the highest and lowest value during the period we’ve specified earlier.

Which are the figures our calculations require, though?

Well, we want to use either the close or the adjusted close price. The former corrects for splits, whereas the adjusted one does so for both dividends AND splits.

Hence, we will use the adjusted close price. For conciseness, we get rid of all the information we won’t need and format the table in a more presentable way, based on the guidelines provided previously.

To save you some time, I have already done that.

Our next task is to calculate the holding period return for the stock.

What’s the formula we should be working with?

It is the ending value of an investment minus its beginning value, divided by the beginning value.

In our example, we have $ minus $ divided by $ which gives us approximately %.

Finally, we go ahead and drag this formula all the way down.

Don’t forget to convert the values to percentages. Actually, you can get away with a simple shortcut.

Magicians never reveal their secrets, but we will willingly tell you ours: Excel is all about shortcuts! Be smart and use them as much as possible! This can save you a lot of time that you will eventually need for analyzing your data.

Here is one shortcut we can opt for, at this stage. Select the range of values, and then press and hold control plus shift, plus 5.

The last piece of the puzzle is to estimate the simple and geometric mean.

To obtain the former, we use the average function of Excel. We select the first argument and, after that, drag the range down to the last term.

Alternatively, we can use another useful shortcut. Press and hold Control plus shift plus the down arrow. This function marks the entire row of values below the cell you initially selected.

So, we estimate the mean return to be %.

Now, let’s calculate the geometric mean return.

For this purpose, we will use the geometric function. Basically, it gives us the geometric mean of an array or a range of positive data.

We type “GEOMEAN” and we pick the data range we will use. Don’t forget to add 1 to the expression before closing the parentheses.

The last step is to subtract 1 from the total.

Excel interprets the expression in the following way: Add one to each of the returns and then take the geometric mean.

For those of you using earlier versions of Excel ( or older), you need to press Control, Shift and Enter after you key in the formula. This command makes Excel convert the expression to an array formula. In other words, it performs multiple calculations on one or more items in an array. If you have done that right, you will see braces that are added to the formula.

The geometric mean comes to %.

In our next article, we will learn how to measure the standard deviation of the stock’s returns.

Keep calm and invest on!

This tutorial looks at how to use the FV function in Excel to find the future value of a series of periodic payments and a single lump-sum payment.

Building your personal and corporate finances requires thorough planning. One of the most important factors of success is understanding how much an investment made today will grow to in the future. That is called the future value of investment, and this tutorial will teach you how to calculate it in Excel.

Future value in Excel

The future value (FV) is one of the key metrics in financial planning that defines the value of a current asset in the future. In other words, FV measures how much a given amount of money will be worth at a specific time in the future.

Normally, the FV calculation is based on an anticipated growth rate, or rate of return. When the money is deposited in a saving account with a predefined interest rate, determining a future value is quite easy. The FV of investments in stocks, bonds or other securities may be hard to calculate accurately because of a volatile rate of return.

Luckily, Microsoft Excel provides a special function that does all the math behind the scenes based on the arguments that you specify.

Excel FV function

FV is an Excel financial function that returns the future value of an investment based on a fixed interest rate. It works for both a series of periodic payments and a single lump-sum payment.

The function is available in all versions Excel , Excel , Excel , Excel , Excel and Excel

The FV syntax is as follows:

Where:

• Rate (required) - the interest rate per period. If you pay once a year, supply an annual interest rate; if you pay each month, then you should specify a monthly interest rate, and so on.
• Nper (required) - the total number of payment periods for the lifetime of an annuity.
• Pmt (optional) - the constant amount paid each period. Should be expressed as a negative number. If omitted, it is assumed to be 0, and the pv argument must be included.
• Pv (optional) - the present value of the investment. Should be represented by a negative number. If omitted, it defaults to 0, and the pmt argument must be included.
• Type (optional) - indicates when the payments are made:
  • 0 or omitted (default) - at the end of a period (regular annuity)
  • 1 - at the beginning of a period (annuity due)

4 things to remember about Excel FV function

To correctly build a FV formula in your worksheets and avoid common errors, please keep in mind these usage notes:

1. For any inflows such as dividends or other earnings, use positive numbers. For any outflows such as deposits to a saving or investing account, use negative numbers.
2. If the present value (pv) is zero or omitted, the payment amount (pmt) must be included, and vice versa.
3. The rate argument can be expressed as a percentage or decimal number, e.g. 8% or
4. To get the correct future value, you must be consistent with nper and rate. For instance, if you make 3 yearly payments at an annual interest rate of 5%, use 3 for nper and 5% for rate. If you do a series of monthly investments for a period of 3 years, then use 3*12 (a total of 36 payments) for nper and 5%/12 for rate.

Basic future value formula in Excel

This example shows how to use the FV function in Excel in its simplest form to calculate future value, given a periodic interest rate, the total number of periods, and a constant payment amount per period.

• Periodic interest rate (rate): C2
• Number of periods (nper): C3
• Payment amount (pmt): C4

Let's say you are going to make a yearly $1, payment for 10 years with an annual interest rate of 6%. It is assumed to be a regular annuity where all payments are made at the end of the year.

To find the future value, configure the FV function in this way:

Please notice that pmt is a negative number because this money is paid out.

If the payment is represented by a positive number, don't forget to put the minus sign right before the pmt argument:

How to calculate future value in Excel - formula examples

The basic Excel FV formula is very simple, right? Now, let's have a look at how to tweak it to handle a couple of most common scenarios.

FV formula for periodic payments

When investing money through a series of regular savings, it often happens that you are provided with an annual interest rate and the investment term defined in years, whereas the payments are to be made weekly, monthly, quarterly or semiannually. In such situations, it is very important that the rate and nper units be consistent.

To convert an annual interest rate to a periodic rate, divide the annual rate by the number of periods per year:

• Monthly payments: rate = annual interest rate / 12
• Quarterly payments: rate = annual interest rate / 4
• Semiannual payments: rate = annual interest rate / 2

To get the total number of periods, multiply the term in years by the number of periods per year:

• Monthly payments: nper = no. of years * 12
• Quarterly payments: nper = no. of years * 4
• Semiannual payments: nper = no. of years * 2

Now, let's see how it works in practice. Suppose you monthly invest $ for 3 years with an annual interest rate of 6%. The source data is input in these cells:

• Annual interest rate (B2): 6%
• No. of years (B3): 3
• Monthly payment (B4):
• Periods per year (B5): 12

To calculate the future value of this investment, the formula in B7 is:

As shown in the image below, the same formula determines the future value based on quarterly savings equally well:

FV formula for lump-sum investment

If you choose to invest money as a one-time lump sum payment, the future value formula is based on the present value (pv) rather than periodic payment (pmt).

So, we set up our sample data as follows:

• Annual interest rate (C2): 7%
• No. of years (C3): 5
• Present value (C4):

The formula to calculate the future value of the investment is:

Please notice that:

• The investment amount (pv) is a negative number because it's an outflow.
• The pmt argument is 0 or omitted.

If the compounding periods for your investment are not annual, then to determine the future value accurately, you need to make the following adjustments to the formula:

• For rate, divide an annual interest rate by the number of compounding periods per year.
• For nper, multiply the number of years by the number of compounding periods per year.

As an example, let's find the future value of the above investment with an interest rate compounded monthly. For this, we divide an annual interest rate (C2) by 12 and multiply the number of years (C3) by

or

Where C5 is the number of compounding periods per year:

Get future value for different compounding periods

To compare the amount of growth generated by various compounding periods, you need to supply different rate and nper to the FV function.

To have all calculations performed with a single formula, do the following:

• Input the number of compounding periods per year in B2.
• Arrange your data like shown in the image below.
• Enter the following formula in C2 and drag it down through C6:
  

Please pay attention that we lock the annual interest rate ($F$2), the number of years ($F$3) and the investment amount ($F$4) references with the dollar sign ($) so they won't shift when copying down the formula.

Make a future value calculator in Excel

If your goal is to build a universal FV calculator that works for both periodic and lump-sum payments with either annuity type, then you will need to use the Excel FV function in its full form.

For starters, allocate cells for all the arguments, including the optional ones like shown in the screenshot below. And then, define the arguments in this way:

• Rate (periodic interest rate): B2/ B7 (annual interest rate / periods per year)
• Nper (total number of payment periods): B3*B7 (number of years * periods per year)
• Pmt (periodic payment amount): B4
• Pv (initial investment): B5
• Type (when payments are due): B6
• Compounding periods per year: B7

Putting the arguments together, we get this formula:

Suppose you wish to save some money for renovating your house in 5 years. You deposit $3, to your saving account at an interest rate of 7% compounded monthly. Furthermore, you are going to add $ at the beginning of each month. How much money will there be in your saving account in 5 years? According to our Excel FV calculator - around $11,

When setting up a future value calculator for other users, there are a few things to take notice of:

• Both pmt and pv should be negative numbers because they represent an outflow. If positive numbers are entered in the corresponding cells, then put the minus sign before these arguments directly in the formula.
• If pmt is zero or omitted, be sure to specify the present value (pv) and vice versa.
• For type, consider creating a drop-down list to only allow 0 and 1 values. This will help you prevent accidental mistakes that users could make.
• The Compounding periods per year cell (B7) must have a number in it other than zero, otherwise the formula will return a #DIV/0 error. If an interest rate is compounded annually, enter 1 in that cell.

Excel FV function not working

If a FV formula results in an error or yields a wrong result, in all likelihood, that will be one of the following.

#VALUE! error

May occur if one or more arguments are non-numeric. To fix the error, check if any of the numbers referenced in your formula are formatted as text. If some are, then convert text values to numbers.

FV function returns an incorrect future value

If the returned future value is negative or much lower than expected, most likely, either the pmt or pv argument, or both, are represented by positive numbers. Please remember that negative numbers should be used for all outgoing payments.

That's how to how to calculate future value of annuity in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Future value formula in Excel (.xlsx file)

You may also be interested in: