Compound Interest Formula and Excel Calculator

As you have learned from my article “Compound Interest – 8th wonder” that long term investments can be an effective strategy to be wealthy, and even small deposits can make a big difference over the period of time. The Excel compound interest formula explained further will help you get the savings strategy to work. Also we are going to make a common formula that calculates the future value (FV) of the investments at any of the compounding interest rates i.e. – daily, weekly, monthly, quarterly, or annually.

Simple Annual Compound Interest Formula

An easy way to calculate the amount earned with an annual compound interest rate =Amount * (1 + %).

In our below example, the formula is =A2*(1+$B2) where cell A2 is your initial investment (Rs. 1000) and cell B2 is the annual interest rate (7.5%) which a bank pays you. The $ sign with B2 cell means that we have fixed the reference to column B, because we are going to use same interest rate for each year.

Compound Interest Formula in Excel-1

Note that, 7.5% is actually 0.075 (1% is one part of a hundred, i.e. 0.01, so 7% is 0.075). You can verify the result returned by the formula by performing a simple calculation of 1000*(1+0.075) = 1000*1.075=1075.

Now, let’s calculate the balance after 2 years. For that just copy formula to cell D2 (as we are copying from C & have value in A2 cell it will take B2 as default).

Here make sure to have C2 cell for calculation. So you will have 1155.63 after 2 years.

Compound Interest Formula in Excel-2

To calculate how much amount you have in your bank account at the end of 5 years, simply copy the same formula to column G and you will get Rs.1,435.63.

Compound Interest Formula in Excel-3

Those of you who have some experience with Excel formulas have probably figured out that what the above formula actually does is multiplying the initial deposit of Rs.1,000 by 1.075 five times:

=1000*1.075*1.075*1.075*1.075*1.075= 1436

General compound interest formula

I have explained monthly & daily compound interest section in my previous post “Compound Interest – A Real 8th Wonder”, you can go through this link & understand what miracles can happen with this power of compounding. Now let’s construct compound interest formula in excel.

Universal compound interest formula in Excel (daily, weekly, monthly, quarterly, yearly compounding)

Let’s start creating our Excel compound interest calculator with entering the basic factors that determine the future value (FV) of your investment in an Excel sheet:

  • Initial investment (A3) – Rs.1,000
  • Annual interest rate (A4) – 7.5%
  • of compounding periods per year (A5) – 12
  • of years (A6) – 5

Now we need to make formula in excel. See below formula which we will insert in excel format.

Compound interest formula:

=Initial investment * (1 + Annual interest rate / Compounding periods per year) ^ (Years * Compounding periods per year)

When we enter these fields in excel, it looks like below picture.

=B3 * (1 + B4 /B5) ^ (B6 * B5)

Compound Interest Formula in Excel-4

 

You can notice here that balance is equal to that we have performed with monthly compound interest formula, and this result proves that we are right!

If you wish to know how much will be your investment worth at 7.5% annual interest rate which is compounded quarterly, then enter 4 (12 months divided by 3 is a quarter) in cell B5.

Compound Interest Formula in Excel-5

To calculate the future value of your investment with half-yearly compounding, enter 2 as the Compounding periods per year value. For weekly interest rates, enter 52(no. of weeks in a year).Going more in detail, if you wish to daily compounding, enter 365, and so on.

This is a universal compound interest calculator for Excel which you can use for any type of compounding.

You can either create one for you or you can download ready Compound Interest Calculator here.

Advanced compound interest calculator for Excel

There is another way by which you can create calculate compound interest with Future Value (FV) function that is available in all Microsoft Excel versions.

Excel’s FV function returns the future value of an investment based on factors similar to what we have just discussed. The syntax is bit different than what we just created.

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

In the function, the first 3 arguments are compulsory and the last 2 are optional.

  • rate – The interest rate per period.
  • nper – the total number of payment periods annually
  • pmt – additional payment that is made in each period, it is represented as a negative number. If the pmt argument is omitted, the pv parameter must be included.
  • pv– Optional.The present value of your investment, which is also a negative number. If the pv argument is omitted, it is assumed to be 0 (zero), in this case the pmt parameter must be specified.
  • type–Optional. It is specified when additional payments are due: 0 or omitted – at the end of the period, and 1 – at the beginning of the period.

Let’s use this Excel FV formula with the same values as in monthly compound interest examples and see what results we get.

As you may remember, we deposited Rs.1,000 for 5 years into a savings account at 7.5% annual interest rate compounded monthly, with no additional payments. So, our Excel compound interest formula goes as follows:

=FV(0.075/12, 5*12, ,-1000)

If you need some explanation of the parameters, here you go:

  • rate – 0.008/12 since you have the 8% annual interest rate compounded monthly.
  • nper – 5*12, i.e. 5 years * 12 months
  • pmt is left blank because we are not making any additional payments to our initial investment.
  • pv is -1000 since the syntax of the FV function requires using a negative number in this argument.

As demonstrated in the following screenshot, the FV formula returns the same result as the compound interest calculator that we created in the previous example.

Compound Interest Formula in Excel-6

 

Alternately, you can replace the values in the FV function with cell references, after that your FV formula will double-check the result returned by our Excel compound interest calculator. Here is how to do it.

Compound Interest Formula in Excel-7

 

All in One Compound Interest Calculator

To make your Excel compound interest calculator even more powerful, you can extend it with the Additional Payments option and modify the compound interest formula accordingly:

=FV(B4/B5, B6*B5, -B7, -B3, B8)

Where:

  • B3 – principle investment
  • B4 – an annual interest rate
  • B5 – the number of compounding periods per year
  • B6 – the number of years to save
  • B7 – additional contributions (optional)
  • B8 – additional contributions type. Remember that you enter 1 if you deposit an additional amount at the beginning of the compounding period, 0 or omitted if additional payments are made at the end of the period.

Compound Interest formula in Excel-8

 

That’s it from my side. If you wish you can download this advanced all in one compound interest excel calculator here.

Do let me know if you have comments & share the post if you liked this.

 

8 Replies

Trackback  •  Comments RSS

  1. Krupal says:

    Thanks for the great post and excel calculator..

  2. Cready Trim says:

    Hiya very cool site!! Excellent ..
    Superb .. I will bookmark your site and take the feeds additionally.
    I am glad to find a lot of helpful information right here in the
    publish, we’d like develop extra techniques on this regard, thank
    you for sharing. . . . . .

  3. Jeus says:

    Very elaborative article. Learnt few more new things..thanks for the ready excel calculator. Keep up the good work.

  4. John Kuhler says:

    I have gone through your both posts regarding compound interest & let me tell you those are phenomenal..I would like to see many more posts from you. Thanks.

  5. Venkatesh R says:

    I needed to thank you for this fantastic read!!

    I definitely appreciating every small touch of it I
    have you bookmarked to have a look at new stuff you post.

Post a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Top