How to Calculate Variance of Stock Returns in Excel (2 Easy Ways)

In general, variance is one of the most useful topics in statistics and is widely used to calculate stock returns in the stock market. Moreover, it gives you a measurement of how the data is spread around the mean. It calculates the distribution by looking at all the data. In this article, I will show you 2 easy ways to calculate the variance of stock returns in Excel. However, if you are curious about it, download our practice workbook and follow us.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


What is Variance?

The measure of a dataset’s variability, known as variance, shows how far apart the various values are from one another. Normally, the variance is calculated by dividing the square of the difference in the mean by the number of the population. However, its mathematical definition is the average of the squared deviations from the mean. Thus, we can represent the mathematical expression of sample variance as a formula. In sample variance, a sample is a chosen number of samples taken from a population.

Variance ( σ2 ) = Σ (X – µ)2 / (N-1)

Here,

X Individual value

µ Arithmetic Mean

N Total number of observations


2 Easy Ways to Calculate Variance of Stock Returns in Excel

This section of the article will teach you two simple and tricky ways to calculate the variance of stock returns in Excel. Moreover, I should not mention that I have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience. For the purpose of demonstration, I have used the following sample dataset.

Sample Dataset for How to Calculate Variance of Stock Returns in Excel

Here, you can calculate the Holding Period Return manually using the following formula:

=(C6-C5)/C5

Write the formula in cell D6 and apply the AutoFill tool to the entire column.


1. Calculate Variance of Stock Returns in Excel with VAR.S Function

In this example, I will apply the VAR.S function in order to calculate the variance of stock returns. It is the easiest process to complete the task. However, go through the steps below to do it easily.

📌  Steps:

  • Initially, select cell C17 and write down the following formula.

=VAR.S(D5:D16)

Calculation of Variance of Stock Returns in Excel with VAR.S Function

  • Lastly, hit the Enter key to find the variance of stock returns.

Read More: How to Do Variance Analysis in Excel (With Quick Steps)


2. Create Basic Formula to Calculate Variance of Stock Returns in Excel

Furthermore, you can use the basic variance formula to calculate the variance of stock returns in Excel. In this case, I have used the general formula of the variance. However, I have utilized the previous dataset to calculate the variance. Hence, go through the below steps.

📌  Steps:

  • Firstly, click on cell E17 and write the following formula.

=AVERAGE(D5:D15)

Here, I have used the AVERAGE function for the calculation.

  • Secondly, select cell E5 and enter the formula below.

=D5-$E$17

Create Basic Formula to Calculate Variance of Stock Returns in Excel

  • Thirdly, use the AutoFill tool for the whole column, and you will get Average Return Differences.

  • Again, write the following formula on cell F5 and use the AutoFill tool in order to find the Squared Difference.

=E5^2

  • After that, find the Sum of Squared Differences using the formula below on cell E18.

=SUM(F5:F15)

Here, I have used the SUM function.

  • Now, press Enter and use the following formula to find the Variance of Stock Returns.

=E18/(E19-1)

  • Finally, press the Enter key to get the final result.

Output of Formula to Calculate Variance of Stock Returns in Excel

Read More: How to Find Population Variance in Excel (2 Easy Ways)


Conclusion

These are all the steps you can follow to calculate the variance of stock returns in Excel. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo