A Guide to Calculate Variance in Excel

Variance is an essential financial measurement that is used for all types of businesses. Variance can play an important part whether you are trading futures or want to understand the performance of different assets in your portfolio.

What is variance?

Variance can be described as a measure to analyse how far numbers of a set are from the mean value. From a practical point of view, it shows how much something tends to alter. Hence, variance measures the variability in the data set. Mathematically, it is the average of differences(squared) from the mean value.

Let’s look at an example below to understand how you can use variance in your life.

Suppose that there are 5 children in your locality. The ages of these children are 14, 10, 8, 6 and 2.

The mean would be:

14 + 10 + 8 + 6 + 2

————————— = 8

Now that we’ve calculate mean, variance can be calculated using simple steps.

Calculate the mean value of all the values i.e 8.
Subtract the mean from each number to compute the differences between the mean value and the actual value. The differences for each of the items (14, 10, 8, 6 and 2) would be 6, 2, 0, (-2), and (-6).
It’s time to find the square of each of the different values. These would be 36, 4, 0, 4, and 36.
Now, calculate the average of all the squared differences. It would be:

36 + 4 + 0 + 4 + 36

————————— = 16

The variance comes out to be 16 in this case.

But what is the purpose of calculating variance? What does this 16 signify?

Variance is used to compute differences in the data set. The smaller the number, the lesser the data set would spread. The larger the number, the more dispersions will be. The number 0 means there isn’t any variability in the data set.

What are the advantages and disadvantages of calculating variance?

Advantages:

Variance gives a better insight into the groups covered in the data set, as seen in the above example.
Variance treats all the differences/deviations from the mean as they are, even without paying attention to their directions.
Variance is widely used in many fields, including finance, where investors can calculate risk.

Disadvantages

Data gets skewed when there are numbers far from the mean. Hence, sometimes, additional weight might be given to such numbers.
Interpretation of variance is not always easy for everyone.
Standard deviation might be a better option than variance and is often used more than a variance.

How to calculate variance in excel?

To calculate variance in excel, you need to know the variance formula. Since there are inbuilt features, calculating variance in excel is an easy task.

Generally, variance is calculated for a sample and population. Let’s look at how to calculate the variance of a sample in excel.

Calculating the variance of a sample

Suppose you are interested in a rare animal found in America. You have collected 15 samples and computed their length in meters.

Now, to calculate the variance of a sample, you’ll use the VAR.S function in excel.

= VAR.S (value 1, value 2, value3, value4, ……..value 15)

Let’s find out the variance.

Step1. Enter your entire data in a single range of cells(all the values of 15 samples) in excel.

Step2. For calculating variance, enter the formula:

Variance =VAR.S(A2:A15)

Step3. The variance for your sample will be displayed in the next cell.

Note: Three functions can be used for calculating the variance of a sample ( VAR, VAR.S, VARA). For VAR and VARA, excel version 2000-2019 is required. For VAR.S excel version 2010-19 is needed.

Calculating the variance of a population

Now let’s say instead of a sample, you’re interested in the entire population of the animal in the whole world. There are 100 species in total.

You must keep in mind that calculating variance for a population requires a different formula from the sample one, i.e. VAR.P function.

Now, to calculate the variance of a population, you’ll use the VAR.P function in excel.

= VAR.P (value 1, value 2, value3, value4, ……..value 100)

Let’s find out the variance.

Step 1: Enter your entire data in a single range of cells(all the values of 100 samples) in excel.

Step 2: For calculating variance, enter the formula:

Variance =VAR.P(A2:A16)

Step 3: The variance for your population will be displayed in the cell.

Note: Three functions can be used to calculate a sample’s variance ( VAR, VAR.S, VARA). For VAR and VARA, excel version 2000-2019 is required. For VAR.S, excel version 2010-19 is needed.

Similarly, three functions can be used to calculate a population’s variance ( VARP, VAR.P, VARPA). Excel versions required are 2000-19 for VARP and VARPA and 2010-2019 for VAR.P.

Importance of variance in finance

Variance holds a special space in finance like any other field. It is used to compare the performance of all the assets in a portfolio to attain the best asset allocation where risk is minimised, and returns are maximised.

Let’s have a look at an example where our interest lies in the prices of the stocks and their returns.

We observed the stock’s prices and returns for 10 days. The stock prices were Rs 100, 120, 105, 110, 125, 130, 140, 135, 125 and 130 respectively. The returns as observed were 20.0%, -12.5%, 4.8%, 13.6%, 4.0%, 7.7%, -3.6%, -7.4% and 4.0%.

The variance would be 0.082, and the standard deviation (square of variance) would be 9.05% when we use the variance formula that we mentioned earlier.

Variance for investors

Variance assists investors in making investment decisions. Let’s say you are an investor with a high-risk tolerance. Then you’ll look for investments with high risk. High-risk investments usually have a high variance. Investments with low risk(low variance) are considered ideal for conservative investors.

Variance explains or computes the risk associated with investments that whether a particular investment is expected to perform better than/worse than the expectations.

But can you reduce variance without affecting expected returns? Absolutely. It can be done by diversification. Diversification across various types of investments(stocks, bonds, REITs etc.), economies and sectors can help you achieve your financial goals.

Conclusion

Variance is used in many fields for different purposes and holds a significant position in finance too. It shows how data points in the data set vary from each other. Investors can use variance to predict risk and returns for their investments. Usually, investments with high variance come with high risk and, thus, a high possibility of extreme returns.

A Guide to Calculate Variance in Excel