Pinterest Pixel

How to Create Compound Interest Calculator in Excel – 2 Easy Methods

Compound interest is a concept in finance that refers to the interest on a loan or deposit... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

How to Create Compound Interest Calculator in Excel - 2 Easy Methods | MyExcelOnline How to Create Compound Interest Calculator in Excel - 2 Easy Methods | MyExcelOnline

Compound interest is a concept in finance that refers to the interest on a loan or deposit that is calculated based on both the initial principal amount and the accumulated interest from previous periods. In other words, it’s interest on interest.

There are two methods that can be used to create a compound interest calculator in Excel

Let us look at two methods in detail.

Download the Excel Workbook below to follow along and understand How to Create a Compound Interest Calculator in Excel – download excel workbookCompound-Interest-Calculator-in-Excel.xlsx

 

Method 1 – Compound Interest Formula

Suppose you wish to invest $10,000 in your savings account for 3 years. Your account offers a return of 6.5%, subject to semi-annual compounding. You want to know the future value of your investment using the compound interest calculator in Excel.

The formula for this approach will be –

P’ = P*(1+R/N)^NT

where.

  • P’ is the future value of the investment, including interest.
  • P is the principal amount.
  • R is the annual interest rate (in decimal form).
  • N is the number of times that interest is compounded per unit t.
  • T is the time the money is invested for, in years.
See also  SUBSTITUTE Function - Replace Characters in Excel!

Follow the steps below to learn how to use the compound interest calculator in Excel

STEP 1: Enter the initial principal amount i.e. P. Here, it is 10000.

=10000

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 2: Enter the multiplication sign(*).

=10000*

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 3: Enter 1 + 0.065/2 within brackets.

=10000*(1+0.065/2)

where,

  • o.o65 is the annual interest rate in decimal form i.e. R
  • 2 is the number of times the interest is compounded i.e. N

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 4: Enter the ^ sign.

=10000*(1+0.065/2)^

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 5: Now, enter the 2*3 within brackets.

=10000*(1+0.065/2)^(2*3)

where

  • 2 is the number of times the interest is compounded i.e. N
  • 3 is the time the money is invested for i.e. T years.

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

The resultant amount will be the future value of the amount invested, compounded semi-annually.

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

 

Method 2 – FV Formula

The FV function in Microsoft Excel assists in calculating the future worth of a company’s investments, assuming periodic, consistent payments with a constant interest rate.  Let’s use Microsoft Excel’s FV Function that will help us to create a compound interest calculator in Excel.

See also  Calculate How Many Days Until December in Excel

The syntax for the FV function is –

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

  • Rate: The interest rate per period. 
  • Nper: The total number of payment periods in an annuity.
  • Pmt: The payment made each period. It cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument. 
  • Pv: The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type: This is an optional argument.
  • Type: The number 0 or 1 indicates when payments are due. 0 if the amount is being added to the investment at the end of the period or 1 if the amount is being added to the investment at the beginning of the period. This is an optional argument.

Follow the steps below to use the FV function to calculate compound interest –

See also  Advanced SUMPRODUCT Function: Maximum Sales

STEP 1: Enter the FV function.

=FV

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 2: Enter the first argument i.e. rate. Here, it is 0.0065/2 i.e.

=FV(0.065/2

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 3: Enter the second argument i.e. nper. Here, it is 3*2 i.e. 6.

=FV(0.065/2,3*2

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 4: Enter the third argument i.e. pmt. Here, it is 0 as there were no deposits made in between the period.

=FV(0.065/2,3*2,0

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

STEP 5: Enter the fourth argument i.e. pv. Here, it is -10,000.

=FV(0.065/2,3*2,0,-10000)

You will have to use a negative sign as it is a cash outflow.

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

The resultant amount is the future value of your investment.

How to Create Compound Interest Calculator in Excel - 2 Easy Methods

Click here to learn 5 ways of Using Excel as a Time Value of Money calculator.

 

Conclusion

In conclusion, understanding and utilizing compound interest calculator is crucial for maximizing returns on investments over time. By employing either the Compound Interest Formula or the FV Formula in Excel, investors can project the future value of their investments more accurately.

These tools empower individuals to make informed financial decisions and harness the power of compound interest for long-term financial growth.

See also  Calculate Total Sales in Excel With An Array Formula

Click here to learn more about Finance Formulas in Excel.

If you like this Excel tip, please share it
How to Create Compound Interest Calculator in Excel - 2 Easy Methods | MyExcelOnline How to Create Compound Interest Calculator in Excel - 2 Easy Methods | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...