How to Calculate Compound Interest in Excel: Full Guide (2024)

Financial institutions generally pay you interest on interest 🤔

It means you are getting the interest not only on your initial investment.

You can get the interest on previous periods’ accumulated interest as well 🤩

Compound interest calculation is very common in financial modeling.

So, learning how to calculate compound interest in excel is important 💡

Let’s get started.

To follow along with me, please download the attached Excel workbook.

Calculate annual compound interest with the Excel formula

The compound interest formula considers both;

• The initial principal
• Previously accumulated interest

This is the compound interest formula.

Where;

• A = Future value including the compounded interest earned
• P = Present value of the investment
• r = Annual interest rate
• n = Compounding periods per annum
• t = Investment period in year has 2 matches in the lookup column.

Let’s calculate the interest compounded annually for the below data using the formula.

In Excel, enter the general compound interest formula.

All we have to do is to select the correct cell references.

So, you enter;

=B1*(1+B2/B4)^(B4*B3)

You will get the future value using the compound interest formula when you press “Enter” 👍

Calculate compound interest with the FV function

I know it is not easy to memorize the above Excel formula.

Do you like to learn an Excel function to calculate compound interest?

There are few Excel financial functions to calculate compound interest.

The FV Function is one of them 🥳

The FV function calculates the investment’s future value at a constant interest rate.

Let’s look at the above example to see how to apply the FV function to calculate compound interest.

1. Enter an equal sign and select the FV function.

Now your formula is;

=FV(

2. Select the interest rate per compounding period.

So we must select the excel cell with the annual interest rate.

Now the formula is;

=FV(B2

3. Select the total number of interest compounding periods.

In this example, it is 2 and we can select the investment period (years).

So, the updated formula is;

=FV(B2,B3

4. Enter the additional payment made each compounding period.

If there are no additional payments, enter zero.

So, let’s enter zero and the function will be like this.

=FV(B2,B3,0

5. Finally, enter the present value of the initial investment as a negative value.

Now your formula is;

=FV(B2,B3,0,-B1

6. Close the paratheses and press the “Enter” key.

The future value based on the annual compound interest rate is ready 🥳

You now understand how to calculate annual compounded value.

Pro Tip!

What is the Excel function to calculate compound interest with variable interest rates? 🤔

You can use the FVSCHEDULE function.

Give the range of variable interest rates as the schedule of the Excel function 😍

Now we will try intra-year compound interest rates to calculate compound interest.

We will begin with future value based on daily compound interest 😊

Calculate daily compound interest in Excel

The formula for daily compound interest and annual compound interest are fairly similar.

You have to adjust only the interest rate.

Let’s start.

1. Enter an equal sign and select the FV function.

Now your formula is;

=FV(

2. Enter the EFFECT function.

It helps to convert the annual interest rate to a daily compound interest rate.

Select the annual interest rate and the compounding periods per annum as arguments.

Now the formula is;

=FV(EFFECT(B2,B4)

3. Now repeat steps 3–5 of the above example.

Then, the formula would be;

=FV(EFFECT(B2,B4),B3,0,-B1

4. Close the paratheses and press the “Enter” key.

The future value with the daily compounded interest is ready🥳

Isn’t it easy? 😎

Let’s check how to calculate compound interest in Excel using monthly compound interest.

Calculate monthly compound interest in Excel

Do you think you have to learn another new compound interest formula for that?

Not at all 🤗

The monthly compound interest formula and the daily compound interest formula are the same.

The only difference is that the number of compounding periods per year is now 12.

Due to that, it gives 2 different compounding interest values.

Now, change the compounding periods to 12 and use the same compound interest formula.

=FV(EFFECT(B2,B4),B3,0,-B1

Future value with the monthly compound interest rate is ready 😍

You can calculate compound interest on monthly compound interest from this too.

=FV(B2/12,2*12,0,-B1)

You need to do only 2 changes for the FV function of the annual interest rate here.

1. Convert the annual interest rate to the relevant intra-year period compound interest rate.

So, divide the annual interest rate by the compounding frequency per annum.

As the interest is compounded monthly here, use the monthly interest rate.

• • If the interest is compounded quarterly, use the quarterly interest rate.
  • If the interest is compounded daily, use the daily interest rate.

2. Multiply the number of periods by 12.

That’s it – Now what?

Well done 👏🏻

Now you know how to calculate compound interest in excel for any compounding period.

Like to upgrade your finance career?

Use these compounding interest calculation methods in your financial models.

But don’t forget to use the old-school all-purpose functions of Excel too, like VLOOKUP, SUMIF, and IF.

To master these functions, all you need to do is enroll in my free 30-minute email course (and many more).

Other resources

It is good to learn how to calculate other loan factors along with the compound interest in Excel.

You can read our article about the PMT function and learn how to calculate the periodic loan payment.

Do you want to create your own loan payment calculator? Click here to get started 😊