How to calculate rate of return on a share of stock in Excel?
Let’s say you purchased a share of stock, got dividends in paste several years, and then sold the stock. Now you want to calculate the rate of return on this share of stock, how could you solve it? The XIRR function can figure it out easily.
Recommended Excel Productivity Tools
For example, you purchased the stock on 2015/5/10 at $15.60, sold it on 2017/10/13 at $25.30, and get dividends every year as below screenshot shown. Now I will guide you to calculate the rate of return on the stock easily by the XIRR function in Excel.
1. Select the cell you will place the calculation result, and type the formula =XIRR(B2:B13,A2:A13), and press the Enter key. See screenshot:
Note: In the formula =XIRR(B2:B13,A2:A13), B2:B13 is the Cash Flow column recording the money you paid and got, and the A2:A13 is the Date column.
2. Keep the calculation result selected, and click the Percent Style button on the Home tab, and click the Increase Decimal or Decrease Decimal buttons to change decimal places of the percentage. See screenshot:
Now the rate of return for the stock is figured out and shown as a percentage. See screenshot: