Calculate Compound Interest in ExcelLearn how to calculate compound interest in Excel
Excel is a nifty tool that anyone can use for almost anything.
You can perform mathematical or statistical equations in it, create balance sheets and other financial statements, make graphs and charts, and many more.
That’s why it should be no surprise that Excel is still used in highly technical fields such as finance and accounting.
One of the many things that you can do in Excel is to calculate interest. And not just any interest.
I’m talking about compound interest, which is a little more complicated than simple interest.
With simple interest, you only calculate interest on the principal sum or amount.
With compound interest, you still calculate interest on the principal sum or amount. But, you also calculate interest on the interest already earned or accumulated.
For example, let’s say that you have an investment of $1,500 which earns 10% interest annually.
For the first year, simple and compound interest is the same which is $150 ($1,500 x 10%).
But the years beyond will be different.
For the second year, the simple interest will still be $150 ($1,500 X 10%).
However, the compound interest will now be $165 ($1,650 x 10%).
In this article, we’ll be discussing how to calculate compound interest in Excel.
We’ll be an Excel formula that is based on the formula for calculating compound interest.
We’ll also be using one of Excel’s in-built functions for calculating the future value to calculate compound interest.
Let’s get started.
What is Compound Interest?
Compound interest is the interest on principal plus any earned or accumulated interest (hence, compound or compounding).
For example, let’s say that you have an investment that earns compound interest.
You will earn interest based on your initial investment.
Additionally, you will also earn interest on any interest that you’ve already earned or accumulated.
The formula most commonly associated with the calculation of compound interest is as follows:
FV = PV(1 + r/n)nt
FV = refers to the future value
PV = refers to the present value
r = refers to the interest rate
n = refers to the number of periods or the compounding frequency (e.g. 1 for annual, 2 for semi-annual, 12 for monthly, etc.)
t = refers to the length of time interest is applied, usually expressed in years
If you want to calculate how total compound interest will accumulate at the of the loan or investment’s term, here’s the formula that you’ll be using:
Total Compound Interest = (PV(1 + r/n)nt) – PV
Simply put, you need to subtract the present value from the future value to calculate the total compound interest.
If you want to calculate the compound interest for a particular year or period, here’s the formula you’ll be using:
Compound Interest = (PV(1 + r/n)nt) – (PV(1 + r/n)n(t-1))
What you’re doing here is calculating the future value for that year and the year before it. Then you subtract the latter from the former.
Calculating Compound Interest in Excel
Now that we know the basics of calculating compound interest, we can now proceed with calculating it in Excel.
Setting the Stage
To make the calculation a smooth process, we’ll be setting up the sheet. It’s pretty much like making a template.
First, let’s set up an area where we will be inputting the variables for calculating compound interest (principal, interest rate, compounding frequency, years).
Next, we’ll set up the area for calculating compound interest. Make a column for year, interest rate, interest, and gross balance.
Set the Annual Interest Rate Column
Let’s get to the fun part. This time, we’ll be inputting formulas.
First, let’s put the formula for the Annual Interest Rate column. This one’s easy.
We only need to reference the interest rate from the variables table.
In this case, we’ll be referencing cell B2.
For better presentation, apply the percentage format to the cell.
Then, for the rest column, just reference the cell above it.
Set the Interest Column
Let’s proceed to the Interest column.
This is where we’ll be applying the basics of compound interest.
First, select cell D8.
Then, input this formula: =E7*(C8/$B$3)
To expound, we’re multiplying the gross balance of the previous balance by the annual interest rate divided by the compounding frequency.
This will give us the compound interest for that period.
We’re adding dollar signs to the reference to cell B3 because we want to make it an absolute reference.
Next, copy the formula to the rest of the column.
The formula will show an error for now. That’s okay.
It will resolve itself once we input the variables.
Set the Gross Balance Column
Lastly, let’s set up the Gross Balance column.
Select cell E7.
Enter the formula =B1 to make a reference to the initial/principal amount.
Then, on cell E8, enter the formula =E7+D8.
Copy and paste the formula to the rest of the column. Again, the formula will show an error. Don’t worry about it. It will resolve itself when we input the variables.
- To make the template more presentable, apply the accounting or currency number format to cell B2, as well as to columns as D and E.
- You can also set up an area for the Future Value and Total Interest.
- In the cell beside Future Value, enter this formula: =B1*(1+B2/B3)^(B3*B4)
- In the cell beside Total Interest, enter this formula: =E1-B1
Our template is now set.
Save the workbook or save it as a template so that you can readily access it anytime you want to calculate compound interest.
Calculate Compound Interest
Let’s try our new template. Suppose we have the following data:
- Principal – $2,000
- Annual Interest Rate – 12%
- Years – 5
- Compounding Frequency – 1 (annually)
Let’s fill in our variables table with this data:
And here’s the result:
From this sheet, you’ll be able to see the compound interest earned per period, the future value, and the total interest.
Excel’s FV Function
If you only want to calculate the future value of an investment or loan that accumulates compound interest, then you might want to use Excel’s FV function.
What this function does is calculate the future value according to the parameters you’ve set.
Its formula is as follows:
rate – refers to the interest per compounding period; to get this, divide the annual interest rate by the compounding frequency
nper – refers to the number of compounding periods; to get this, multiply the number of years when interest accumulates by the compounding frequency
pmt – refers to additional payments made per period. This should be represented as a negative number. Set this to zero (0) if there are no additional payments
pv – refers to the principal amount. This should be represented as a negative number. This parameter is optional and has a default value of zero (0)
type – specifies when additional payments are made. “0” means that additional payments are made at the beginning of the period. “1” means that additional payments are made at the end of the period. This parameter is optional and has a default value of zero (0)
How to use the FV function
Suppose we have the following data:
Let’s compute the future value using the FV function.
- Select an empty cell.
- In the selected cell, enter the FV formula. For our illustration, the formula will be: =FV(B2/B3,B4*B3,0,-B1)
- Press the Enter key. We have successfully calculated the future value.
Knowing how to calculate compound interest can be plenty helpful when deciding on which investment to make and loans to take.
Now that you know how to calculate compound interesting Excel, you should use this knowledge to make educated decisions.
FundsNet requires Contributors, Writers and Authors to use Primary Sources to source and cite their work. These Sources include White Papers, Government Information & Data, Original Reporting and Interviews from Industry Experts. Reputable Publishers are also sourced and cited where appropriate. Learn more about the standards we follow in producing Accurate, Unbiased and Researched Content in our editorial policy.