In this tutorial, we will explain how you can use Excel to calculate a stock’s expected return, standard deviation, and its covariance with another stock.

See the last item in the Contents box below to follow this tutorial in a video format and/or download the Excel template we use.

#### Contents

If it is an online calculator you’re looking for, we’ve got you covered as well.

## Step 1: Prepare the inputs

The first thing we need to do is to enter the returns of the stocks we are interested in under the different states of the economy. As seen in Figure 1 below, we’ll assume an economy with five different states (great, good, neutral, bad, terrible). There is a probability associated with each state of the economy. For instance, the economy will be in a “great” state with 10% probability, in which case Stock A yields 14% and Stock B yields 35%.

## Step 2: Calculate the expected return

Once we’ve entered the return data and probabilities, we can compute the **expected return** using the “SUMPRODUCT” function as follows (see also Figure 2). This essentially multiplies the probabilities with respective return outcomes and sums them up.

`=SUMPRODUCT($C$3:$C$7,D3:D7)`

## Step 3: Calculate the variance and standard deviation

After computing the expected return, we can move on to **variance **and **standard deviation** calculations. We’ll compute expected variance first. Then, we can easily obtain standard deviation by taking the square root of the result.

Variance is based on squared deviations from the mean. So, we begin with computing deviations from the mean, which is expected return in this case. As seen in Figure 3, these are recorded in cells F3:F7 for Stock A and G3:G7 for Stock B. For example, the formula in cell F3 is:

`=D3-D$9`

Next, we once again use the “SUMPRODUCT” function to compute expected variance. For stock A, this is done in cell D10 (the highlighted cell in Figure 3) as follows:

`=SUMPRODUCT($C$3:$C$7,F3:F7,F3:F7)`

Note that “F3:F7” appears twice in the function as we need the *squared *deviations from mean. We find the variances of Stock A and Stock B as 0.0032 and 0.02448, respectively.

Then, the standard deviation is obtained by either using the square-root function “SQRT” or equivalently raising the variance figure to the power 0.5 (“^0.5”). Specifically, for stock A:

`=SQRT(D10)`

## Step 4: Calculate the covariance

The final step is to calculate the covariance between the returns of Stock A and Stock B. This will be similar to the variance calculation except rather than taking the squared deviation from mean, we’ll multiple the deviation from mean for Stock A with that of Stock B.

Again, this can be achieved using the “SUMPRODUCT” function as follows (see also Figure 4):

`=SUMPRODUCT(C3:C7,F3:F7,G3:G7)`

We observe that the covariance between Stock A and Stock B, which suggests a positive relation between the returns of these two stocks. In fact, we can easily calculate the correlation coefficient between A and B as follows:

0.00854 / (5.66% × 15.65%) = 0.96

## Video tutorial and Excel template

Click on the link below to download the Excel template used in this tutorial.

##### What is next?

If you have enjoyed this tutorial, you might like the following ones too.

If you would like to learn more about Excel functions widely used in finance, we recommend the following resources:

https://corporatefinanceinstitute.com/resources/excel/excel-for-finance