Compound Interest Formula in Excel: Calculator with All Criteria

In Microsoft Excel, there are built-in functions to calculate future investment values and compound interest. We can also make our own formula by using other functions or with simple algebraic operators to determine the compound interest with ease. In this article, all suitable and quick methods have been described with steps to find out your compound interest by using a formula or some other functions.


Download Practice Workbook

You can download our Excel workbook that we’ve used to prepare this article.


What is Compound Interest?

Compound interest is the addition of interest to the principal sum of a loan or deposit, or in other words, interest on interest. It is the result of reinvesting interest, rather than paying it out, so that interest in the next period is then earned on the principal sum plus previously accumulated interest.

Source: https://en.wikipedia.org/wiki/Compound_interest

For example, you have deposited $5000 in a bank for 5 years and the bank will give you an interest amount after the 1st year based on your principal investment. The interest rate is 10%. So, after 1 year you’ll get- 5000*1*0.10 = $500 as the interest amount. This interest value will be added to your primary investment which means you’re getting $5000+$500 = $5500 after 1 year.

Now, the bank will provide you with another 10% interest on $5500. So, after one more year, you’ll get 5500*1*0.1 = $550 as interest. It means you’re getting a total of $5500+$550 = $6050 after 2 years from the initial investment.

In this way, you’ll get a total of $8052.55 after 5 years from the initial investment in the bank. Here, the difference between the final value and the initial investment is $8052.55-$5000 = $3052.55. And this amount of interest value here is called the ‘Compound Interest’.


Generic Formula of the Compound Interest

We have a mathematical formula of the Compound Interest and the Future Value. The generic formula for the Future Value is:

FV=P(1+R)n

Where,

FV = Future Value

P = Primary Investment

R = Rate of Interest

n = Length of Years of Investment

And if we want to know the Compound Interest, then we have to simply subtract the Principal Investment from the calculated Future Value. So, the Compound Interest (CI) formula should look like the following:

CI=P(1+R)n-P


4 Suitable Approaches to Calculate Compound Interest with Formula in Excel

1. Calculate Compound Interest with Simple Mathematical Formula

At first, we’ll build up the generic formula in an Excel spreadsheet to determine the future value of the primary investment as well as the compound interest.

1.1 Compounding Period as Year

Let’s assume, our initial investment is $5000 for a term of 5 years where we’re supposed to get 10% compound interest annually on that investment value. In the following table, we’ll generate two formulas in Cells C7 and C8 to obtain the Future Value and the Compound Interest respectively.

Calculate Compound Interest with Simple Mathematical Formula

📌 Step 1:

To make a generic formula to calculate the Future Investment Value in Cell C7, we have to type the following formula:

=C4*(1+C5)^C6

Calculate Compound Interest with Simple Mathematical Formula

📌 Step 2:

After pressing Enter, we’ll get the Future Investment Value for 5 years in Cell C7. Now to find out the Compound Interest based on the following data, we have to subtract the value in Cell C4 from the value in Cell C7. So, in the output Cell C8, the final formula will be:

=C7-C4 

Calculate Compound Interest with Simple Mathematical Formula

And we’ll get the value of Compound Interest at once.

Now if you change any of the data in Cells C4, C5, or C6, the Future Investment Value as well as the Total Interest amounts will be updated automatically. So, you can use the downloadable Excel file in this article as a calculator indeed as we’ve prepared all the datasets with embedded formulas in the output cells.


1.2 Compounding Period as Month(s)

Now let’s assume, the rate of interest will be applied to the initial investment after every three months interval. Or we can say the interest will be compounded quarterly. So, we’ll have 4 quarter periods in a year for compounding the interest.

In this case, the rate of interest has to be divided by 4. And also we have to multiply the length of time by 4. So, the required formula to determine the Future Investment Value in Cell C8 will be now:

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

Calculate Compound Interest with Simple Mathematical Formula

The formula will return the following output in Cell C8. And then we can obtain the Total Interest value in Cell C9 accordingly by the subtraction formula as shown in the previous section.

Calculate Compound Interest with Simple Mathematical Formula

If we want our deposited money or the investment value to be compounded semi-annually, we have to change the compounding period only and input ‘6’ in Cell C6. The Future Value and the Compound Interest amounts will be auto-updated subsequently.


1.3 Compounding Period as Week(s)

We can also determine the future investment value with weekly compounding interest. Assuming that, we want our investment value to be compounded in every 13 weeks. As we know there are 52 weeks in a year, so we have to divide 52 with 13 at first to know how many compounding periods are available in a year for the scenario.

After that, the rate of interest will be divided by the number of compounding periods. The length of time will have to be multiplied by the same datum too.

The required formula in the output Cell C8 will be then:

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

Calculate Compound Interest with Simple Mathematical Formula

Now press Enter and you’ll get the following outputs right away.


1.4 Compounding Period as Day(s)

In our last criterion, we’ll find out how the mathematical formula works out to generate the future value if the compounding period is a specific number of days. Let’s say, we want our investment value to be compounded every 90 days. So, the required formula to obtain the future investment value in the output Cell C8 will be:

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

Calculate Compound Interest with Simple Mathematical Formula

After pressing Enter, you’ll get the return values as displayed below.


2. Use Excel FV (Future Value) Function to Determine Compound Interest

We can use the Excel FV function which is used to return the future value of an investment based on periodic, constant payments and a constant interest rate. The generic formula of the FV function is:

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

Where,

rate– A required value and refers to the interest rate per period.

nper– A required value and denotes the total number of payment periods.

pmt–  A required value and it implies the actual payment made each period. This argument has to be negative, so we’ll have to use a Minus (-) sign before the input value.

[pv]  It’s optional and refers to the present value that the investment is worth. If PV is omitted, Excel assumes that it is 0. And this argument too will be a negative value.

[type]– Another optional value that indicates when payment is actually made. If the argument is ‘0’, this denotes that payment is made at the end of the period, whereas if the type argument is ‘1’, this denotes that payment is made at the beginning of the period. If the type value is left out, Excel assumes that it is ‘0’.

2.1 Without Additional Payment per Period

In the following dataset, we’re using the previous inputs. We can now insert a certain amount of additional payment per period in this function. But we’ll include this criterion in the later parts of this method.

Use Excel FV (Future Value) Function to Determine Compound Interest

The required formula with the FV function to calculate the Future Investment Value in Cell C9 will be:

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

As we’re not including any additional payment here, so the 3rd argument (pmt) will be ‘0’ in the function.

Use Excel FV (Future Value) Function to Determine Compound Interest

Now press Enter and you’ll get the Future Investment Value as shown in the screenshot below.

Use Excel FV (Future Value) Function to Determine Compound Interest

To get the Total Interest amount, we can now subtract the initial investment from the future value in Cell 10. And the result is as follows.

Use Excel FV (Future Value) Function to Determine Compound Interest


2.2 With Additional Payment per Period

Let’s assume we want to deposit an additional $500 twice a year (with an interval of every 6 months). So, the required formula with the FV function now will be:

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

Here we’re using ‘1’ as the [type] argument in the FV function. It means the additional payment will be made at the beginning of each period.

Use Excel FV (Future Value) Function to Determine Compound Interest

After pressing Enter, we’ll find our Future Investment Value at once. And the following output includes the specified additional payments for all periods available in a term of 5 years.

But if we want to deposit the additional payment at the end of every period, then we have to input ‘0’ as the [type] argument. So, the modified formula will look like the following:

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

And the outputs will be lying in the specific cells as well.


Similar Readings:


3. Insert FVSCHEDULE Formula to Calculate Compound Interest in Excel

The FVSCHEDULE function returns the future value of an initial principal after applying a series of compound interest rates. It means you’re now able to input different rates of compound interest in a single function. We have to include the series of interest rates with a range of cells or we can enter the values in an array too.

In the dataset below, our initial investment is $5000. And we want to apply 4 different compound interest rates for successive 4 years.

FVSCHEDULE Formula to Calculate Compound Interest in Excel

So, the required formula with the FVSCHEDULE function to obtain the Future Investment Value in the output Cell C12 will be:

=FVSCHEDULE(C4,C7:C10)

FVSCHEDULE Formula to Calculate Compound Interest in Excel

Now press Enter and you’ll get the future value right away as shown below.

FVSCHEDULE Formula to Calculate Compound Interest in Excel

By using the FVSCHEDULE function, we can also enter a fixed rate of interest in an array. Let’s say, we want to apply 10% compound interest for an investment value of $5000. And the number of total compounding periods in a year is 4, which means the interest will be compounded after every 3 months in a year.

FVSCHEDULE Formula to Calculate Compound Interest in Excel

In this case, first of all, we have to divide the rate of interest by 4 and the resultant value will be 0.025.

Now we’ll include the value ‘0.025’ four times with commas (,) in an array for the 2nd argument (schedule) of the FVSCHEDULE function. So, the required formula with an array input of the interest rates will be:

=FVSCHEDULE(C4,{0.025,0.025,0.025,0.025})

And the return value will be displayed in the output cell as follows.


4. Estimate Compound Interest and Interest Rate with EFFECT Function

In the final method, we’ll apply the EFFECT function to determine the Compound Interest as well as the Future Value of an initial investment. The EFFECT function returns the effective annual interest rate. The generic formula of the function is:

=EFFECT(nominal_rate,  npery)

Where,

nominal_rate– Required argument. The nominal rate of interest.

npery– Required argument. It denotes the number of compounding periods per year.

To find out the future investment value, the required formula by using the EFFECT function will be:

=C4+(C4*EFFECT(EFFECT(C5,C6)*C7,C7))

Compound Interest and Interest Rate with EFFECT Function

After pressing Enter, the formula will return the future investment value immediately in the output Cell C8.

Compound Interest and Interest Rate with EFFECT Function


Compound Interest Calculator Online

Apart from using our Excel workbook for calculating compound interest, you can also browse online for more dynamic calculators. You can search for them on Google and the resources are uncountable. Here I’m adding two URLs of the websites which are well-known and most used online calculators to determine compound interest based on different input criteria.

The first one I’d like to recommend the Investor.gov. Here you can input your monthly contribution to the investment. In addition, the interest rates can be altered too in their online calculator.

Compound Interest Calculator Online

And another one with a simplified look but fulfills more requirements is from the Calculator Site. They have reputations for providing a large range of online calculators for different purposes. The following screenshot represents the outlook of their online calculator destined to determine the future investment value and the compound interest as well.

Compound Interest Calculator Online


Concluding Words

I hope all of the methods mentioned in this article will now help you to apply them in your Excel spreadsheets when you need to calculate compound interest with a formula. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo