An Introduction To Compound Interest With Spreadsheets, Part 2: Monthly Compound Interest, APRs, and APYs

Previously, we discussed how compound interest works on a year-by-year basis, but in the real world, interest is usually compounded more often than that. For many purposes, monthly compounding is used, so let’s look at monthly compounding. Fire up your spreadsheet and enter a few labels:
In A1, enter Monthly Compound Interest Example
In A3, enter Amount
In A4, enter Annual Interest Rate
In A6, enter Monthly Interest Rate
In B3, enter $20,000
In B4, enter 5.00%

You’ll end up with something that looks like this.

Compound Interest 8

You’ll notice that in B6, I’m about to enter =B4/12 … what does that mean? That’s simply how to figure 1/12th of the annual rate of interest, or the piece of the annual interest that happens in a single month. When you enter that formula in the cell, you’ll see the number zero. Don’t worry, just click on that cell, go up to the Format menu above, choose the Cells option on that list, then in the popup box choose Percent and have it show two decimal places. After you do that, you’ll see a value of 0.42%, which is the actual monthly interest rate if the annual rate is 5%.

Now, if you’re doing annual compounding, a calculation is very easy. In cell A8, write If annual, interest is: and then in B8, enter =B3*B4 … you’ll see that with annual compounding, you’ll earn $1,000 in a year.

If you’re doing monthly compounding, though, it’s a little different. To see this in action, you’re going to have to set up some more labels:
In A10, enter If monthly:
In B10, enter Balance
In C10, enter Interest
In A11, enter Month 1, then click on the cell and drag the little black square in the lower right of the cell downwards until you can see all the months up to Month 12.
In B23, enter interest is:

You should have something that looks like this:

Compound Interest 9

Now, the math. For the first month, the balance is the same as the amount, so enter =B3 in cell B11. How much interest will that earn in a month? Enter =B11*$B$6 in cell C11 to find out; for our example, it’s $83.33. If you were to multiply this amount by 12, you’d find that it is $1,000, which is the same as the annual interest earnings.

But things change at the start of the next month: it compounds. In B12, enter =B11+C11 … and then click on that cell and drag the black square down until it’s lined up with Month 12. Do the same in the C column, starting with cell C11. You’ll notice that each month, the interest earned is a little higher.

So how much did you actually earn in the year using monthly compounding? In cell C23, enter =SUM(C11:C22) … this formula basically says add up everything between C11 and C22. The total in our example is $1,023.24.

Compound Interest 10

Right now, you’re seeing the difference between APR and APY. APR is the annual percentage listed above: 5.00%. However, APY is not equal to that. If you want to figure out the APY here, type APY in cell B25, and in cell C25, enter =C23/B3 … and then reformat the cell to be a percentage, as mentioned above. You’ll get a value of 5.12%.

Whenever a bank mentions an interest rate to you, they’ll give you the APR when they’re lending you money but give you the APY when you deposit money with them. To the uneducated, it makes the offer seem better, because almost everyone outside of the financial industry uses these values interchangeably. Thus, when you see a savings account with a 5.05% APY, the actual interest rate they’re giving you is lower; you can just earn a 5.05% overall return if you don’t touch the money at all.

Next time, we’ll take a look at mortgages and build a simple mortgage calculator.

Trent Hamm

Founder & Columnist

Trent Hamm founded The Simple Dollar in 2006 and still writes a daily column on personal finance. He’s the author of three books published by Simon & Schuster and Financial Times Press, has contributed to Business Insider, US News & World Report, Yahoo Finance, and Lifehacker, and his financial advice has been featured in The New York Times, TIME, Forbes, The Guardian, and elsewhere.