Compound Interest Formula in Excel

Compound interest is a concept heavily used by the banking and finance industry.

If you’re investing or saving money in a bank, Compound Interest is the magical formula that is going to help you reach your money goals faster. It’s often called the Eighth wonder of the world.

If you’re borrowing money from a bank, however, then Compound interest is what’s going to make the money you owe rise faster.

In fact, the more time you take, the faster the amount grows.

In this tutorial, we will explain what Compound interest is, how it’s calculated, and how to calculate compound interest in Excel spreadsheets.

What is Compound Interest?

Compound interest is the interest on both the initial principal amount, as well as the interest accumulated over the past periods.

You can think of compound interest as a sort of ‘interest on interest’.

Since you are having the interest compounded with the passage of time, your initial sum grows at a much faster rate than the simple interest (which only applies to the principal amount).

To understand the concept, let’s take an example.

Let’s say you initially deposit $100 to a bank that offers an interest rate of 5% compounded yearly. 

At the end of the first year, 5% of $100 (=$5) gets added as an interest to your initial amount.  In other words, your bank account now contains $105.

At the end of the second year, you will earn interest on this compounded amount (not just your initial deposit). So you now get 5% of $105 (=$5.25) added to your account.

Your bank account now contains $105+$5.25=$110.25.

At the end of the third year, you again get 5% of $110.25 (=$5.51) added to your account. In other words, your bank account now contains $110.25+$5.51=$115.76.

In comparison with Simple Interest, Compound Interest is different as it also earns you interest on the interest, while in Simple Interest, you only earn the interest on the base value.

Below is an example, where I have calculated simple and compound interest for 10 years or the base payment of $100 with a 5% annual interest rate

As you can see, due to the compounding effect, the return when compound interest is applied is higher than simple interest.

The same happens when you borrow money from a bank. In fact, Compound interest is the biggest reason most people find it difficult to pay back their student loans even years after passing out of school/college (or credit card loans). 

Also read: How to Calculate Growth Rate in Excel

How to Calculate Compound Interest?

Let us understand the compound interest calculation in a little more detail. 

If your principal amount is represented by a P and interest is represented by R, then at the end of the first year, the amount in your account is P+(P*R) or P(1+R).

At the end of the second year, the amount increases to P(1+R)+P(1+R)*R or P(1+R)².

Similarly, at the end of the third year, the amount increases to P(1+R)² +P(1+R)²*R or P(1+R)³.

This means, if N is the number of years (in this case), then the formula for the Compound interest accumulated will be:

P(1+R)N

Note that the above formula works in all cases, whether your interest is compounded annually, semi-annually, monthly or weekly.

Let us take another example to demonstrate this.

Let’s say the bank offers an annual interest of 5%, which is compounded semi-annually (twice a year).

This means there are 2 compounding periods in a year. The interest rate will then need to be divided by 2 and the time period multiplied by 2 in the above formula.

So, if you want to compute the worth of your $100 investment after 10 years, in this case, it is going to be:

100(1+0.05/2)(10*2) =$163.86

This means we can further generalize the compound interest formula to:

P(1+R/t)(n*t)

Here, t is the number of compounding periods in a year.

If interest is compounded quarterly, then t=4. If interest is compounded on a monthly basis, then t=12.

Also read: Percentage Difference Calculator Excel

Two Ways to Calculate Compound Interest in Excel

Now let us see how we can calculate the compound interest in Excel.

Take the following example where you’ve invested $1,000 in a bank that compounds yearly at an annual interest rate of 8%.

How can we find the compound interest after 10 years?

There are two ways to find this in Excel:

• Using the general Compound Interest formula
• Using the FV function

Let us look at each of these methods.

Using the General Compound Interest Formula to Calculate Compound Interest in Excel

The first method uses the same generic formula that we used in the previous section to compute the compound interest:

P(1+R/t)(n*t)

In cell B6, type the following formula:

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

Note that the above formula is simply an Excel implementation of the general compound interest formula.

The result we get is as follows:

 Let’s take up another case where the interest is compounded twice a year:

 Applying the same formula, the result we get is as follows:

This means the formula works with all scenarios.

Using the FV Function to Calculate Compound Interest in Excel

The second method to compute the compound interest is using the FV function. The term FV is short for “Future Value”.

The Excel FV function is a financial function that returns the future value of an investment. The function assumes a periodic and constant payment made with a constant interest rate.

Syntax of this function is as follows:

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

Here,

• rate is the interest rate per payment period
• nper is the number of payment periods for which you want to compute the future value
• pmt is the additional payment made during each period. This value is usually specified as a negative number. If there’s no additional payment being made then this value can be 0.
• pv is the present value (or principal amount). This value is optional and, if ommitted, is assumed to be 0. It is also specified as a negative number (as it’s considered an outflow).
• type is an integer specifying when the payments are due. A value of 0 means the payment is due at the end of the period, while a value of 1 indicates that the payment is due at the beginning of the period. This value is also optional with a default value of 0.

Let us use the above function to compute the compound interest for the following example:

We use the FV formula to calculate the compound interest as follows:

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

Note that the above formula calculates the future value assuming that the interest is compounded just once every year within the given time period.

You need to make sure that both rate and nper values provided to the function are consistent.

This means, if the bank pays at an 8% annual interest two times in a year, then use rate/2 in the first parameter and nper*2 in the second one.

Let us look at the following case where we want to find the future value of a $1000 investment at the end of 10 years, where interest is compounded twice a year at an annual rate of 8%:

The FV formula, in this case, will be as follows:

=FV(B2/B3, B4*B3,0,-B1)

Using the above formula, we get the future worth of our investment to be $2,191.12, as shown below:

Note: Since the pv value has been specified as a negative number, the result we got is positive.

In this tutorial, I covered all that you need to know about compound interest.

We explained what it is and how it is calculated. We also showed you two ways to compute the compound interest in Excel.

We hope you found this Excel tutorial helpful.

Other Excel tutorials you may also like:

• How to Calculate NPV in Excel (Net Present Value)
• How to Calculate IRR with Excel (easy Examples)
• How to Apply Accounting Number Format in Excel
• How to Calculate Standard Error In Excel
• How to Find Slope in Excel (Easy Formula)
• How to Find Percentile in Excel (PERCENTILE Function)
• How to Calculate Cumulative Percentage in Excel?
• How to Calculate Year-Over-Year (YOY) Growth in Excel (Formula)