**How to Calculate** **Compound Interest**** in Excel: ****Formula ****& ****Templates**

**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.

Let’s get started! 😊

**Table of Content**

**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**.

**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? 😊

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

**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.

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:

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:

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:

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:

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.

**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. 👍