Compound interest is where interest is credited on a loan or deposit considering the initial principal and the cumulative interest from the time before. A good example is when you invest 700,000/= with a 10% annual interest rate compounded annually. When a year lapses from the initial principal, you get 70,000/= (700,000 x 0.10) as interest, so your investment is worth 770,000/=. This amount will have cumulative power over a given period of years. This can mathematically be calculated using a formula FV = PV (1+r) n, where FV denotes the future value, PV is the present value, r indicates the interest rate per period, and n is the period.
Excel, a powerful tool aiding in accounting, can help calculate compound interest.
Compound interest yearly
One of the easiest ways is using the formulae; Principal*(1 + Interest rate in period)
If you have a principal of 7000 with a 12% interest rate, compounded annually, this is how the annual investment will be calculated in a year.
In our example, B2 is the principle that a rate of interest in A2 i.e. 12%. The formula is =B2*(1+$A2) in C2, and $ is used to put the reference to B2 since it is a constant.
The investment for two years can be calculated by simply using the formula, copying it, and pasting it into cell D2
The gross figure automatically changes from B2 to C2 with the change in reference cell, and hence C2 becomes our current gross figure.
To calculate the compound interest for 10 years of investment, you can use a generalized formula of compound interest instead of the above formula of calculating compound interest, which may seem so easy but inefficient.
General compound interest formula.
Applying the general interest formula, you can calculate compound interest in excel more conveniently: FV = PV (1+r) n, FV denotes the future value, PV is the present value, r indicates the interest rate per period, and n is the number of periods.
For example, if you have a principal of 7,000 with a 12% interest rate, compounded semi-annually, and you want to find an investment after 7 years, this is how the annual investment will be calculated in a year.
The principal interest in PV, (annual interest rate) /2 is r since the interest is compounded semi-annually (two times a year), the compounding period per year *(year) is n, and the investment value is FV. This formula saves a lot of steps of the calculation, and it is more convenient than the first one since you don't have to calculate the gross figure after each period.
FV Function and Compound Interest
Using excel's built-in future value function, you can calculate compound interest. As the general formula, the FV determines the future value of an investment depending on the values of specific variables.
The variables are
– rate-the interest rate for each period.
– nper-number of compounding periods.
– pmt– additional payment per period, and it signifies a negative number. When no value for pmt it is assumed to be zero.
– pv is the principal investment, which is represented by a negative number.
– type indicates when additional payments are made. When 0 is used, it indicates that that payment is made in the beginning period. 1 indicates payments due at the end of the period.
From our example above fv function can be found.
Knowing how to compute compound interest is critical for projecting investment success, whether for retirement planning or your portfolio, and Excel is a fantastic tool for doing so.