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

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

4 Easy Ways to Use Compound Interest Formula in Excel


Method 1- Applying the Compound Interest Formula in Excel

Step 1:

  • Enter the following formula in C7.
=C4*(1+C5)^C6

Applying Compound Interest Formula in Excel

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.
=$C$4*(1+$C$5)^B8
  • Use absolute values in C4 and C5.

Calculating Compound Interest Over Multiple Years in Excel

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.
=C4*(1+C5/C7)^(C6*C7)
  • The result is 12.

Determining Compound Interest with Time Contributions in Excel

Step 5:

  • Press Enter.

Step 6:

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

Determining Compound Interest with Daily Contributions in Excel

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. The syntax is:

FV(rate, nper, pmt, [pv], [type])
  • 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

  • Enter the following formula in C7.
=FV(C5,C6,0,-C4)
  • 0 is used as there is no periodic payment.
  • The negative sign is used as values are considered “money out” .

Applying FV Function to Use Compound Interest Formula in Excel

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
=FV(C5/C6,C7*C6,0,-C4)

FV Function with Compounded Period to Use Compound Interest Formula in Excel

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.
=FV(C5/C6,C7*C6,-C8,-C4,C9)

 FV Function with Periodic Payment to Use Compound Interest Formula in Excel

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

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.
=IPMT(C5/12,1,C7*C6,-C4)
  • 1 stands for the time interval between the two dates: 1 month.

Calculating Compound Interest Between Two Dates in Excel

Step 2:

  • Press Enter to see the result.


Determining the Compound Interest with Regular Deposits.

Step 1:

  • Enter the following formula in C12.
=FV(C7,C9,-C10,-C4,C11)
  • In C12, the payment type is selected as 1.

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

Determining Compound Interest Formula with Regular Deposits in Excel

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


<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Siam Hasan Khan
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

2 Comments
  1. Hi team,
    Your website was the only one that explained the step by step function of the formulas. I am eternally grateful.

    Dina

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo