Here is how, to find out what an APY is with Excel, here's the function:
=POWER((1+(A1/B1)),B1)-1 where A1 is the Rate and B1 is compounding frequency.
Try pasting this formula into any cell on a spreadsheet (except A1 or B1). In cell A1 you’ll put the stated annual interest rate – in decimal format. For example, if the stated annual rate is 6%, you’ll type “.06” in cell A1. Then, you put the number of times you’ll compound each year. For example, for daily compounding you’d enter “365” (or 360 depending on the institution) in cell B1.
In the example I’ve used, you’ll find that the APY is 6.183%. In other words, if you get 6% annually with daily compounding, your APY = 6.183. Try changing the compounding frequency and you’ll get an idea of how the APY changes. For example, you might show quarterly compounding (4 times per year) or the unfortunate 1 payment per year (which just results in a 6% APY).