How To Calculate APR And APY In A Spreadsheet – And Why You Would Want To

In the past, I gave a brief discussion about the difference between APR and APY when talking about simple and compound interest. What I didn’t explain, however, is why the difference is important and how you can use Microsoft Excel to calculate one from the other.

First, let’s define the two:

APR (annual percentage rate) is the return your money would earn in an investment over a year without any compounding. Let’s say you put $10,000 in a bank account with a 5% APR, but instead of putting the interest straight into the account, they paid the interest directly to you instead. Over the course of a year, the account would pay you exactly $500.

On the other hand, APY (annual percentage yield) is the return your money would earn in an investment over a year with compounding. Let’s say that same account with the 5% APR actually compounded monthly and left that money in your savings account to earn interest the following month. At the end of the year, your account would have $10,511.62 in it, meaning you actually earned $511.62 over the year. Thus, your APY would actually be 5.1162%.

Why doesn’t everyone just use either APR or APY to represent the earnings on an investment, instead of some situations using one and other situations using the other? To put it simply, companies will use whichever one makes their product look better in print. When you’re the one paying the interest, like on a credit card, they’ll quote APR; when they’re paying you interest, they’ll quote APY.

Let’s look at HSBC Direct, for example. Their savings account interest rate is quoted as being 5.05% APY. Their actual APR, though, is roughly 4.93% – the monthly compounding is what lifts the interest rate to 5.05% APY. On the other hand, let’s look at a credit card with an 18.99% APR, but it’s compounded daily … what’s the APY? 20.91%. For every dollar you have on an 18.99% APR credit card and don’t pay interest on all year, you’ll owe almost 21 cents at the end of the year.

So how can I convert back and forth between the two? It’s very simple to do with the aid of Microsoft Excel or Open Office Calc. If you know how to use the two programs, the instructions below should be very simple; if not, it’s well worth educating yourself on how to use a spreadsheet as they can be invaluable tools for personal finance (here’s a nice primer to get you started).

Converting APY To APR

These instructions will set up Excel to convert APY to APR, useful for figuring out how much a savings account is really paying you.

In cell A1, type APY
In cell A2, type # times a year
In cell A3, type APR
In cell B3, type =((1+B1)^(1/B2)-1)*B2

Now, type your desired APY value into cell B1 and the number of times a year the interest is compounded into B2 (most of the time it’s monthly, so you’d type in 12). The APR will appear in B3. You may need to set B3 to have the “percentage” data format; just right click on B3, choose “Format Cell…” and then choose “Percentage” in that box.

Converting APR To APY

These instructions will set up Excel to convert an APR value into an APY value, useful for evaluating how much you’re really paying on a credit card.

In cell A1, type APR
In cell A2, type # times a year
In cell A3, type APY
In cell B3, type =(1+B1/B2)^B2-1

Now, type your desired APR value into cell B1 and the number of times a year the interest is compounded into B2 (most of the time for a credit card it’s daily, so you’d type in 365). The APY will appear in B3. You may need to set B3 to have the “percentage” data format; just right click on B3, choose “Format Cell…” and then choose “Percentage” in that box.

These instructions should help you really understand what bank account offers and credit card offers really mean and how you can get them on the same terms.

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