How to Calculate APY in Excel
Microsoft Excel is a computer spreadsheet program released in the Microsoft Office software package. Excel is a powerful data organization and management tool which allows users to perform complex calculations. APY, or annual percentage yield, is the amount of interest accrued on money over a year, taking into account any interest compounding, which can be calculated quickly using Excel
Open Excel and start with a blank worksheet. The formula for APY is: APY= (1+(i/N))^N-1, where "i" is the nominal interest rate, and "N" is the number of compounding periods per year. "N" would equal 12 for monthly compounding, and 365 for daily. For yearly compounding APY= the nominal interest rate.
In cell A1 type "i," in cell B1 type "N," and in cell C1, type "APY."
In cell A2 type the nominal interest rate in percent, and in column B2 type the compounding periods per year.
Click on cell C2 and press the equals button.
Enter the APY formula, referring back the cells for values of "i," and "N" as needed. By clicking on a certain cell, Excel will enter the value of those cells into your formula. So, click on cell A2 for "i" and cell B2 for "N." The formula should look like this: =(1+(A2/B2))^B2-1. Press enter when you are finished entering the formula. It will give you the interest rate in decimal form.
Continue entering values for "i" and "N" going down columns A and B if you need to perform more APY calculations. After entering more values, all you have to do to run the calculations is click on the lower right-hand corner of cell C2 and drag the cell down for as many rows as necessary. Excel will remember the formula used in C2 and perform the same calculation on each row using the new inputs for "i" and "N."
Tips & Warnings
- APY is commonly used in conjunction with savings accounts, CDs and other interest bearing financial accounts.
- The higher the interest rate, the more interest compounding will affect APY.