Excel Formula to Calculate Compound Interest with Regular Deposits

Get FREE Advanced Excel Exercises with Solutions!

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.

Quick view


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.

Dataset

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.

Compound Interest Excel Formula with Regular Deposits Using Excel FV Function

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

Calculating Compound Interest with Regular Deposits Using Excel Formula

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

Calculating Compound Interest with Regular Deposits Using Excel 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.

Calculate Compound Interest with Irregular Deposits

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

compound interest formula

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

Futures Values of an Investment Using Compound Interest 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).

Power of Compounding


Practice Section

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

Practice Section


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

4 Comments
  1. 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.


  2. 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.xlsx
    Thanks 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 Sultana
        Project Manager | ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo