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. Semiannual 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)^n1. The letter is the interest rate, and the letter n is compounding periods. In my video I used the formula =(1+E2/E3)^E31 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/exceltables
Chris Menard
Chris Menard is a Microsoft Certified Trainer (MCT) and is employed fulltime as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 900 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 16 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube
Categories