Annual Percentage Yield (APY) vs. Interest Rate

Posted on: 01/17/2020

You should know the difference between the Annual Percentage Yield (APY) and the Interest Rate. APY takes compounding into account while the interest rate does not. APY is a better reflection on what you will earn on a savings or CD account and even some checking accounts. APY is based on one year. APY is the real rate of return earned on a savings account or CD or investment. APY is prominently displayed on savings accounts and CD accounts. The interest rate does not take into account compounding periods.

Example of APY with saving accounts

Below is an example showing several banks offer saving accounts. Notice the APY is clearly displayed.

Here is an easy example of APY vs. Interest Rate:

Bank A is offering a 1.69% interest rate, and Bank B is offering a 1.70% interest rate. Which bank do you pick for your savings account? Before you choose Bank B, with a higher interest rate of 1.70%, you need to know how frequently do the banks compound. If Bank A compounds daily, that would be 365 days of compounding, and Bank B compounds annually, Bank A is the better bank even with the lower interest rate. That is why we have APY. It allows you to compare apples to apples.

How to calculate the Future Value in Microsoft Excel

Excel has a Future Value function. It has 5 arguments, but only three are required.

Future Value (FV) Arguments in Excel

RATE - I took the interest rate in cell E3 and divided by 365 since the compounding is daily. If the compounding was monthly, I would divide by 12. Quarlery compounding is dividing by 4. Semi-annual would be dividing by 2.
NPER is the total number of payment periods. Cell E6 has 365 in it.
PMT is the payment made each period, it cannot change over the life of the investment. In this example, I did not use PMT since I'm making a one time payment up front, which goes into the PV field.
PV is the present value. In my example, the PV was cell E4, but I made it negative E4 since I put up $15,000. That is money coming out of my pocket.
Type is a value representing the timing of payment: payment at the beginning of the period = 1; payment at the end of the period = 0 or omitted.

The Future Value function is =FV(E2/E3,E3,,-E1) in my video at 01:50. I was calculating the total I would earn if I deposited $15,000 in a saving account at 1.69% interest, compounded daily for one year. The answer was $15,255.65 (cell E4 in my video).

How to calculate the APY in Microsoft Excel

There are two easy methods for calculating the APY in Excel:

Use the APY formula. The formula is =(1+r/n)^n-1. The letter is the interest rate, and the letter n is compounding periods. In my video I used the formula =(1+E2/E3)^E3-1 at 06:14
Use Excel's EFFECT function. The EFFECT function has two required arguments. The nominal rate or interest rate and the number of compounding periods per year. In my video, I used =EFFECT(E2,E3) at 06:58

Effect Function in Excel to detemine APY

There are only two argument with the Effect function and both are required.

Nominal rate is the interest rate. In my example, it is cell E3
Npery is the number of compounding periods per year. It is cell E6

Note: if the number of compounding periods is 1, or annual, then the interest rate the APY will be the same.

YouTube video on APY vs. Interest Rate with FV and EFFECT functions

Chris Menard' Free class on Excel Tables:

https://chrismenardtraining.teachable.com/p/excel-tables

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.