Calculating apy
From Deep Thought
Contents |
Background
Getting the APY (annual percentage yield) on a savings or checking account is easy, just look at the number on the bottom of your bank statement. However, calculating this value for current investments is a bit harder. Very few investment companies show you your APY. Yes, they will show you your overal return and most likely a percent increase (or decrease) in your investment value, but this is NOT your annual yield.
Example
For example, if you invest $100,000 to purchase something (stocks, for example) and sell the investment in 20 years for $120,000 your APY is NOT 20%. Nor is it 1% (20% / 20 years). In case you are curious, the APY on that investment is 0.91%.
Easy Solution
If you are looking for an easy way to calculate your APY on an investment, so you can compare it to a loan rate or another investment such as your savings account, there is an easy solution provided by Excel or Google Docs Spreadsheets.
The trick is to use the built in XIRR function. XIRR is the calculator for Internal Rate of Return (IRR) which is economic geeks term that is synonymous with APY.
Using XIRR()
XIRR requires a minimum of 2 parameters passed to it, both of which is a RANGE of values or cells on the spreadsheet. The range of cells should be adjacent to each other and all in a single column or row.
First Parameters The Values
The first parameter is a range of cells that includes all your investment costs and your returns on the investment. In a simple case you will have 2 cells next to each other that reflect your initial purchase costs (stock price * shares + broker fees) as a negative number and your final reward for selling the asset hopefully as a positive number.
Second Parameter: The Dates
The second parameter is a range of cells that includes the date for each value. These should pair up on a one-to-one match with the first parameter list. In a simple case you will have 2 dates, an earlier date for the purchase date and a later date for the sales date.
Example XIRR Usage
Cell I1 contains the initial investment cost: -100,000 Cell I2 contains the sold for value: 120,000 Cell J1 contains the purchase date: 12/01/1987 Cell J2 contains the sale date: 12/01/2007
A complete XIRR function for Excel would look like this (place it in cell J3): =XIRR(I1:I2, J1:J2)
The result will show 0.915%, or .00915 if you have not formatted the XIRR() cell as a percentage.
