How do you calculate variance in Excel?

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

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.  

New Articles:

dollar to peso rate philippines cost of cab from newark airport to manhattan euro to peso convertion currency conversion pounds to usd iban number of bank of india standard chartered symbol troy pound to troy ounce amex gold 50000 irs home improvement tax deductions uscis elis log in chase wire transfer limit personal account how much is fica withholding chase credit card reward points swot analysis threat walmart money center kiosk salaried employee meaning taxturbo.com cigna hra plan review 3000 usd to gbp different types of commercial leases qualify for american express platinum experian credit report phone number live person send money online with moneygram turbo tax estimator for 2014 dinar money exchange rate formula for compound interest with monthly deposits iso 26000 wiki how to get preapproved for a house loan natural state jackpot winners redeeming us savings bonds synonyms entrepreneur citi credit cards for bad credit are special assessments tax deductible walmart cashier requirements compare american airlines credit cards chase 1099 int irs form 433-a
RELATED FAQS
  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. Is variance good or bad for stock investors?

    Learn how high variance stocks are good for some investors and how diversified portfolios can reduce variance without compromising ... Read Answer >>
  3. How reliable is the mean variance analysis of an investment?

    Learn how mean variance analysis is used to determine the historical volatility of an asset and how future volatility may ... Read Answer >>
  4. Can a mean variance analysis be done for any investment?

    Learn how mean variance analysis is used in modern portfolio theory to create an optimal mix of assets to maximize return ... Read Answer >>
  5. What is the difference between variance and covariance?

    Learn more about the differences between covariance and variance, and how these metrics can be of use to you in minimizing ... Read Answer >>
  6. How to calculate Value at Risk (VaR) in Excel

    Learn what value at risk is, what it indicates about a portfolio and how to calculate the value at risk of a portfolio using ... Read Answer >>
Related Articles
  1. Investing

    Explaining Variance

    Variance is a measurement of the spread between numbers in a data set.
  2. Investing

    Using Historical Volatility To Gauge Future Risk

    Use these calculations to uncover the risk involved in your investments.
  3. Investing

    Computing Historical Volatility in Excel

    We examine how annualized historical volatility is computed from daily log returns, variance and standard deviation.
  4. Investing

    Guide To Excel For Finance

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

    Improve Your Investing With Excel

    Excel is a useful tool to assist with investment organization and evaluation. Find out how to use it.
  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. Trading

    The Linear Regression Of Time and Price

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

    Trading With Gaussian Models Of Statistics

    The entire study of statistics originated from Gauss and allowed us to understand markets, prices and probabilities, among other applications.
  9. 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.
RELATED TERMS
  1. Budget Variance

    A periodic measure used by governments, corporations or individuals ...
  2. Mean-Variance Analysis

    The process of weighing risk against expected return. Mean variance ...
  3. Variance Swap

    A type of volatility swap where the payout is linear to variance ...
  4. Sales Price Variance

    The difference between the amount of money a business expects ...
  5. Efficiency Variance

    The difference between the theoretical amount of inputs required ...
  6. Analysis Of Variances - ANOVA

    An analysis of the variation between all of the variables used ...
Hot Definitions
  1. Retirement Planning

    Retirement planning is the process of determining retirement income goals and the actions and decisions necessary to achieve ...
  2. Drawdown

    The peak-to-trough decline during a specific record period of an investment, fund or commodity. A drawdown is usually quoted ...
  3. Inverse Transaction

    A transaction that can cancel out a forward contract that has the same value date.
  4. Redemption

    The return of an investor's principal in a fixed income security, such as a preferred stock or bond; or the sale of units ...
  5. Solvency

    The ability of a company to meet its long-term financial obligations. Solvency is essential to staying in business, but a ...
  6. Dilution

    A reduction in the ownership percentage of a share of stock caused by the issuance of new stock. Dilution can also occur ...
Trading Center