Expected return, standard deviation, covariance in Excel

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.

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

Expected return calculator and formula

Return volatility formula and calculator

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%.

Figure 1: Returns under the different states of economy

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)

Figure 2: Use the “SUMPRODUCT” function to compute the expected return

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.

Figure 3: Expected variance and standard deviation

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)

Figure 4: Use the “SUMPRODUCT” function to compute covariance

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