EXCEL Tutorial – How to Construct a Compounding Interest Financial Calculator

You can construct an almost infinite variety of calculators right within your MS EXCEL spreadsheet application.
Here is how you can construct your own 10-year compounding interest financial calculator that would help you to see, for example, how much your $100 will grow at x percent of interest a year, on a year by year basis.
1) Launch a clean worksheet in Excel.
2) Reserve the cell A1 for your principal dollar amount. Think of this as your “deposit” in the bank.
3) Reserve the cell B1 for your annual interest rate (entered as a decimal number like 5.6 or 34.8, etc).
4) In cell C1, enter the following formula into the text input bar just above the spreadsheet and then click the green check-box icon to save the formula into cell C1:
=A1 + ((A1/100) * B1)
This formula will take the amount you enter into cell A1, divide it into 100, and then multiply it by the annual interest rate B1, and then add it to the original A1 amount to give you the total COMPOUNDED amount at the end of year one. For example, for $100 of deposit (A1) at 5% a year (B1), the C1 value should be should be $105.
Now here comes the exciting part.
5) Click and select cell C1 so that Excel should display a black rectangle around the cell. When you bring your cursor to the lower right corner of the cell, your cursor should turn into a dark and slim plus sign (“+”).
6) Click and drag the cell C1 down as many cells as you want along the C column. This automatically copies the formula in C1 to all the other cells – but not perfectly. Now you need to adjust each formula slightly.
If you click on the unadjusted cell C2, you will see the following formula:
=A2+((A2/100)*B2)
7) Change this by replacing all “A2”s with “C1” because you would like to have the C1 amount get compounded, not the amount in A2 (which is empty).
So the correct formula for C2 becomes:
=C1+((C1/100)*B1)
Similarly, the correct formula for C10 becomes:
=C9+((C9/100)*B1)
Now your calculator is ready for testing.
Plug in 100 for A1 and 5 for B1 and you’ll have $162.8 dollars at the end of 10 years.
What if the interest rate went up by one percent to 6%? Change B1 to 6 and you will have $179 dollars at the end of 10 years.
You can easily stretch this calculator to 20 or 30 years by adding the necessary additional rows to column C and adjusting the formula for each cell accordingly, as explained above.
Happy savings!