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.

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.

The compound interest formula is not as easy as the simple interest formula 😕

Don’t worry!

I will show you how to convert the above mathematical formula easily to an Excel formula.

Kasper Langmann, Microsoft Office Specialist

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

Calculating the interest compounded annually - using compound interest formula in Excel

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)

Calculating compound interest using the compound interest formula.

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

The future value based on compound interest formula

You can use the Excel template provided above as your compound interest calculator.

You can see how the future value changes as you give different values to the below factors.

  • The interest rate
  • The compounding period
  • The time period of the investment value
  • The present value of the principal investment
Kasper Langmann, Microsoft Office Specialist

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(

Select the FV function to calculate the future value using a compound interest rate
  1. 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

Selecting the annual compounding annual interest rate
  1. 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

Select the total number of interest compounding periods
  1. 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

Enter zero for the additional payment as there are no additional payments
  1. Finally, enter the present value of the initial investment as a negative value.

Now your formula is;

=FV(B2,B3,0,-B1

Entering the present value of the initial investment as a negative value.
  1. Close the paratheses and press the “Enter” key.

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

Future value using the FV function

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(

Entering FV function to calculate compound interest on daily basis
  1. 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)

Compounding interest rate is entering using the EFFECT function.
  1. Now repeat steps 3–5 of the above example.

Then, the formula would be;

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

Daily compound interest formula after changing the annual interest rate to daily compound interest rate
  1. Close the paratheses and press the “Enter” key.

The future value with the daily compounded interest is ready🥳

Calculating the future value using after changing the annual interest rate to the compound interest on daily basis.

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

Calculating the future value using the monthly compounding interest formula.

Future value with the monthly compound interest rate is ready 😍

Calculate compound interest on monthly basis after changing the annual interest rate

Don’t you like to use functions inside functions?

Then use the formula below instead😎

Kasper Langmann, Microsoft Office Specialist

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

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

Calculate compound interest monthly basis without EFFECT function

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.
  1. 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 😊