How to Create a Compound Interest Calculator in Excel

Compound interest is one of the most powerful financial concepts with applications in banking, accounting, and finance. If you’re an accounting student or you dip your toes into the stock market, you’ll need to calculate compound interest on a regular basis.

There are two ways to calculate compound interest in Excel. You can use the compound interest formula or use Excel’s built-in financial functions that allow you to easily calculate compound interest.

In this article, we’ll walk you through these two methods, and in the end, you’ll be calculating compound interest like a pro.

How does compound interest work?

Suppose you have invested $1,000 with an interest rate of 5% which will accrue each year. In this case, you will earn $50 (5% of 1,000) after one year, bringing your gross to $1,050. The following year, interest will apply to the gross amount, which is 5% of 1,050. This will make your gross amount $1,102.5.

Similarly, interest will apply to the gross amount of the previous year the following year, ie 5% of 1102.5. As this continues, your initial investment (capital) grows exponentially each year.

Compound interest differs from simple interest because in the former, interest applies to the gross amount, while in the latter, it applies only to the principal. Compound interest has exponential increases just because of this difference.

Calculate compound interest using formula in Excel

Now that we understand how compound interest works, let’s learn how to calculate compound interest in Excel using the compound interest formula.

The compound interest formula is:

P’=P(1+R/N)^NT

Here:

• P is the principal or initial investment.
• P’ is the gross amount (after applying interest).
• R is the interest rate.
• N is the number of times compounding occurs per year.
• T is the total time (in years) over which compound interest is applied.

Now that we have the compound interest formula, let’s use it in Excel to calculate our five-year compound interest (from the previous example):

1. Create a table in Excel containing the principal value, interest rate and other parameters required in the formula.

2. Write the compound interest formula in the cell B5 (starting with an equals sign.)
3. Here the principal is shown in cell B1, the interest rate in cell B2the capitalization period (per year) in the cell B3and the total number of years in the cell B4. So your compound interest formula should look like this:

4. Hit enter and you will get the gross amount, more commonly known as the future value, that you will get after five years.

You’ve basically created a compound interest calculator. Now it’s just a matter of changing the values ​​to calculate your compound interest.

Calculate compound interest using the FV function

Another method to calculate compound interest in Excel is to use the FV function. The syntax of the FV function is as follows:

=FV(rate,nper,pmt,[pv],[type])

Here,

• Assess is your compound interest rate
• Nper where number of periods is the number of periods (years) for which you want to create the future value (gross amount)
• pmt where payment is the additional payment made each period. This value must be constant throughout the period. If no other amount is paid, this value will be 0.
• PV where principal is your initial investment
• [type] or end_or_beginning is used to specify whether to calculate the gross amount at the end of the period or at the beginning. (Enter 0 if you want the gross amount at the end of the period and 1 if you want it at the beginning of the period.)

Now that we understand the FV function, let’s use it to calculate compound interest in our initial example.

1. In an Excel cell, type =VF and enter the required values ​​in parentheses.
2. For the rate, we enter 5%, which was the interest rate in our original example. (Be sure to type the interest rate as a percentage, i.e. 5%, or as a decimal, i.e. 0.05.)
3. We calculate compound interest over five years, so the value of nper is 5.
4. There is no additional payment in our example, so we will enter 0.
5. Type 1000 in PV because it is our initial or main investment.
6. Type 0 in [TYPE] because we want to calculate our gross amount (future value) at the end of the period.
7. Close the parentheses and hit enter, and you’ll get your compound interest for five years.

To note: Don’t forget to put commas between the values.

Calculate compound interest for an intra-annual period

Notice how we’ve only calculated annual compound interest so far. But often we want to calculate quarterly, monthly or even daily compound interest. It’s time to understand how to calculate compound interest for an intra-annual period.

We can calculate monthly, weekly or daily compound interest as well as annual compound interest using the compound interest formula and an Excel function. Let’s try the compound interest formula first:

Calculate intra-annual compound interest using the formula

We already know that the compound interest formula is given as follows:

P’=P(1+R/N)^NT

Here, N denotes the number of times composition occurs in a period (year). In our initial example, we set N = 1 because compounding only happens once a year. If we want to calculate monthly compound interest for our initial example, we would put N=12 (since there are 12 months in a year.) Similarly, for daily compound interest, we would put N=365.

The monthly compound interest in our initial example is calculated as follows:

Calculating intra-annual compound interest using the EFFECT function

Excel has an EFFECT function which is one of Main financial functions in Excel. You can use it to calculate compound interest for an intra-year period. The syntax of the effect function is as follows:

=P+(P*EFFECT(EFFECT(R,N)*T,T))

Here,

• P is the main
• R is the interest rate
• NOT is the number of times compounding occurs per year
• J is the total time (in years) over which compound interest is applied

If we go back to our initial example, we can calculate monthly compound interest using the EFFECT function as follows:

1. Our principal is $1000, so we enter P=1000
2. The interest rate is 5%, so R=5%
3. Since we are calculating monthly compound interest, N = 12 (for semi-annual compound interest, N will be 2, for daily compound interest, N will be 365, etc.)
4. We need to calculate monthly compound interest for five years; this is why T=5.
5. Putting the values, we get:

Learn how to easily find compound interest

By now you have a good understanding of compound interest. Now all you need is a little practice and you will be able to calculate annual or intra-annual compound interest using the compound interest formula or functions in Excel.

But why stop there? There are a ton of Excel functions you can learn to perform tricky calculations and quickly analyze complex data.