How to Create a Quarterly Compound Interest Calculator in Excel

Understanding Compound Interest

Compound interest allows you to earn interest on both the initial investment (the principal) and any previously earned interest.

The generic formula for compound interest is:

FV = PV(1+r)n= PV(1+r)n

Where:

(FV) is the future value
(PV) is the present value (initial investment)
(r) is the interest rate per period
(n) is the number of compounding periods

Using Excel’s FV Function

Excel has a built-in function called FV that calculates the future value of an investment using a constant interest rate.

The syntax of the FV function is: FV(rate, nper, pmt, [pv], [type]).

rate: The interest rate per period
nper: The total number of periods
pmt: Additional payments (usually 0 for compound interest)
pv: The present value (negative for investments)
type: Optional; 0 for end-of-period payments, 1 for beginning-of-period payments

Example Calculation

Let’s assume a $5,000 investment for ten years at a 5% annual interest rate, compounded monthly.

We can use the following formula:

=FV(C5/C6,C7*C6,0,-C8)

How to Compute Compound Interest in Excel?

Here, C5 represents the interest rate, C6 is the compounding periods per year, C7 is the terms in the year, and C8 is the present value of the investment.

Quarterly Compound Interest Formula

To calculate quarterly compound interest, we compute interest four times a year. Each quarter’s interest is added to the principal for the upcoming quarter.

The formula is:

=Principal Amount*((1+Annual Interest Rate/4)^(Total Years of Investment*4))

Creating the Calculator in Excel:

Step 1 – Dataset Introduction

Consider a dataset with a principal amount of $10,000, an annual interest rate of 5%, and a total investment period of 10 years.

Create Quarterly Compound Interest Calculator in Excel

Step 2 – Inserting the Formula

In cell C9 (your resulting cell), enter the formula:

=C5*((1+C6/4)^(C7*4))

Here:

- C5: Principal amount
- C6: Annual interest rate
- C7: Total years of investment

quarterly compound interest calculator excel

Press Enter.

Step 3 – Final Output

Press Enter, and cell C9 will display the future amount (quarterly compound interest), which in this case is $12,820.

quarterly compound interest calculator excel

Step 4 – Customizing Values

Modify the input values (e.g., change the total years of investment) to see how the result changes.

Download Practice Workbook

You can download the practice workbook from here:

Quarterly Compound Interest Calculator.xlsx

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

Bob Martray Jul 22, 2022 at 9:56 PM

I am confused at your notation for quarterly compounding! FV= p*(1+ APR/4)*(#years*4)
FV = C5*(1+C6/4)*(C7*4)

(# Years * 4) or (C7*4) should be an EXPONENT,NOT a factor! FV=p*(1+APR/4)^(C7*4)
FV=C5*(1+C6/4)^(C7*4)

This could cause much confusion!
Bob Martray
Math Instructor at NVCC
571-276-8325
- Reply
  Sabrina Ayon Aug 14, 2022 at 1:52 PM
  
  Hello, BOB MARTRAY!
  Thanks for noticing.
  The formula is now updated!
Terry Sep 10, 2024 at 6:18 AM

T want to show the total cost DIFFERENCES (comparison) of a $500,000 mortgage at 5% amortized 20years
On a print out or spread sheet between simple interest and compound interest (annual compounding) how can I get such a print out or how can achieve this do this?
- Reply
  Shamima Sultana Sep 10, 2024 at 1:02 PM
  
  Hello Terry,
  
  To compare the total cost of a $500,000 mortgage with simple and compound interest (annual compounding) at 5% over 20 years, you can create a spreadsheet with two sections:
  
  Simple Interest Formula: I=P*r*t
  Use the formula =500000 * 5% * 20 to calculate the total interest for simple interest.
  
  Compound Interest Formula: =500000 * (1 + 5%)^20 – 500000
  In Excel: =FV(B2, B3, 0, -B1) – B1
  
  Now calculate the differences and display them in a printout or spreadsheet.
  
  Downlaod Excel File:
  Mortgage-Interest-Comparison.xlsx
  
  Regards
  ExcelDemy