**How to Calculate Stock Return in Excel?**

Calculating stock return in Excel is a crucial step in finance and investment analysis. It helps investors and analysts to evaluate the performance of a stock over a specific period, which is essential for making informed investment decisions. In this article, we will demonstrate how to calculate stock return in Excel using various methods.

**What is Stock Return?**

Before we dive into the calculation process, let’s define what stock return is. **Stock return** is the profit or loss made by an investment in a stock over a specific period, usually expressed as a percentage. It is the change in the stock price over a certain period divided by the initial stock price.

**Formula for Stock Return**

The formula to calculate stock return is as follows:

**Stock Return = (Ending Stock Price – Starting Stock Price) / Starting Stock Price**

Where:

**Ending Stock Price**is the price of the stock at the end of the period**Starting Stock Price**is the price of the stock at the beginning of the period

**How to Calculate Stock Return in Excel**

There are several ways to calculate stock return in Excel, and we will discuss the most common methods:

### Method 1: Using the Formula

The first method is to use the formula directly in Excel. Follow these steps:

**Enter the starting stock price**in cell A1.**Enter the ending stock price**in cell B1.**Enter the formula**in cell C1:**=(B1-A1)/A1**.**Format the cell**as percentage by selecting the cell, going to the "Number" tab in the ribbon, and clicking on "Percentage".

**Example**

Date | Stock Price |
---|---|

1/1/2022 | 100 |

1/31/2022 | 120 |

In this example, the starting stock price is $100, and the ending stock price is $120. To calculate the stock return, enter the formula in cell C1:

`= (120-100)/100`

The stock return would be **20%**.

### Method 2: Using Excel Functions

The second method is to use Excel functions to calculate stock return. Follow these steps:

**Enter the starting stock price**in cell A1.**Enter the ending stock price**in cell B1.**Use the XIRR function**in cell C1:**=XIRR(A1:B1, Dates)**.**Enter the dates**in the "Dates" field, separated by commas. For example, if the dates are 1/1/2022 and 1/31/2022, enter them as**"1/1/2022", "1/31/2022"**.

**Example**

Date | Stock Price |
---|---|

1/1/2022 | 100 |

1/31/2022 | 120 |

In this example, the starting stock price is $100, and the ending stock price is $120. To calculate the stock return using the XIRR function, enter the formula in cell C1:

`=XIRR(A1:B1, {"1/1/2022", "1/31/2022"})`

The stock return would be **20.00%**.

### Method 3: Using a Table

The third method is to use a table to calculate stock return. Follow these steps:

**Create a table**with two columns: "Date" and "Stock Price".**Enter the starting and ending stock prices**in the table.**Use the formula**to calculate the stock return:**=(Ending Stock Price – Starting Stock Price) / Starting Stock Price**.**Copy the formula**down the column to calculate the stock return for each period.

**Example**

Date | Stock Price |
---|---|

1/1/2022 | 100 |

1/15/2022 | 105 |

1/31/2022 | 120 |

In this example, the starting stock price is $100, and the ending stock price is $120. To calculate the stock return using a table, enter the formula in cell C2:

`= (C2-A2)/A2`

Copy the formula down the column to calculate the stock return for each period.

**Tips and Tricks**

**Use consistent dates**: Make sure to use consistent dates throughout your calculation, including the dates in the formula and the dates in the table.**Use the same currency**: Use the same currency for all stock prices to avoid errors.**Format the cell**: Format the cell as percentage to display the stock return as a percentage.**Use Excel’s built-in functions**: Excel has built-in functions such as XIRR and XNPV that can help you calculate stock return.

**Conclusion**

Calculating stock return in Excel is a straightforward process that can be done using various methods. Whether you use the formula directly, Excel functions, or a table, you can easily calculate the stock return and evaluate the performance of a stock over a specific period. By following the steps outlined in this article, you can improve your financial analysis skills and make informed investment decisions.

**Additional Resources**

- Excel Functions: XIRR and XNPV
- Excel Formulas: Stock Return Formula
- Excel Tutorials: Basic Excel Formulas and Functions