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

**at a rate of**

*$100*

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

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

If in ** 5** years you invest

**at a rate of**

*$100***, the calculated future value is,**

*5%****(**

*100***+**

*1***)^**

*5%***=**

*5***.**

*127.6282*The sample dataset showcases a ** $100** investment for

**years at an annual interest rate of**

*5***.**

*5%*### Method 1- Applying the Compound Interest Formula in Excel

**Step 1:**

- Enter the following formula in
.*C7*

`=C4*(1+C5)^C6`

**Step 2:**

- Press
to see the future value in cell C7:*Enter*.*$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
and*C4*.*C5*

**Step 3:**

- Press
**Enter**to see the interest foryear in*1*:*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
and go to the*C7*tab.*Data* - In
, select*Data Tools*.*Data Validation*

**Step 2:**

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

**Step 3:**

- The dropdown with time intervals is created.

**Step 4:**

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

`=C4*(1+C5/C7)^(C6*C7)`

- The result is
.*12*

**Step 5:**

- Press
.*Enter*

**Step 6:**

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

**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])`

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

#### 4.1. Applying the FV Function

- Enter the following formula in
.*C7*

`=FV(C5,C6,0,-C4)`

is used as there is no periodic payment.*0*- 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

**it is**

*the FV function***).**

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

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

**Step 2:**

- Press
to see the result:*Enter*.*$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

**. Find the interest between**

*6%***and**

*15-Jun-2022***. The loan is to be repaid in**

*14-Jul-2022***12**years.

**Step 1:**

- Enter the following formula in
**C11**to get the result.

`=IPMT(C5/12,1,C7*C6,-C4)`

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

**Step 2:**

- Press
to see the result.*Enter*

## Determining the Compound Interest with Regular Deposits.

**Step 1:**

- Enter the following formula in
.**C12**

`=FV(C7,C9,-C10,-C4,C11)`

- In
, the payment type is selected as*C12*.*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**

Hi team,

Your website was the only one that explained the step by step function of the formulas. I am eternally grateful.

Dina

Dear

Dina,Thank you so much for your appreciation.

Regards

ExcelDemy