There are two methods to calculate the interest amount. These methods are simple interest and compound interest. The interest rate can be the nominal interest rate, annual percentage yield, or effective interest rate. The nominal interest does not consider the interval while calculating the interest rates, while in the effective interest, the compounding is done after certain intervals. This compounding can be calculated for a month, quarter, year, or 2 years. In general, the compounding is done weekly. Let us try to understand in detail how compound interest is calculated mathematically as well as in excel.
What is compound interest?
Compound interest, also called compounding interest, means the accumulated interest, which is calculated for the principal amount and interest on the deposit amount. In other words, it can be said as an interest in interest. Therefore, with compound interest on the same amount, the investment companies get interest on both the initial amount and the interest incurred on the previous period. The compound interest generally increases every year. As an example, suppose you have deposited Rs. 50 for 2 years with the 10% compound interest. For the first year, you will get the amount Rs. 5 (=50*0.10), and for the second year, the amount will be Rs. 5.5 (50*0.10 + 5*0.10), which is the sum of the principal amount of first-year and interest on that amount. So, the total compound interest is Rs. 10.5, which is the sum of the first year and second year’s interest.
How is Compound Interest different from Simple Interest?
Simple interest calculates the interest rate using the initial invested amount, rate of interest for the amount, and the number of years for the investment. On the other hand, compound interest is calculated considering the previous interest amount too. Take an example that you have invested Rs. 500 for 10 years at the interest rate of 10%. The simple interest that you will get on this amount is (500*10*.10). If we calculate the compound interest, it will be (500*.10) for the first year, (500*.10 + interest on the interest of the first year) and so on till 10 years. Let us check the differences between simple interest and compound interest.
Simple interest Vs. Compound Interest
The rate of growth for compound interest is high as compared to simple interest.
The compound interest has more benefit over an investor’s simple interest because compound interest almost doubles the amount invested. So, the amount grows faster for the compound interest.
The amount of interest for compound interest is always higher than the simple interest.
The principal amount changes every year for the compound interest. On the contrary, the principal amount remains the same for the simple interest.
Using compound interest, you can preserve the value of your money and diminish the inflation cost. This is not possible in the case of simple interest.
How to calculate the Compound Interest?
The formula used to calculate the compound interest is as below:
Compound Interest = P (1+r/n) nt
where, P = Initial amount
r = rate of interest
n = number of times compounded
t = Term period or number of years
Example of Compound Interest
Take an example that you have borrowed an amount of Rs. 5000 for the time of 5 years with the interest at 5% compounded annually.
So, here we have P= Rs. 5000, r=5%, n=12, and t=5.
Therefore, applying the formula of compound interest, Compound Interest = 5000 (1+5/12/100) 12*5
If we take the step by step calculation of the compound interest, then check how the amount is being calculated for each year.
For the first year, the compound interest is 5000*0.5 = 2500
For the second year, the compound interest is 2500 + 2500*0.5 = 2500 + 1250 = 3750
For the third year, the compound interest is 3750 + 3750*0.5 = 3750 + 1875 = 5625
For the fourth year, the compound interest is 5625 + 5625*0.5 = 5625 + 2812.5 = 8437.5
For the fifth year, the compound interest is 8437.5 + 8437.5*0.5 = 8437.5 + 4218.75 = 12656.25
In the above example, if the interest is compounded half-yearly, then n=2.
So, the formula will become 500 (1+5/2/100) 5
Compound Interest calculated daily, monthly, quarterly, annually
The compounding frequency affects the compound interest amount. The compounding frequency can be daily, monthly, quarterly, or annually. The compounding frequency decides the value of n in the compound interest formula.
Consider an example, three people invested Rs. 10000 for a year with an interest rate of 6.6%. The compounding frequency for Person A is monthly, for Person B quarterly, and Person C yearly. So, if we calculate the compound interest for three people, the interest amount will be as below:
Person A: Interest amount is Rs. 656.
Person B: Interest amount is Rs. 660.
Person C: Interest amount is Rs. 677.
How to calculate Compound Interest in Excel
The compound interest can be calculated in excel in two ways.
Method1: Calculation using the mathematical formula.
We will consider the above example to calculate the compound interest here. First of all, fill the excel with the values to be used in the formula. Then, in front of Compound Interest type = and stat typing the formula.
| A | B | C | D | |
| 1 | Principal amount (P) | 5000 | ||
| 2 | Interest rate (r) | 5 | ||
| 3 | Number of years (t) | 5 | ||
| 4 | Number the interest compounded (n) | 12 | ||
| 5 | Compound Interest (CI) | = B1 * (1 + B2/B4) ^ (B3*B4) |
Here B1, B2, B3, and B4 are the cells in excel.
This was the compound interest when compounded annually. Similarly, we can update the cell B4 with 2 or 4, in case the interest is compounded half-yearly or quarterly respectively.
Method 2: Advanced method using FV
The future value (FV) formula lets you calculate the compound interest in excel. You can use this formula if you do not want to use the above formula. This formula returns the FV, i.e., future value. Below is the syntax of the FV formula:
FV(rate, nper, pmt, [pv], [type])
where, rate = interest rate
nper = payment periods
pmt = additional amount paid in every period
pv = current investment value. This field should be negative.
type = denotes if the additional payment is pending; 0 means at the end and 1 means in the beginning.
The first three parameters are compulsory arguments, and the remaining two are optional ones. In case we omit the argument pmt, then the pv argument becomes compulsory.
Let us understand this with an example
Consider, you deposited Rs. 2000 for 5 years with an annual interest of 8% compounded monthly, and there is no additional payment. So, we will fill the formula as below:
FV(0.08/12, 5*12, -2000)
Here, the rate = 0.08/12 because the annual interest is 8%.
nper = 5*12, which means 5 years calculated for 12 months
pmt is empty because it is mentioned that there is no additional payment
pv = -2000 because 2000 is the amount, and this argument should be negative as per the formula.
For this formula too, we can fill the excel with the values such as principal amount, rate, years, etc. and fill the arguments in the FV formula with cell numbers.
| A | B | C | |
| 1 | Principal (pv) | 2000 | |
| 2 | Rate of interest (rate) | 8% | |
| 3 | Number of years (t) | 5 | |
| 4 | Number the interest compounded (n) | 12 | |
| 5 | Compound Interest (CI) | =FV(B2/B4, B3*B5, , -B1) |
The benefit of Compound Interest
Compound Interest has many benefits over simple interest. Below are some of the advantages of compound interest:
- It almost doubles the money you have invested. You can calculate the number of years in which your money will get double by dividing the rate of interest by 72. This will work under the condition that the interest rate is less than 20%.
- The investment for more years will give more return.
- The compounding frequency also impacts the amount you earn on an investment. For example, the interest compounded quarterly will get more money as compared to the interest compounded annually or half-yearly.
Conclusion
I have explained the compound interest in this article in simple terms. I hope you have understood the concept well. Now, you have understood how compound interest and simple interest differ and how compound interest is more beneficial than simple interest. You can easily calculate the compound interest in excel, which will help you solve many real-life investment related queries. For more concepts of mathematics, you can visit cuemath and learn commercial math. With cuemath, you can become an expert in commercial maths.
