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.

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

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

27 thoughts on “How To Calculate APR And APY In A Spreadsheet – And Why You Would Want To

  1. Keesa says:

    Thanks for giving the spreadsheet information! One more thing I can do with Excel!! :grins:

  2. Excellent posts! This really comes in handy.

  3. liz says:

    Thank you! This is something I’ve never completely understood, but the working through a spreadsheet really brings it to light.

  4. avlor says:

    Cool! I never understood those two values very well. That really helped! I think I “get it”. :) I plugged the calculations into a google spreadsheet and am ready to take over the world! (well maybe not quite yet…but I can at least compare the two values.)

  5. Andrea says:

    Love the spreadsheet info. Thanks!

  6. Bill says:

    Thanks for tackling this subject.

  7. Dan says:

    I was WAY confused, until I realized you need to enter APY or APR as a decimal (“0.05″, or “5%” – instead of just “5″).

    Thanks for the definitions!

  8. Greg says:

    I finally figured out why I could not match my bank’s savings account calculations. I was doing everything right except using the right interest rate! I had to first convert from APR to APY. It’s interesting that the bank’s comments on how interest is calculated is rather vague–it says it applies a “daily periodoc rate” to the daily balance–but does not say how that rate is calculated. Now I know. Thanks.

  9. Scott says:

    thanx a heap…trying to figure out what we will have for a family vacation. Works like a charm
    !!!!

    again thanx!

  10. Chuck says:

    I am a little confused here.

    The bank is offering a 6 month CD special with a rate of 5.67% with an APY of 5.75% simple interest that pays at the end of the term. When I put a rate of 5.67% on a 12 month term the APY is the same as the rate.

    Why? Wouldn’t the 12 month CD offer the same APY as the 6 month term?

  11. Chris says:

    For the 6 mo. CD 5.67% APR/5.75% APY example, be sure to enter “2″ for the “# times a year” entry as the assumption is that you will leave the money in a year, the rate of return will NOT change at the 6 mo. point, you will get reinvested interest at the 6 mo. point, and then get interest again at the 1 year point. The Excel formula works for your example if you don’t forget the two point in time at which interest is assumed paid.

  12. David G. says:

    Trent,
    Great Blog, Question for you : If I borrow $16,500.00 for 4 months and pay back 17,470.00 what is the rate of interest I paid.

    TIA David

  13. AJ says:

    Trent,
    Do you know what the formula is to calculate APR on an interest-only loan?

  14. Thomas J. says:

    This has been throwing me for a long time. Thank you, thank you, thank you!

  15. Bogdan says:

    Thanks very much for this excellent post.

  16. tegegne says:

    If I invest $5.000 in 4 month CD with a 3.4% APY, what would it be my retrn and how do you calculate that?

  17. MERS says:

    everything is ok, but how do banks calculate your apy on the cd accts. What is the formula to do the calculation?

  18. Jenelle says:

    Thank you, your excel conversion was really helpful. I wish I’d read this before opening my new, supposedly illustrious savings account.

  19. Very helpful says:

    Thanks for the explanation with details.
    Zaya Khananu

  20. Tracy says:

    Thanks for the formulas – I’ve been searching for some time. Can you walk me through the manual calculation (non-excel)?

  21. wolf says:

    num 17 i have the same question we dont understand this compound interest we ben doing the simple way just cant understand it we ll you show us step by step .

  22. Rie says:

    What is the APR for $46,638. if the APY is 5.50%, and the term of the CD is 4 years. How much interest paid may I expect at the end of 4 years?

  23. Andy says:

    Thanks for the article. I’ve tested the formula and I figured it needs to be changed to =(1+(B1/100)/B2)^B2-1 in order to give correct answers in percentage format.

  24. Doug says:

    FYI,

    The Excel Functions EFFECT and NOMINAL are available to calculate these along with other functions to calculate loan payments (PMT), payments on Principal (PPMT), Future value (FV),

  25. Hobbes says:

    Thanks man great post.

    Doug @ #24 – are these functions in an addon because they are not among my excel 2003 SP2 functions?

  26. crayonman says:

    Really helpful blog post. Calculating APR is a biatch.

    I was also searching for what is and is not included when making the APR calculation- what fee’s etc.
    For those curious:

    http://www.bankapedia.com/mortgage-encyclopedia/faqs/371-what-is-and-is-not-included-when-calculating-apr

  27. Rich says:

    Calculating the APR just using a calculator, how do you do that?
    APR=((1+B1)^(1/B2))-1
    APR=((1+.024)^(1/12))-1) or
    APR=1.024^.08333-1

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>