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.

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)`

- 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`

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

**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

**How to Calculate Coefficient of Variance in Excel (3 Methods)****Calculate Mean Variance and Standard Deviation in Excel****How to Calculate Budget Variance in Excel (with Quick Steps)****Budget vs Actual Variance Formula in Excel (with Example)****How to Calculate Sample Variance in Excel (2 Effective Approaches)****Calculate Pooled Variance in Excel (with Easy Steps)****How to Calculate Variance Inflation Factor in Excel**