Using Excel to Compute Compound Interest

by Kristina Dunbar, UGA

The formula for compound interest is

P = A(1 + i)t

where A is the initial amount, i is the interest rate per compounding period, and t is the number of periods the amount is compounded for.  P is the value of the investment after t periods.

 

We can use Microsoft Excel to explore this problem.

Let's Invest Some Money!

Question:  With an initial investment of $5000, how much money would we have after 1 year if we had an annual interest rate of 3% compounded annually?  Quarterly?  Monthly?  How much after 2 years?  10 years?

We have created a spreadsheet to solve this problem.

These are the equations we used:

Column D (cell D2):    P = 5000(1 + .03)A2

Column E (cell E2):    P = 5000(1 + .0075)A2*4

Column F (cell F2):    P = 5000(1 + .0025)A2*12

You can see that after 50 years, with no further investment, you will have $21,919.53 to $22,366.54 in the bank, depending on how the interest is compounded.  Three percent interest is actually a very good interest rate to get for an investment, most banks offer around 2% annual interest rate.

Watch out for Credit Card Debt!

We have looked at the problem above where we had a $5,000 investment that received an annual interest rate of 3%.  But what if that $5,000 were a debt?

Most credit card companies charge anywhere from 10% to 22.5% interest.  The below spreadsheet shows how much debt you would accumulate if you accrued $5,000 debt with a 20% annual interest rate without making any payments.

These are the equations we used:

Column D (cell D2):    P = 5000(1 + .20)A2

Column E (cell E2):    P = 5000(1 + .05)A2*4

Column F (cell F2):    P = 5000(1 + .01667)A2*12

 

Credit card companies compound interest on a monthly basis.  Just think, after only one year, you will owe $6,099.35, which is $1,099.35 more than you borrowed.  In 30 years, that $5,000 debt will be almost two million dollars! 

Lesson:  Pay off your credit cards!

 

If you would like to see the Excel file used to create the above data, click here.