Compound Interest Formula in Excel
Have you ever wondered how to calculate compound interest in Excel? It can be confusing even for a seasoned finance expert. That's why we've written up this step-by-step tutorial on how to use the compound interest formula in Excel to get the job done.
Before we begin, let us see what compound interest is.
Compound interest is the interest that is earned not only on the initial principal but also on accumulated interest from previous periods.
The total amount of money in an account after 't' years with simple interest and with compound interest depends on the principal (the initial amount of money deposited), the annual nominal interest rate (the stated percentage rate, usually quoted as an annual percentage), and how often the interest is compounded per year.
You can calculate compound interest by using the following formula:
A = P(1 + r/n) (nt)
Where:
A = amount after t years including interest earned
P = principal amount (initial investment)
r = annual nominal interest rate
n = amount of interest compounded per year
t = number of years
Now let us see 3 different ways to calculate compound interest in Excel.
Method 1:
Using Mathematical Compound Interest Formula in Excel
Let us consider the following example with the following data present.
Using a similar formula as described above, let us now calculate the Future Value for the principal amount for the given period and interest rate.
Step 1:
Cell E2 contains the principal amount (also known as the present value). Multiply this amount by the interest rate.
= E2 * InterestStep 2:
Since the interest rate is to be compounded quarterly (D5), we need to divide the annual interest rate with cell D5.
= E2*(1+E3/E5)
Step 3:
Since interest compounds four times a year, we must refer to a cell containing the number of years so that we can multiply 4 by the number of years. The formula would look like this:
=E2*(1+E3/E5)^(E4*E5)
Step 4:
With compound interest, we receive Rs. 24628.79 as the future value.
Now you have a compound interest calculator in Excel. We can compound interest for any data by changing the value for the Annual Interest Rate, the number of years, and compounding periods per year.
Method 2:
Calculating Compound Interest Using FVSCHEDULE Excel Formula
FVSCHEDULE is the most commonly used compound interest formula in Excel.
Let us consider the same data set as used in the previous example.
Calculate future value by using the FVSCHEDULE function. After applying a series of compound interest rates to an initial principal, the FVSCHEDULE formula provides its future value.
The formula used to calculate FVSCHEDULE is
=FVSCHEDULE(Principal, Schedule)
Step 1:
The first step is to write the FVSCHEDULE function in cell C6. This function takes two arguments, i.e., principal and schedule.
- Where the principal is the investment amount.
- Schedule is a series of interest rates enclosed in curly braces.
Step 2:
The principal value is provided with the reference of the C2 cell, and schedule is the value we get when we divide the annual interest rate by compounding frequency.
=C3/C5/p>
which gives the value 0.0175.
Apply the FVSCHEDULE formula now.
=FVSCHEDULE(C2, H3:H14)
where H3:H14 is the series of interest rates per quarter.
The compound interest at the end of 3 years using the compound interest formula in Excel is as shown below.
Method 3:
Calculating Compound Interest Using FV Excel Formula
Compound interest can also be calculated using the FV Excel formula.
Let us consider the same data set as used previously.
FV (Future Value) gives the future value of an investment based on periodic, constant payments and a constant interest rate.
Here's the formula to calculate the FV function:
=FV (rate, nper, pmt, [pv], [type])
Where the arguments used are as follows:
- Rate - The interest rate for each period.
- nper - The number of payments per period.
- pmt - The payment made per period. This value must be negative.
- pv - The present value of future payments. Omitted values are assumed to be zero. Else, enter as a negative number.
- type - When a payment is due. 0 = period end, 1 = period beginning. The default value is 0.
Either the PMT or PV argument must be mentioned.
The rate is calculated as ‘Annual Interest Rate (C3)/ Compounding frequency (C5)’.
nper is calculated as 'Years (C4) * Compounding frequency (C5)'.
There is no additional payment to the initial investment. Hence pmt is considered as 0.
PV is the present value (C2) denoted with a negative sign.
So the final formula for FV function would be,
=FV(C3/C5,C4*C5,0,-C2)
The final amount calculated using the FV compound interest formula in Excel is shown below.
In this tutorial, we learned how to use the compound interest formula in Excel. This formula is extremely useful for calculating the value of an investment based on interest over time. We hope that after reading this tutorial, you have a better understanding of how it works and when you will use it in your daily life.
