Calculating "APY"

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 5.05%, you’ll type “.0505” 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 5.179%. In other words, if you get 5.05% annually with daily compounding, your APY = 5.179. 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 5.05% APY).

The resulting interest earned over each time period in question is shown below.​

Institution	Rate	CompFrq	Deposit 	APY	Int/Yr  	Time	Int/Time

HSBC (D) 	0.0505	365	$20,000.00	0.05179	$1,035.86	180	$510.84
							
Emigrant (D)	0.0515	365	$20,000.00	0.05285	$1,056.91	180	$521.21
							
							
Citibank (D)	0.055	365	$28,489.09	0.05654	$1,610.67	30	$132.38
Citibank (D)	0.055	365	$28,489.09	0.05654	$1,610.67	180	$794.30
							
Citibank (M)	0.055	12	$28,489.09	0.05641	$1,607.01	1	$133.92
Citibank (M)	0.055	12	$28,489.09	0.05641	$1,607.01	6	$803.50