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

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

STEP 2: Enter the multiplication sign(*).

=10000*

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

STEP 4: Enter the ^ sign.

=10000*(1+0.065/2)^

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.

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

 

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.

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 –

STEP 1: Enter the FV function.

=FV

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

=FV(0.065/2

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

=FV(0.065/2,3*2

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

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.

The resultant amount is the future value of your investment.

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.

Click here to learn more about Finance Formulas in Excel.