Variance is a measurement of the spread between numbers in a data set. The variance measures how far each number in the set is from the mean.

Using a data set chart, we can observe what the linear relationship of the various data points, or numbers, is. We do this by drawing a regression line, which attempts to minimize the distance of any individual data point from the line itself. In the chart below, the data points are the blue dots, the orange line is the regression line, and the red arrows are the distance from the observed data and the regression line. (Want to learn more about excel? Check out Investopedia Academy's excel course online!)  

When we calculate a variance, we are asking, "Given the relationship of all these data points, how much distance do we expect on the next data point?  This "distance" is called the error term, and it's what variance is measuring.  

By itself, variance is not often useful because it does not have a unit, which makes it hard to measure and compare.  However, the square root of variance is the standard deviation, and that is both practical as a measurement.  

Calculating Variance in Excel

Calculating variance in Excel is easy if you have the data set already entered into the software. In the example below, we will calculate the variance of the last 20 days of daily returns in the highly popular exchange-traded fund (ETF) named SPY, which invests in the S&P 500.

  1. The formula is =VAR.S(select data)

The reason you want to use VAR.S and not VAR.P (which is another formula offered) is that often you don't have the entire population of data to measure.  For example, if we had all returns in history of the SPY ETF in our table, we could use the population measurement VAR.P, but since we are only measuring the last 20 days to illustrate the concept, we will use VAR.S.  


As you can see, the calculated variance value of .000018674 tells us little about the data set, by itself.  If we went on to square root that value to get the standard deviation of returns, that would be more useful.  

  1. What is the difference between standard deviation and variance?

    Understand the difference between standard deviation and variance; learn how each is calculated and how these concepts are ... Read Answer >>
  2. What is the difference between standard deviation and average deviation?

    Understand the basics of standard deviation and average deviation, including how each is calculated and why standard deviation ... Read Answer >>
  3. How can I create a linear regression in Excel?

    Learn the steps involved in creating a linear regression chart in Microsoft Excel. A linear regression is a data plot that ... Read Answer >>
  4. According to the CAPM, the expected return on a stock, that is part of a portfolio, ...

    A. the covariance between the stock and the market. B. the variance of the market. C. the market risk premium. D. ... Read Answer >>
  5. How do you calculate R-squared in Excel?

    Calculate R-squared in Microsoft Excel by creating two data ranges to correlate. Use the correlation formula to correlate ... Read Answer >>
Related Articles
  1. Trading

    Exploring the Exponentially Weighted Moving Average

    Learn how to calculate a metric that improves on simple variance.
  2. Investing

    Value at Risk (VaR)

    Value at risk, often referred to as VaR, measures the amount of potential loss that could happen in an investment or a portfolio of investments over a given time period.
  3. Investing

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
  4. Trading

    The Linear Regression of Time and Price

    This investment strategy can help investors be successful by identifying price trends while eliminating human bias.
  5. Investing

    Understanding The Sharpe Ratio

    The Sharpe ratio describes how much excess return you are receiving for the extra volatility that you endure for holding a riskier asset.
  6. Investing

    Regression Basics For Business Analysis

    This tool is easy to use and can provide valuable information on financial analysis and forecasting. Find out how.
  7. Personal Finance

    Backtesting Value-at-Risk (VaR): The Basics

    Learn how to test your VaR model for accuracy.
  8. Investing

    How Investment Risk Is Quantified

    FInancial advisors and wealth management firms use a variety of tools based in modern portfolio theory to quantify investment risk.
  9. Investing

    Calculating volatility: A simplified approach

    Though most investors use standard deviation to determine volatility, there's an easier and more accurate way of doing it: the historical method.
  10. Small Business

    How to Calculate NPV Using XNPV Function in Excel

    Learn how to calculate the net present value (NPV) of your investment projects using built-in functions from Excel using the XNPV function.
  1. Yield Variance

    Yield variance describes the difference between actual output ...
  2. Sales Price Variance

    Sales price variance is the difference between the money a business ...
  3. Production Volume Variance

    The production volume variance measures the total amount of overhead ...
  4. Variability

    Variability is the extent to which data points in a statistical ...
  5. Homoskedastic

    Homoskedastic refers to a condition in which the variance of ...
  6. Autoregressive Conditional Heteroskedasticity - ARCH

    Autoregressive conditional heteroskedasticity (ARCH) is a statistical ...
Trading Center