In this tutorial, we’ll explain how to calculate compound interest using the Excel formula with regular and irregular deposits. We shall also discuss how to calculate future values of investment based on daily, monthly, and yearly compounding interest rates.

Firstly, we have to know that the compounding interest rate concept is the center point of the investment world. Basically, it moves the stock market, the bond market, or simply the world. Simply, understanding compounding interest rates can change your behavior with money and savings.

Moreover, the concepts might seem a bit complex for individuals who did not study finance, accounting, or business studies. But if you read this article with attention, your misconceptions will be removed, your understanding will be clear.

The following image provides an overview of the calculation process of compound interest in Excel using the **FV** function. Later, we’ll show you the process with simple steps and proper explanations.

**Table of Contents**Expand

## Calculate Compound Interest Using Excel Formula with Regular Deposits: 2 Methods

Say, you’re going to run a savings scheme with one of your trusted banks. Here, you want to know what your total amount after a certain period (years) will be. In this case, we will use the Excel **FV **function. We can also calculate it with Excel formulas.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience.

### 1. Using FV Function

**The FV function** returns the future value of an investment based on periodic, constant payments and a constant interest rate.

**📌**** Steps:**

- Firstly, select cell
**C12**and write down the formula

**=FV(C6,C8,C9,C10,C11)**

Here,

**C6**=Interest Per Period, (** rate**)

**C8**=Numbers pet periods, (** nper**)

**C10**=Payment per period, (** pmt**)

**C11**=Present Value, (**pv**)

The syntax **FV(C6,C8,C9,C10,C11) **returns the future value by compound calculation.

- After that, Press
**ENTER,**and the formula will display the future value.

**Read More: **Formula for Monthly Compound Interest in Excel

### 2. Calculate Compound Interest with Regular Deposits Using Manual Formula

We can use an Excel formula for calculating compound interest with regular deposits. For this, you have to follow the steps below.

**📌**** Steps:**

- Initially, we have taken only 9 months or periods (under the Period column). Add more periods under this column if necessary and apply the formulas from the above row.
- After that, in cell
**C5**(under the column “New Deposit”), we used this formula,**C5=$H$7**. And then applied this formula to other cells in the column.

- Then, in cell
**D5**(under the column Starting Principle), We used this formula,**D5=H5+C5**. This formula is used just once. This is just to add the initial investment to the formula.

- Later, in the cell
**E5**(under the column Amount at the End), We have used this formula,**E5=D5+D5*($I$6/12)**

This formula will add the **Starting Principle** (**D5**) to the interest earned (**D5*($I$6/12)**) for the period. We are dividing the yearly interest rate **$I$6 **by **12** as the regular deposit is made monthly. Copy the formula and apply it to the cells below.

- Then, in cell
**D6**(under the column Starting Principle), we used this formula,**D6=E5+C6**. This formula will add the new deposit to the amount at the end of the previous period. Then we copied down this formula for other cells in the column.

- Finally, drag down the
**Fill Handle**tool for other cells and your result will look like this.

## Calculate Compound Interest with Irregular Deposits

However, we can extend the previous template to calculate compound interest with irregular deposits. Just use your irregular deposits manually in the “New deposit” column like the image below.

**Read More:** Methods to Apply Continuous Compound Interest Formula in Excel

## Definition and Building Compound Interest Formula

Suppose you have some investable money in the amount of $10,000. You go to a bank and the bank says their savings rate is 6% per year. You deposited the money with the bank for the next 3 years as you felt safe with the bank and the interest rate is competitive.

So, your principal is: $10,000

The annual interest rate is: 6%

**🔶**** After 1 Year:**

After 1 year, you will receive interest on the amount: $10,000 x 6% = $10,000 x (6/100) = $600

So, after 1 year, your principal + interest will be:

= $10,000 + $600

= $10,000 + $10,000 x 6%; [replacing $600 with $10,000 x 6%]

= $10,000 (1+6%)

If you withdraw this interest ($600), then your principal at the beginning of the 2nd year will be $10,000. But if you don’t withdraw the interest, your principal at the beginning of the 2nd year will be $10,000 + $600 = $10,600 And this is where compounding starts. When you don’t withdraw the interest, the interest is added to your principal. The principal and earned interest work as your new principal for the next year. Your next year’s interest is calculated based on this new principle. Eventually, the yearly return from investments in the coming years gets bigger.

**🔶**** After 2 Years:**

At the beginning of year 2, your new principal is: $10,600

At the end of year 2, you will receive interest (on the basis of new principal) of the amount: of $10,600 x 6% = $636. Let’s make the compound interest rate formula from the above expression:

= $10,000(1+6%) + $10,600 x 6%; [replacing $10,600 with $10,000(1+6%) and $636 with $10,600 x 6%] = $10,000(1+6%) + $10,000(1+6%) x 6%; [again replacing $10,600 with $10,000(1+6%)]

= $10,000(1+6%)(1+6%)

= $10,000 x (1+6%)^2

So, we can make a generalized compound interest formula to calculate principal + interest:

**=p(1+r)^n**

Where,

**p**is the principal invested at the beginning of the annuity,**r**is the yearly interest rate (**APR**)- And
**n**is the number of years.

So, your principal + interest at the end of year 2 will be:

$10600 + $636 = $11,236

We can also reach this same amount using the above formula:

=p(1+r)^n

=$10,000 x (1+6%)^2

= $10,000 (1+0.06)^2

= $10,000 (1.06)^2

=$10,000 x 1.1236

= $11,236

**🔶**** After 3 Years:**

The new principal at the start of year 3 is: $11,236

But we don’t need this to calculate the principal + interest at the end of year 3. We can use the formula directly.

After 3 years, your principal + interest will be:

= $10,000 x (1+6%)^3

= $11,910.16

## Future Values of an Investment Using Compound Interest Formula

Initially, using the following compound interest formula, we can calculate future values on investment for any compounding frequency.

**A = P (1 + r/n)^(nt)**

Where,

**A**= Total amount after**nt**periods**P**= The amount invested at the beginning. It cannot be withdrawn or changed in the investment period.**r**= Annual Percentage Rate (APR)**n**= Number of times interest is compounded per year**t**= Total time in years

Check out the image below. I have shown 4 variations of the above formula.

Finally, you see that for the same investment of $10,000, we get the following results:

- For daily compounding: $18220.29
- For weekly compounding: $18214.89
- For Monthly compounding: $18193.97
- And for Quarterly compounding: $18140.18

So, if the number of compounding per year is higher, the return is also higher.

## Power of Compounding

Accordingly, the power of compounding is very significant. Let me show you the power of compounding in the investment world or with your savings.

Let’s assume you want to be a millionaire and that is in sleeping mode 😊

Warren Buffet (the living legend of the investment world) advises you to invest in a low-cost index fund, for example, Vanguard 500 Index Investor. And historically this fund has returned 8.33% annual return for the last 15 years (including the fall of 2008).

**Practice Section**

Here, we have provided a Practice section on each sheet on the right side for your practice. Please do it by yourself.

## Conclusion

Basically, understanding the concept of compounding can benefit you hugely. When making investment decisions, you should check out the long-term and consistent growth of your investment. It is far better to earn 15% per year than earn 100% a year and then vanish your investments. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Thanks for your patience in reading this article.

## Related Articles

- How to Calculate Compound Interest in Excel in Indian Rupees

**<< Go Back to Compound Interest in Excel | ****Excel for Finance**** | ****Learn Excel**

Kawser, excellent tutorial. You have a gift for clear explanation.

May I ask guidance setting up a “statement” for an 18-year-old grandson who has not yet learned to handle money.

I no longer want him to receive a birthday gift = his age x $100, instead to hold it in the “Bank of Grandpa” until he has demonstrated some maturity or has a valid need (rent, car, etc) for his money. (Yes, it’s occurred to me to not change the plan, to let him squander his money and then feel regret later, but I would feel more regret than him.)

I know the basics of Excel but can’t figure out how to set up a spreadsheet showing irregular deposits ($1900, then $2000, and so on, along with random gifts throughout the year) earning 2% annually with quarterly compounding.

The spreadsheet would be his “statement,” like the statement our bank gives us once a month, to let him see how much money he has with us.

Can you provide me a sample spreadsheet with the needed formulas, please?

Many thanks.

— Steve K.

At first, Create a dataset having Age in Column B, Fixed Amount in Column C and Principle At the Start of the period in Column D.

Then, insert the following formula in cell D5 and use the Fill Handle option to apply it to all cells of column D.

=B5*C5

Finally, insert the following formula in cell E6 and use the Fill Handle option to apply it to all cells of column E to get the desired result.

=E5*(1+0.02)+D6

The excel file is added here according to your wish.

Compound Interest Statement.xlsxThanks and happy helping.

Zehad

I was wondering if you ever do projects for people? I would like to have a couple of spreasheets built out and I’m curious what you would charge. My email is [email protected]

Thank you

Bo Thibaut

Dear

Bo Thibaut,We are willing to do any Excel-related projects. Kindly share your requirements through this Email: [email protected]

Regards

Shamima SultanaProject Manager | ExcelDemy