## What Is Compound Interest In Excel Formula?

The

Compound Interest formula in Excelgives the interest amount on savings estimated on the initial principal and accumulated interest from the previous periods.

Users can use the **Excel** **Compound Interest formula** to evaluate the compound interest non-annually, and the appreciation and depreciation in commodity prices. And it also helps determine the inflation in profit and loss.

For example, the table below contains the inputs required for the monthly compound interest calculation.

If the data format in the target cell **C8** is set as **Currency, t**hen, we can apply the **monthly Compound Interest formula in Excel** cell **C8**, and achieve the final amount earned.

The compounding frequency each year is 12. And the formula divides the yearly interest rate by 12 and multiplies the **Term** by 12, as the interest gets compounded every month in a year.

And the result in cell **C8** is the overall accumulated sum, which includes the initial investment and the compound interest.

Thus, based on the above **monthly Compound Interest formula **calculation, the interest earned in the first month will be **$20,000*(10%/12)**, which is **$166.66**. And in the second month, it will be **($20,000+$166.66)*(10%/12)**, which is **$168.055**, and so on every month for two years, arriving at a total of **$24,407.82**.

##### Table of contents

###### Key Takeaways

- The
**Compound Interest formula in Excel**determines the interest on the initial investment and the interest accrued over the previous periods. - The compounding frequency each year for the interest compounded semi-annually, quarterly, monthly, and daily is 2, 4, 12, and 365, respectively.
- Users can use the compound interest expression to evaluate the returns earned on the money they invested initially and after each compounding period.
- We can apply the compound interest expression in a worksheet using the mathematical expression, the calculation table, and Excel inbuilt functions
**FVSCHEDULE**and**FV**.

### How To Calculate Compound Interest In Excel Formula?

We can **Calculate Compound Interest In Excel Formula** in the following ways, namely,

**Using Mathematical Compound Interest Excel Formula.****Using the Compound Interest Calculation Table in Excel.****Compound Interest Using FVSCHEDULE Excel Formula.****Compound Interest Using the FV Excel Formula.**

We will consider specific examples for each of the above-mentioned ways to determine **compound interest in Excel**.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#### Example #1 – Using Mathematical Compound Interest Excel Formula

The mathematical expression to determine the compound interest is as follows:

where,

**P**: The principal amount or the initial investment**r**: The yearly interest rate**n**: The compounding frequency each year**t**: The total period for which one applies the interest.

And the **Total Accumulated Amount **includes the initial investment and the compound interest.

For example, the table below contains the inputs required for the daily compound interest calculation.

The steps to apply the **daily Compound Interest formula in Excel** cell D8 and achieve the final amount earned are,

**Step 1:**Select cell**D8**, and enter the formula.*=D3*((1+D4/D6)^(D5*D6))*

**Step 2:**Press**Enter**to view the final accumulated sum.

The compounding frequency each year is 365. And the formula divides the yearly interest rate by 365 and multiplies the **Term** by 365, as the interest gets compounded daily or 365 times per year.

And the result in cell D8 is the overall accrued sum, which includes the principal and the compound interest.

Thus, based on the above **daily Compound Interest formula **calculation, the interest earned on the first day will be **$5,000*(6%/365)**, which is **$0.82**. And on the second day, it will be **($5,000+$0.82)*(6%/365)**, which is **$0.82205**, and so on every day for five years, making a total of **$6,749.13**.

Furthermore, the following table shows the standard compounding frequency per year data used in the compound interest calculations and their interpretation.

We can refer to the above table when evaluating the non-annual compound interest values.

#### Example #2 – Using Compound Interest Calculation Table in Excel

Using a calculation table, we shall see how to apply the** quarterly Compound Interest formula in Excel**.

For example, the table below shows the initial investment in period 0 and the yearly interest rate in cells **E3 **and** H2**, respectively. We will determine the amount earned at each quarter end and display the output in cells **E4:E7**.

The steps to apply the **quarterly Compound Interest formula in Excel** using a calculation table are,

**Step 1:**Select the target cell**E4**, enter the formula, and press*=E3*(1+$H$2/4)***Enter**.

**Step 2:**Using the fill handle in excel, implement the formula in the remaining target cells**E5:E7**.

The formulas in cells **E4: E7** return the investment’s future value after each quarter, from Q1 to Q4. And we divide the yearly interest rate by 4 in the above formula as we require the interest, compounded quarterly.

Furthermore, the total amount earned at the fourth quarter end is **$27,060.80**, which includes the principal sum and the compound interest.

#### Example #3 – Compound Interest Using FVSCHEDULE Excel Formula

We shall see the method to execute the **half yearly Compound Interest formula in Excel **using the inbuilt **FVSCHEDULE** function.

For example, the table below shows the inputs required to achieve the interest compounded semi-annual in cell **B9**.

The steps to implement the **half yearly Compound Interest formula in Excel **using the inbuilt function **FVSCHEDULE** in the target cell.

**Step 1:**Select cell**B8**, enter the formulato determine the interest rate for the interest compounded semi-annually, and press*=B4/B6***Enter**.

**Step 2:**Select cell**B9**, enter the formula*=FVSCHEDULE(B3,{0.035,0.035,0.035,0.035,0.035,0.035})***Enter**.

The **FVSCHEDULE() **accepts,

- the principal sum of
**$8,000**as the first argument,**principal**, which is the present value. - And its second argument is the
**schedule**, which is the array of interest rates we must apply.

In the above example, as the compounding frequency in each year is 2, and the total terms are 3, the array range must include 6 interest values. And the interest rate to use is **0.035**, which we calculated in cell **B8**.

Thus, based on the input argument values, the **FVSCHEDULE()** returns the accrued amount as **$9834.04**. And it is the initial investment’s future value, with a series of compound interests.

#### Example #4 – Compound Interest Using the FV Excel Formula

Let us see how to determine the quarterly compound interest using the **FV function in excel**.

The table below contains the inputs required to obtain the interest compounded quarterly.

The steps to use the **FV()** in the target cell to determine the accumulated sum at the sixth year’s last quarter end are as follows:

**Step 1:**Select the target cell**B8**, enter the**FV()**formula, and press*=FV(B4/B6,B5*B6,0,-B3)***Enter**.

[**Special Note:** The **FV()** arguments are **rate**, **nper**, **pmt**, **pv**, and **type**.

- The
**rate**argument indicates the interest rate per period in a series of regular payments. So, we pass the argument value as the yearly interest rate divided by compounding frequency per year. And the argument**nper**is the total number of periods of regular payments, which is the product of the Term and compounding frequency per year. - On the other hand, we must supply
**pmt**or**pv**. And as we do not add any additional cost to the initial investment in the investment period, we will set the**pmt**argument value, which is the payment in each period, as**0**. - So, as we ignore the argument
**pmt**, we must provide the argument**pv**value, which is the present value or the principal sum. And as theamount is an investment, it will be a negative value. - And the last argument,
**type**, is optional, which we shall ignore, as the example considers the payment is due at every period end.]

Thus, based on the supplied input values, the **FV()** returns the accumulated sum at the sixth year’s end, **$51,173**. And it is an amount that includes the initial investment and the compound interest.

### Important Things To Note

- The input values used in the
**Compound Interest formula in Excel**must be of the correct data types. - We will get the
**#VALUE!**Error if the**FVSCHEDULE**and**FV**functions’ argument values are non-numeric. - Ensure the supplied present value to the
**FV()**is negative, as it is an outgoing amount.

### Frequently Asked Questions (FAQs)

**1. Can Excel calculate compound interest?**

Excel can calculate compound interest. The following methods help us achieve the increased amount, which includes the initial investment and the compound interest.**• **Using mathematical compound interest expression**• **Using the compound interest calculation table**• **Using the **FVSCHEDULE** function**• **Using the **FV()**

**2. What is the difference between simple interest and Compound Interest formula in Excel?**

The difference between simple interest and **Compound Interest formula in Excel** is that the simple interest formula calculates the interest on the principal amount of a loan. On the other hand, the compound interest expression determines the interest on the principal amount and the accrued interest in the previous periods.

For example, the tables below contain the inputs required to determine the simple and semi-annual compound interests in cells **B8 **and** E8.**

The steps to perform the required calculations are,**• Step 1:** Select the target cell **B8**, enter the simple interest formula ** =B3*B4*B5**, and press

**Enter**.

The formula multiplies the principal, yearly interest rate, and the time to return the simple interest accrued when the loan period ends.

**• Step 2:**Select the target cell

**E8**, enter the formula

**, and press**

*=E3*((1+E4/E6)^(E5*E6))***Enter**.

The above formula returns the initial investment’s future value plus the compound interest.

**3. Why is Compound Interest formula in Excel not working?**

The **Compound Interest formula in Excel** might not work for the following reasons:**• **The input values supplied to the compound interest expression are of the incorrect data types.**• **The compounding frequency per year does not match the non-annual compound interest calculation requirement.**• **The supplied arguments to the **FVSCHEDULE() **or **FV()** are non-numeric or blank cells.**• **The initial investment supplied as the **pv** argument value to the **FV()** is not negative.

### Download Template

This article must help understand the **Compound Interest formula in Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Compound Interest Formula In Excel. Here we explain the top 4 method to calculate it along with examples & downloadable template. You can learn more from the following articles –

## Leave a Reply