VAR.P Function

What is Variance

Variance is the extent of a spread between the numbers in the data set of the population. Why does the variance need to be calculated, and what does it signify to the Excel user?

We calculate the variance to check how far a value deviates from the mean of the population and from every other value in the population.

It typically measures the extent of variability between the numbers in the population. But how do you interpret the variance about the mean? There can be three different cases when it comes to interpreting the variance:

• When the variance is equal to zero, it means that all the values in the population are equal to the mean value
• When the variance is small, the analysis reveals that the values in the population are closer to the mean value 
• If the variance is large, the values in the population are far away from the mean value

In this article, you will read about the function that calculates the variance of a population and how you can make the best use of the function in data and financial analysis.

Understanding the VAR.P Function​

VAR.P is categorized as a statistical function that returns the variance of the entire population of a given data set.

Variance is calculated for a set of values to determine how much the values deviate from the average value of the set. The formula for calculating the population variance is:

where,

X = each value in the population
x = average of all the values in the entire population
n = number of values in the entire population
Suppose you want to calculate the variance using manual calculations for a given population. 

Assume that you have four numbers 10, 20, 15, and 18. The mean for the numbers is (10 + 20 + 15 + 18) / 4, which equals 15.75, as represented in column C. 

Alternatively, you can use the formula

=AVERAGE($B$4:$B$7) in cell C4, giving you the same result.

Next, we will find the difference between the particular value and the population's mean in column D.

By subtracting the cell C4 from B4, i.e., 10 - 15.75, you will get the result of -5.75. After you drag down the formula, you will get the result:

Now, we will take the square of the (x - x) and then divide it by the number of values (n), in this case, four, which will give the result as illustrated below:

So the variance for each number in the population is in column F. Recall the conditions for interpreting the variance, that the closer the result of variance is to zero, the smaller the variance between the value and the mean.

As you can see, x = 15 has the lowest variance since it is closest to the mean value x, while x = 10 has the highest variance since it is farthest from our mean value x.

The sum total of all the variances (8.265625 + 4.515625 + 0.140625 + 1.265625) will be 14.1875. This is the variance of the entire population.

VAR.P Function Formula

Let’s just agree you wouldn’t have all the time in the world to make all those manual calculations in Excel.

The calculation of variation holds significant importance in investments and portfolio management, where investors can gauge how much risk a particular security carries and if it would be profitable in the future.

As a result, it is necessary to have a ‘cheat code’ in this fast-paced world of money investing, and that cheat code is none other than the VARP function.

The syntax for the VAR.P function is:

=VAR.P(number1, [number2]...)

Where,

• number1 = (required) first argument for the entire population used in the calculation. The value can be hard-coded, cell reference, or range of cells.
• number2 = (optional) Additional arguments for the entire population used in the calculation.

How to use VAR.P Function in Excel?

Let’s assume that you have four numbers, 10, 20, 15, and 18, in Excel, which we will assume is the entire population of numbers as illustrated below:

To find the variance of the entire population, you will use the formula

=VAR.P(B4:B7),

which will give you the result of 14.1875.

This is identical to the result we obtained earlier using the manual calculations for the variance of four numbers of our population.

However, suppose you need to find the variance for individual numbers of the entire population. In that case, you first need to find the mean, subtract it from the value, then square the resulting and divide it by the number of items in the population.

Essentially, the manual process we demonstrated earlier in this article.

Since the variance of 14.1875 is not very far from zero, it means that the values in the population are close to the mean value.

Variance in Investments

So now that we have an understanding of what variance is, i.e., the variability among the population set, let’s understand how portfolio managers use the concept of variance to trade off the risk and rewards while managing the portfolio for their customers.

Variance can help the investors to either opt for a risky portfolio or a risk-averse portfolio. In either case, it helps them to determine how to allocate different stocks to the portfolio so that the risk involved is justifiable for the returns expected from the investment.

Mean-variance analysis

The mean-variance analysis is a mathematical technique that lets investors make investment decisions based on variance, expressed in the form of risk, to achieve the biggest rewards.

The mean-variance analysis assumes that rational investors will look to invest in low-risk and high-reward securities and make informed decisions. 

The mean-variance analysis is a fragment of Modern Portfolio Theory,  wherein investors invest in different securities that ultimately reduce the portfolio's overall risk while maximizing their expected return. 

Mean-variance analysis using VAR.P Function

To understand mean-variance analysis, we will see an example of how you derive variance for a required return. Assume you have $10,000 to invest in two securities you decide to spend equally for your portfolio.

You decide in Apple Inc and Tesla Inc. Firstly, and we wind closing prices for both the stock 2010 up to 202The data looks as illustrated below:

To find the percent change in the price, we will subtract the current price from the previous price of the security and then divide the result by the previous price, i.e.,

=(C4/C3)/C3 in cell E4,

and drag it down up to the last cell. 

Once we find the percent changes for both AAPL and TSLA, we will find the average return from 2010 to 2021 by taking the average of all the percent changes.
You will use the formula

=AVERAGE(E4:E14) in cell E15

to give you the result:

Next, we will find the variance of percentage change in the price for both securities. For this. You will use the formula

=VAR.P(E4:E14) and =VAR.P(F4:F14)

to give the result:

We have represented variance in terms of percentage. The interpretation says the values do not stray far from the mean percentage change in price for both AAPL and TSLA. 

TSLA is relatively a bit less risky than AAPL, based on the calculation of the variance of our historical data.

Standard Deviation and Correlation

Standard deviation is another measure that helps investors determine market volatility. The security price can swing in either direction if the security has a high standard deviation. 

This means that security is precarious. On the other hand, if the standard deviation is low, the stock tends to move rationally. This means that the stock is less risky as compared to its counterpart.

Standard deviation is the square root of variance; you can find it either by using the formula

=STDEV.P(E4:E14)

or by

=E16^(1/2)

The expected result for both the formulas will be the same in the spreadsheet, i.e., 2.84% in cell E17. 

The standard deviation for TSLA stock is 18.29% meaning it is riskier than AAPL due to its dispersed returns over the years.

Another critical parameter is the correlation between the two stocks. You can find it using the formula

=CORREL(E4:E14, F4:F14),

or you can also use the PEARSON function. 

If the correlation is near 1, the stocks move in the same direction by the same amounts, and their movements are directly correlated. On the other hand, if both stock prices move in the opposite direction, the value of the correlation returns near -1. 

Since our value of 0.36 is positive, we can say that the strength and direction of correlation are weak to moderate.

Expected Returns and Portfolio Risk

Once you find all these parameters, we finally find the portfolio return and risk based on the model we have built up. 

To find the portfolio return, we will use the formula:

=(E15*E19)+(F15*F19) 

The formula for total return is simply the sum of the expected return for each asset multiplied by its portfolio weight, as shown below:

While to find the portfolio risk, the formula will be:

=((E19^2*E17^2)+(F19^2*F17^2)+(2*E19*F19*E17*F17*E18))

The portfolio risk formula is:

((Weight of Asset 1)2 * (Standard Deviation of Asset 1)2) + ((Weight of Asset 2)2 * (Standard Deviation of Asset 2)2) + (2 * Weight of Asset 1 * Weight of Asset 2 * Standard Deviation of Asset 1 * Standard Deviation of Asset 2 * Correlation of Asset 1 and 2)

Or, in this case:

Which will give you the result:

Which will give you the result:

The simple interpretation is - a not-so-risky investment where the investor can expect good returns. In the beginning, we had mentioned that investment in both stocks would be equal, as represented by our portfolio weightage of 0.5.

Finally, you can also find the standard deviation of the portfolio risk using the formula

=E21^(1/2)

The portfolio is expected to be risk-averse without any irrational moves in the near term. 

You can further minimize risk by changing the weightage of both AAPL and TSLA stock.

So far, we have seen how the VAR.P function can help you find the variance of the entire data population. The function is relatively easy to use and uses cell references for the arguments. 

Based on a variance application, we even built a mean-variance analysis for two stock portfolios: AAPL and TSLA.

Researched and Authored by Akash Bagul | Linkedin

Reviewed and Edited by Sara De Meyer | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• RANDBETWEEN Function
• RATE Function
• ROUND Function
• SEARCH Function
• SQRT Function