How to Calculate the Average Return for the Share of Stock in Excel

by Mark Kennan

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.

Photo Credits

  • Stockbyte/Stockbyte/Getty Images