Compound interest is the interest that’s calculated both on the initial principal of a deposit or loan and on all previously accumulated interest. Professor Albert Einstein once said, “Compounding interest is the most powerful force in the universe.” It is doubtful whether Einstein really said that or not, but it doesn’t take away from the importance of the message. Compounding is a very powerful force that either works against you (borrowing) or for you (investing). In this article, I will show you how to use the compound interest formula in Excel. Let’s get started with the basics of compound interest.

## Knowing Simple Interest Formula

You can use the simple interest formula to determine the amount of interest that will be charged on a sum of money at a certain rate and for a specified amount of time. The formula for determining simple interest is,

*I = Pnr*

Where,

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

For example, if in ** 5** years you invest

**at a rate of**

*$100***. Then the calculated simple interest will be,**

*5%***that is**

*I = $100*5/100*5,***.**

*$25*## Knowing Compound Interest Formula

For calculating the future value of any investment earning at a constant rate of interest the following formula can be used. Which is,

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

Where,

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

For example, if in ** 5** years you invest

**at a rate of**

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

*5%****(**

*100***+**

*1***)^**

*5%***=**

*5***.**

*127.6282*## How to Use Compound Interest Formula in Excel: 4 Easy Ways

In this article, I will show you four easy ways to use the compound interest formula in Excel. In the first method, I will apply the basic formula of compound interest. Then, in the second procedure, I will calculate the compound interest for multiple years. Thirdly, I will consider different time contributions for calculating compound interest. Lastly, I will apply** the FV function **of Excel to calculate compound interest for different conditions.

For proceeding with our methods, I will use the following sample data set. Here, I have a table that states a ** $100** investment for

**years at an annual interest rate of**

*5***.**

*5%*### 1. Applying Compound Interest Formula in Excel

In the first procedure, I will apply the basic formula of compound interest in Excel. Here we are going to calculate the future value of some ventures using the formula of compound interest in Excel. We can use the formula directly to calculate the future value in Excel. For that, follow the below-given steps.

**Step 1:**

- Firstly, write the following formula in cell
.*C7*

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

**Step 2:**

- Secondly, press
to see the future value in cell C7, which is*Enter*.*$127.63*

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

### 2. Calculating Compound Interest Over Multiple Years

The future value of some amount of investment for a number of years can be shown using the same formula. Here the investment goes as the years add up. To learn more about this method, go through the following steps.

**Step 1:**

- Firstly, I will use the following data set for the following procedure.
- Here, I will calculate compound interest for each year separately.

**Step 2:**

- Secondly, to do that, in cell C8, use the following formula.

`=$C$4*(1+$C$5)^B8`

- Here, I will absolute the values of cells
and*C4*to use them in the lower columns also.*C5*

**Step 3:**

- Afterward, press
**Enter**to see the desired interest foryear in cell*1*, that is,*C8*.*$105* - Then, use the
feature to drag the formula to the lower cells of that column.*AutoFill*

**Read More:** Formula for Monthly Compound Interest in Excel

### 3. Determining Compound Interest with Time Contributions in Excel

In the third procedure, I will determine compound interest with different time contributions in Excel. From this method, you can calculate daily compound interest in Excel, as well as weekly, monthly, or semiannually. So, follow the below-given steps to learn the procedure.

**Step 1:**

- First of all, I will create a dropdown to collect all the time contributions into one cell.
- For that, select cell
and then go to the*C7*tab of the ribbon.*Data* - From there, in the
group, select*Data Tools*.*Data Validation*

**Step 2:**

- Secondly, the
dialog box will appear.*Data Validation* - From there, under the
label, choose*Allow*.*List* - Then, in the
type box, select the cell range*Source*from the*C9:C13*worksheet that has the time interval of all the periods.*Data Set* - Lastly, press
.*OK*

**Step 3:**

- Thirdly, after performing the previous two steps, you will see the dropdown where all the time intervals are present.

**Step 4:**

- Fourthly, insert the following formula in cell
to determine the compound interest for monthly contributions.*C8*

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

- For monthly contributions, the value of cell
will be*C7*.*12*

**Step 5:**

- Finally, get the desired result by pressing
.*Enter*

**Step 6:**

- Furthermore, by changing the cell value of
to*C7*, you can determine the compound interest with daily contributions.*365* - Hence, you can change
**C7**according to your preference.

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

### 4. Applying Excel FV Function for Calculating Compound Interest

It’s good practice to use Excel’s *FV*** function**, which calculates the future value based on different factors. The syntax for this

**function is,**

*FV*

`FV(rate, nper, pmt, [pv], [type])`

Where,

-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 FV Function

In this section, I will show you the application of** the FV function** to calculate compound interest in Excel. Here, the application will be a simple one with no extra conditions. To apply this function, see the following steps.

**Step 1:**

- First of all, write the following formula for
*the FV*in cell*function*.*C7*

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

- Here,
is used as there is no periodic payment.*0* - Also, I used the negative sign as Excel treats this as “money out” for your investment.

**Step 2:**

- Finally, after pressing
, you will see the desired compound interest of*Enter*.*$201.22*

#### 4.2. FV Function with Compounded Period

For calculating the future value to be compounded with a monthly, quarterly, weekly, or daily contribution, you need to divide the annual interest rate (for ** the FV function** it is rate) with the contribution and multiply the contribution by the number of years (for

**it is**

*the FV function***).**

*nper*Let’s say we want to calculate the future value of the same factors. The only difference will be the compounding period. Here, the compounded periods will be the total number of weeks per year, which is ** 52**. The steps below show how it is done.

**Step 1:**

- In the beginning, insert the following formula in cell
with compounding periods.*C8*

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

**Step 2:**

- Secondly, you will get the desired result by pressing
.*Enter*

#### 4.3. FV Function with Periodic Payment

Let’s calculate the compound interest with more options where additional contributions (** pmet & type**) are added. We will calculate the same example where an additional contribution of

**is added. For doing this, see the following steps.**

*$500***Step 1:**

- Firstly, enter the following formula containing periodic payment in cell
.*C10*

`=FV(C5/C6,C7*C6,-C8,-C4,C9)`

**Step 2:**

- Finally, after pressing
, you will get the desired result for this condition, which is*Enter*.*$467,346.31*

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

## Calculating Compound Interest Between Two Dates in Excel

Sometimes, users have to calculate compound interest that is between two dates. Doing this calculation manually can be time-consuming and tiresome. By using** the IPMT function** of Excel, we can solve it easily. Suppose, We have to calculate the interest on a

**loan amount with monthly compounding and an interest rate of**

*$5000***. We are to find the interest between**

*6%***and**

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

*14-Jul-2022***12**years. To do this type of calculation, see the following result.

**Step 1:**

- Firstly, type the following formula of
in cell*the IPMT function***C11**to get the result.

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

- Here,
stands for the time interval between the two aforementioned dates, which is*1*month.*1*

**Step 2:**

- Finally, by pressing
you get the result for this procedure.*Enter,*

## Determining Compound Interest with Regular Deposits

In this section, I will show you how to calculate compound interest using the Excel formula with regular deposits. To do this, we will use the Excel ** FV** function. To learn more about this procedure, see the following steps.

**Step 1:**

- At the beginning, insert the following formula of the
function in cell**FV**with all the necessary arguments.**C12**

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

- Here, in cell
, I select the payment type as*C12*.*1* - Also, we will also pay
ten times a year, which is a regular deposit.*$2,500*

**Step 2:**

- Finally, to get the desired result, press
after inserting the formula.*Enter*

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

**Download Practice Workbook**

You can download the free Excel workbook here and practice on your own.

## Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to use the compound interest formula in Excel. Please share any further queries or recommendations with us in the comments section below.

**How to Use Compound Interest Formula in Excel: Knowledge Hub**

- How to Calculate Compound Interest in Excel in Indian Rupees
- 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