How to Calculate the Average Return for the Share of Stock in Excel
Original post by Mark Kennan of Demand Media
Calculating the average annual return for a share of stock requires you to know the starting price, ending price, dividends paid and the duration for which the stock was held. You can calculate the price manually, or you could use spreadsheet program to set up a formula. This allows you to tinker with the data so you can see how small changes might make a difference. For example, you can quickly find out how the average return would change if you bought the stock for $1 more than you did, or if you sold it for $3 less.
Enter the initial price of the stock in cell A1 of the spreadsheet program. For example, if you bough the stock for $31, enter 31.
Enter the dividends per share earned on the stock in cell A2. If you earned $3, enter 3.
Enter the selling price of the stock per share in cell A3. For example, if you sold the stock for $34, enter 34.
Enter the number of years you held the stock in cell A4. If you held the stock for 3 years, enter 3.
Enter the following formula into cell A5: =(((A3+A2)/A1)^(1/A4)-1)*100 and the spreadsheet will display the average annual return as a percentage. In this example, it will display 6.075074, which means you had a 6.075 percent return per year on the stock.
- Stanford University; CAGR (Compound Annual Growth Rate); Michael Fan; 2006
- DePaul University: Compound Interest Formula
About the Author
Mark Kennan is a freelance writer specializing in finance-related articles. He has worked as a sports editor for "Ring-Tum Phi" and published articles on a number of online outlets. Kennan holds a Bachelor of Arts in history and politics from Washington and Lee University.
- Stockbyte/Stockbyte/Getty Images