Interest
I just got off the phone with a customer who wanted to know how to calculate compound interest. There are two easy ways to accomplish this in Excel. First, on a line by line basis, secondly, using exponents.
Line by Line:
Download a copy of the spreadsheet to follow along. Cell A2 contains the amount you are seeding your account with. Cell B2 is 1, the first year of interest. Cell C2 is the interest you expect to earn that year. Cell D2 is the result of multiplying cells A2 and C2, returning the interest in dollars earned. Cell A3 is the result of adding cells A2 and D2, capital plus interest earned. Cell B3 is the result of adding one to cell B2, increasing the year by one. Cell C3 is set to equal cell C2, which assumes the same interest rate every year. Cell D2 can be copied to cell D3 as the formula will remain the same. Copy cell range A3 through D3 down as far as you would like to the see the compound result.
Exponent:
The above can be accomplished using exponents. The above layout is essentially each year compounding, or building off the prior year. The formula to see the result of a multi-period compound effect is 1 plus the interest rate to the power of the number of periods, multiplied by the initial capital. In our example above, we can enter the formula =1.05^20 * 1000 to receive the same result as we did on the line by line basis.