A:

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.  

RELATED FAQS
  1. How can I measure portfolio variance?

    Find out more about portfolio variance, the formula to calculate portfolio variance and how to calculate the variance of ... Read Answer >>
  2. What is price variance in cost accounting?

    Understand what price variance is in relation to cost accounting. Learn the most common way price variance arises and how ... Read Answer >>
  3. 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 >>
  4. How do I know when to "rebalance" my investments?

    In order to have a disciplined approach using "rebalancing style" investing, you must first setup a defined model that specifies ... Read Answer >>
  5. What is the difference between variance and covariance?

    Learn about the differences between covariance and variance, and how to use them to minimize your stock portfolio's risk ... Read Answer >>
  6. 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 >>
Related Articles
  1. Investing

    Computing Historical Volatility in Excel

    We examine how annualized historical volatility is computed from daily log returns, variance and standard deviation.
  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. Personal Finance

    How budgeting works for companies

    Budgeting is an integral part of running a business efficiently and effectively. Learn more about the two types of budgets that companies commonly use: static and flexible.
  4. 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.
  5. Trading

    Improve your investing with Excel

    Find out how to use Excel, a useful tool for assisting with investment organizations and evaluations.
  6. Small Business

    The Importance of Excel in Business

    Microsoft Excel is an indispensable tool for any business.
  7. Investing

    An Introduction to Value at Risk (VAR)

    Volatility is not the only way to measure risk. Learn about the "new science of risk management".
  8. 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.
  9. Trading

    The linear regression of time and price

    This investment strategy can help investors be successful by identifying price trends while eliminating human bias.
RELATED TERMS
  1. Portfolio Variance

    Portfolio variance is the measurement of how the actual returns ...
  2. Cost Control

    Cost control is the practice of identifying and reducing business ...
  3. Volatility

    Volatility measures how much the price of a security, derivative, ...
  4. Variance Inflation Factor

    Variance inflation factor is a measure of the amount of multicollinearity ...
  5. Analysis Of Variance - ANOVA

    Analysis of variance (ANOVA) is a statistical analysis tool that ...
  6. Sales Mix Variance

    Sales mix variance compares the actual mix of sales with the ...
Hot Definitions
  1. Business Cycle

    The business cycle describes the rise and fall in production output of goods and services in an economy. Business cycles ...
  2. Futures Contract

    An agreement to buy or sell the underlying commodity or asset at a specific price at a future date.
  3. Yield Curve

    A yield curve is a line that plots the interest rates, at a set point in time, of bonds having equal credit quality, but ...
  4. Portfolio

    A portfolio is a grouping of financial assets such as stocks, bonds and cash equivalents, also their mutual, exchange-traded ...
  5. Gross Profit

    Gross profit is the profit a company makes after deducting the costs of making and selling its products, or the costs of ...
  6. Diversification

    Diversification is the strategy of investing in a variety of securities in order to lower the risk involved with putting ...
Trading Center