How to Use a Compound Interest Formula in Excel – 4 Methods

A Simple Interest Formula

Use this interest formula to calculate the amount of interest:

I = Pnr

• I = calculated simple interest
• P = Initial Principal
• n = number of periods
• r = annual interest rate

If in 5 years you invest $100 at a rate of 5%, the calculated simple interest will be: I = $100*5/100*5 = $25.

The Compound Interest Formula

To calculate the future value of any investment at a constant rate of interest use following formula:

Future Value = P*(1+r)^n

• P – the initial amount invested
• r – annual interest rate (as a decimal or a percentage)
• n – number of periods over which the investment is made

If in 5 years you invest $100 at a rate of 5%, the calculated future value is, 100*(1+5%)^5 = 127.6282.

The sample dataset showcases a $100 investment for 5 years at an annual interest rate of 5%.

Method 1- Applying the Compound Interest Formula in Excel

Step 1:

• Enter the following formula in C7.

Step 2:

• Press Enter to see the future value in cell C7:  $127.63.

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

Method 2 – Calculating the Compound Interest Over Multiple Years

Step 1:

Use the following dataset to calculate the compound interest for each year:

Step 2:

• In C8, use the following formula.

• Use absolute values in C4 and C5.

Step 3:

• Press Enter to see the interest for 1 year in C8: $105.
• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: Formula for Monthly Compound Interest in Excel

Method 3 – Determining the Compound Interest with Time Contributions in Excel

Calculate the compound interest with different time contributions.

You can calculate the daily, weekly, monthly, or semiannual compound interest.

Step 1:

• Create a dropdown list to collect get the time contributions.
• Select C7 and go to the Data tab.
• In Data Tools, select Data Validation.

Step 2:

• In the Data Validation dialog box, in Allow, choose List.
• In Source, select C9:C13.
• Click OK.

Step 3:

• The dropdown with time intervals is created.

Step 4:

• Enter the following formula in C8 to determine the compound interest for monthly contributions.

• The result is 12.

Step 5:

• Press Enter.

Step 6:

• Change the value in C7 to 365 to calculate the compound interest with daily contributions.

Read More: How to Calculate Future Value When CAGR Is Known in Excel

Method 4 – Applying the Excel FV Function to Calculate the Compound Interest

Use Excel’s FV function. It calculates the future value based on different factors. The syntax is:

• rate -the interest rate
• nper -number of periods for the investment
• pmt -the periodic payment
• pv -the present value/initial investment
• type is optional.

4.1. Applying the FV Function

Use the FV function.

• Enter the following formula in C7.

• 0 is used as there is no periodic payment.
• The negative sign is used as values are considered “money out” .

Step 2:

• Press Enter.

The compound interest is $201.22.

4.2. Using the FV Function with a Compounded Period

To calculate the future value with monthly, quarterly, weekly, or daily compounded periods, you need to divide the annual interest rate (for the FV function it is rate) by the contribution and multiply it by the number of years (for the FV function it is nper).

The compounded periods will be the total number of weeks per year: 52.

Step 1:

• Enter the following formula in C8

Step 2:

• Press Enter.

4.3.  FV Function with a Periodic Payment

Calculate the compound interest with an additional contribution of $500 (pmet & type).

Step 1:

• Enter the following formula in C10.

Step 2:

• Press Enter to see the result:  $467,346.31.

Read More: Excel Formula to Calculate Average Annual Compound Growth Rate

Calculating the Compound Interest Between Two Dates in Excel

Use the IPMT function.

Calculate the interest on a $5000 loan amount with monthly compounding and an interest rate of 6%. Find the interest between 15-Jun-2022 and 14-Jul-2022. The loan is to be repaid in 12 years.

Step 1:

• Enter the following formula in C11 to get the result.

• 1 stands for the time interval between the two dates: 1 month.

Step 2:

• Press Enter to see the result.

Determining the Compound Interest with Regular Deposits

Calculate the compound interest using an Excel formula with regular deposits.

Step 1:

• Enter the following formula in C12.

• In C12, the payment type is selected as 1.

 $2,500 are paid ten times a year as a regular deposit.

Step 2:

• Press Enter.

Read More: How to Calculate Compound Interest for Recurring Deposit in Excel

Download Practice Workbook

Download the free Excel workbook.

How to Use Compound Interest Formula in Excel: Knowledge Hub

• CAGR Formula in Excel: With Calculator and 7 Examples
• How to Calculate End Value from CAGR in Excel
• How to Calculate CAGR with Negative Number in Excel
• How to Calculate 3-Year CAGR with Formula in Excel
• How to Calculate 5 Year CAGR Using Excel Formula
• How to Create CAGR Graph in Excel

<< Go Back to Excel for Finance | Learn Excel