You are currently viewing How to calculate compound interest in Excel

How to calculate compound interest in Excel

Compound interest is where interest is credited on a loan or deposit with consideration of 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 formulae FV = PV (1+r) n where FV denotes the future value, PV the present value, r indicates interest rate per period and n is the periods.

Excel being 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 in 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 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 it is not efficient.

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 the present value, r indicates the rate of interest 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 investment after 7years, 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), compounding period per year *(year) is n and the investment value is FV. This formula saves a lot of steps of 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 signify 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.