How to Calculate Compound Interest in Excel: Formula & Templates

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Compound interest is an important concept in the financial world.

It’s a powerful force that’s a standard in both finance and economics.

Unfortunately, Excel has no function yet which lets you calculate compound interest directly.

In this article, we’ll show you the steps to calculate compound interest in Excel for different intervals. We’ll also point you to a template you can download.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started! 😊

What is compound interest?

Compound interest is when you’re able to reinvest the interest, instead of paying it out.

It’s better understood in comparison with the concept of simple interest

For example, you deposited $1,000 on a bank at 3% for a year. After a year, your money will grow from $1,000 to $1,030. Your initial deposit earned $30 as interest.

Now, let’s say you deposited the same amount of money on a bank for 2 years at 3% annual interest compounded annually.

During the first year, your money will earn $30, being 3% of $1,000. Next year, however, it won’t just be $30.

The principal deposit where the interest is computed from will grow from $1,000 to $1,030. The $30 is added to your principal instead of being withdrawn.

That means on the second year, the 3% will be calculated from the $1,030, giving you $1,060.9.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

Compound interest formula

If you have tried searching the internet for formulas on compound interest, you probably scratched your head due to the complexity of the formulas.

To simplify, here’s the base formula of compound interest:

FV = PV * (1 + i)n

Where:

  • ‘FV’ – future value of the investment; the total value you’ll get at the end of the investment period
  • ‘PV’ – present value of the investment; the initial deposit
  • ‘i’ – interest rate earned every period
  • ‘n’ – number of periods

Let’s try this formula with our earlier example:

  • ‘PV’ = $1,000
  • ‘i’ = 3%
  • ‘n’ = 2

FV = 1000 * (1 + 0.03)2

FV = 1000 * 1.0609

FV = 1,060.9

Exactly the amount mentioned earlier. Awesome, right? 😊

Kasper Langmann, Co-founder of Spreadsheeto

How to calculate compound interest in Excel

To compute the compound interest in Excel for different time periods, all you have to do is convert the formula above into a relatable formula in Excel.

The formula now becomes:

= initial investment * (1 + annual interest rate/compounding periods per year) ^ (years * compounding periods per year)

To make our examples easier to understand, we have prepared this template which we’ll be filling out as we go on:

simple compound interest calculator template

Annual compound interest formula

The easiest to compute out of all time periods is the annual compound interest.

Why? Because the formula we presented to you earlier is set for annual. Therefore, no adjustments are needed.

In fact, we could take out the ‘Number of compounding periods per year’ parameter which results in:

= initial investment * (1 + annual interest rate) ^ (years)

Looks familiar? It’s because this is actually the base formula for the compound interest we showed you earlier in Chapter 3 of this tutorial.

Kasper Langmann, Co-founder of Spreadsheeto

But for the sake of consistency, let’s leave the formula as it is.

For this example, let’s say you have the following factors:

  • Initial investment: $1,000
  • Annual interest rate: 3%
  • Number of compounding periods: 1
  • Years: 10

To calculate the balance, all we have to do is input the formula and fill it out with the correct cell references:

annual compound interest exercise result

After 10 years, your $1,000 compounded annually at an annual interest of 3%, becomes  $1,343.92.

Semi-annual compound interest formula

To solve the compound interest for other time periods, all you have to do is change the ‘Number of compounding periods per year’.

Here’s the semi-annual compound interest formula:

= initial investment * (1 + annual interest rate/2) ^ (years * 2)

We’ll still be using the same factors for this example. However, the principal amount will now be compounded semi-annually:

  • Initial investment: $1,000
  • Annual interest rate: 3%
  • Number of compounding periods: 2
  • Years: 10

Here’s the result:

semi-annual compound interest exercise result

Your $1,000 investment becomes $1,346.86 when invested for 10 years at 3% annual interest rate compounded semi-annually.

Quarterly compound interest formula

Here’s the quarterly compound interest formula:

= initial investment * (1 + annual interest rate/4) ^ (years * 4)

For this example, let’s compound the interest quarterly:

  • Initial investment: $1,000
  • Annual interest rate: 3%
  • Number of compounding periods: 4
  • Years: 10

Here’s the result:

After 10 years, your $1,000 will be worth $1,348.35 at 3% annual interest rate compounded quarterly.

Monthly compound interest formula

As you have guessed, all you need to do is change the ‘Number of compounding periods per year’ to 12:

= initial investment * (1 + annual interest rate/12) ^ (years * 12)

Using the same factors, let’s compound the interest monthly:

  • Initial investment: $1,000
  • Annual interest rate: 3%
  • Number of compounding periods: 12
  • Years: 10

Here’s the result:

monthly compound interest exercise result

In 10 years, a $1,000 investment becomes $1,349.35 at 3% annual interest compounded monthly.

Daily compound interest formula

For the daily compound interest formula, use 365 as the parameter for ‘Number of compounding periods per year’:

= initial investment * (1 + annual interest rate/365) ^ (years * 365)

With the same factors, let’s compound the interest daily:

  • Initial investment: $1,000
  • Annual interest rate: 3%
  • Number of compounding periods: 365
  • Years: 10

Here’s the result:

daily compound interest exercise result

In 10 years, your $1,000 investment will be worth $1,349.84 at 3% annual interest compounded daily.

Compound Interest Excel Template

The exercise file for this tutorial contains the exact template we used to explain the points above.

However, if you’re looking for a more advanced template, Vertex42’s Compound Interest Calculator for Excel is also great.

vertex42 compound calculator excel template

Wrapping things up…

Understanding the base formula of compound interest is the key in knowing how to compute for the daily, monthly, quarterly, or semi-annual compound interest easily.

If you’re having a hard time, download the exercise file in Chapter 2 of this tutorial and try to understand how the formula works. 👍

Kasper Langmann, Co-founder of Spreadsheeto