How to calculate Compound Interest

Power of compounding - Everyone agrees magic of compounding and power of compounding. No one needs any explanation on this. So next question comes, how do you compute it and make decision on outcome you are looking at. Bear in mind, I am discussing here from the savings perspective, however, the same holds true for the loan as well.

Going to keep this very simple without going into smaller tiny nitty-gritty. I am assuming you have access to Microsoft Excel (you can use online free version by creating file in OneDrive as well). I am currently using Excel Office 365 version.

There’s an inbuilt function in Excel called FV, which I am going to use.

FV(rate,nper,pmt,[pv],[type])

The FV function syntax has the following arguments:

Rate - The interest rate per period.
Nper - The total number of payment periods in an annuity.
Pmt - The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Pv (Optional argument) - The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
Type (Optional argument) - The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
Set type equal to 0, if payment is due at the end of the period and 1 if payment is due at the beginning of the period.

Important – Ensure arguments for Rate and Nper are both on same basis, i.e. if ‘Rate’ is measured as month, ‘Nper’ should be also measured in month.

I am not getting into explaining the mathematical formula (you can refer to Investopedia for the same). Instead, I am going to take few examples and show you, how you would compute the same in Excel.

Example 1 – You make a term deposit of Rs 1,00,000 for 5 year at 6% interest rate with interest compounded annually. What would be the return?

Here, Rate = 6% (yearly), Nper = 5 (yearly), PV = Rs 1,00,000, I am assuming interest is credited at end of the period. So formula would be:

=-FV(6%,5,0,100000,0) ; if correctly done, you should see the output of Rs 1,33,823. Meaning you will be earning Rs 33,823 of total interest on compounded basis.

Note – I have taken negative sign at the beginning of the formula, as output would be otherwise negative to show what the future value basis given assumptions.

Example 2 – You are planning to make Recurring Deposit of Rs 5,000 each month for 12 months at interest of 6%, what would be the return?

Here, Rate = 6%/12 (converting yearly to monthly), Nper = 12 (number of months), PMT = Rs 5,000, I am assuming interest is credited at end of the period. So formula would be:

=-FV(6%/12,12,5000,0,1) ; if correctly done, you should see the output of Rs 61,678. Meaning you will be earning Rs 1,678 of total interest on compounded basis.

Here’s Quiz – how would your formula look like, if you have Rs 1,00,000 as initial investment and you are going to add Rs 5,000 each month for 12 months. Interest rate is 6% per annum compounded monthly. Consider interest credit at the end of the period.

Check in comments, if you want to see the solution.