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.

If you enjoyed reading this, sign up for free updates!

Loading Disqus Comments ...
Loading Facebook Comments ...

4 thoughts on “An Introduction To Compound Interest With Spreadsheets, Part 2: Monthly Compound Interest, APRs, and APYs

  1. Daniel says:

    These calculations are exactly what I’ve been looking for. I couldn’t find a way to do it without a lot of manual math, or odd calculators that don’t show the formulas for achieving the results. Thank you so much!

  2. Angie says:

    These spreadsheets are excellent. I have not beable to find answers needed to finish my son’s homework without going back to college myself! Thanks for the help.

  3. Bogdan says:

    I love these series.

  4. Ted says:

    Do you have a function that will calculate interest compounded annually but also taxed annually? For instance, you deposit $1000 into an account at 4% interest compounded annually. At the end of each year the interest earned is taxed at a flat 30%. What will the balance be at 40 years?

    You could manually calculate the new balance each year and get to the answer of course:

    A = ((25000(1+(0.04)/1)^(1*1))-25000)*.07)+25000
    and then take the balance and replace the original principle and do the calculation over 39 more times. But, there must be a way to create a function or a single calculation that gets you to the answer at 40 years?

    Not sure if this does it (in terms of a graphable function):
    y = (((25000(1+(0.04)/1)^(1*x))-25000)*.07)+25000)(1+(0.04)/1)^(1*x)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>