Nehad Ulfat
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.
📌 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
📌 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 
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))
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.
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))
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))
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.
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.
Now press Enter and you’ll get the Future Investment Value as shown in the screenshot below.
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.
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.
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:
- How to Calculate Compound Interest for Recurring Deposit in Excel!
- Compound Interest Excel Formula with Regular Deposits
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.
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)
Now press Enter and you’ll get the future value right away as shown below.
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.
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))
After pressing Enter, the formula will return the future investment value immediately in the output Cell C8.
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.
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.
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.
