Calculating Interest

excelguy.co.za

Here I will show you how to calculate compound interest as well as normal interest.

COMPOUND INTEREST

To calculate compound interest in Excel, you can use the FV function. This example assumes that R1000 is invested for 10 years at an annual interest rate of 5%, compounded monthly. 

Here is the formula

=FV(C6/C8,C7*C8,0,-C5)

The FV function can calculate compound interest and return the future value of an investment. To configure the function, we need to provide a rate, the number of periods, the periodic payment, the present value.

To get the rate (which is the period rate) we use the annual rate / periods, or C6/C8.

To get the number of periods (nper) we use term * periods, or C7 * C8.

There is no periodic payment, so we use zero.

By convention, the present value (pv) is input as a negative value, since the R1000  “leaves your wallet” and goes to the bank during the term.

The solution goes like this this:

=FV(C6/C8,C7*C8,0,-C5)
=FV(0.05/12,10*12,0,-1000)
=FV(0.00417,120,0,-1000)
=1647

NORMAL INTEREST

interest=principal*rate*term
Explanation 

To calculate simple interest in Excel (i.e. interest that is not compounded), you can use a formula that multiples principal, rate, and term.

This example assumes that R1000 is invested for 10 years at an annual interest rate of 5%. Simple interest means that interest payments are not compounded – the interest is applied to the principal only.

In the example shown, the formula in H10 is:

=I5*I7*I6

How this formula works

The general formula for simple interest is:

interest=principal*rate*term

So, using cell references, we have:

=I5*I7*I6
=1000*10*0.05
=500