# 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)`

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.

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

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

## Step 4 – Customizing Values

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

## Related Articles

<< Go Back to Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF