A:

In the financial world, R-squared is a statistical measure that represents the percentage of a fund's or a security's movements that can be explained by movements in a benchmark index. Where correlation explains the strength of the relationship between an independent and dependent variable, R-squared explains to what extent the variance of one variable explains the variance of the second variable. The formula for R-squared is simply correlation squared. (Want to learn more about Excel? Visit Investopedia Academy for our Excel courses).

Common Mistakes with R-Squared

The first most common mistake is assuming an R-squared approaching +/- 1 is statistically significant. A reading approaching +/- 1 definitely increases the chances of actual statistical significance, but without further testing, it's impossible to know based on the result alone. The statistical testing is not at all straightforward; it can get complicated for a number of reasons. To touch on this briefly, a critical assumption of correlation (and thus R-squared) is that the variables are independent and that the relationship between them is linear. In theory, you would test these claims to determine if a correlation calculation is appropriate.  

The second most common mistake is forgetting to normalized the data into a common unit. If you are calculating a correlation (or R-squared) on two betas, then the units are already normalized: The unit is beta. However, if you want to correlate stocks, it's critical you normalize them into percent return, and not share price changes. This happens all too frequently, even among investment professionals.  

For stock price correlation (or R-squared), you are essentially asking two questions: What is the return over a certain number of periods, and how does that variance relate to another securities variance over the same period? Two securities might have a high correlation (or R-squared) if the return is daily percent changes over the past 52 weeks, but a low correlation if the return is monthly changes over the past 52 weeks. Which one is "better"? There really is no perfect answer, and it depends on the purpose of the test.

How to Calculate R-Squared in Excel

There are several methods to calculating R-squared in Excel.

The simplest way is to get two data sets and use the built-in R-squared formula. The other alternative is to find a correlation and then square it. Both are shown below:

 

RELATED FAQS
  1. How does correlation affect the stock market?

    Learn about the role correlation plays in prudent stock market investing, and how the correlation coefficient is used to ... Read Answer >>
  2. How should I interpret a negative correlation?

    Learn more about correlation and how businesses analyze variables. Find out how negative correlations are interpreted by ... Read Answer >>
  3. What are some examples of positive correlation in economics?

    Learn the most common examples of positive correlation in macroeconomics and microeconomics, including demand and price, ... Read Answer >>
  4. How do you calculate variance in Excel?

    To calculate statistical variance in Microsoft Excel, use the built-in Excel function VAR. Read Answer >>
Related Articles
  1. Tech

    How to Calculate R-Squared in Excel

    Daniel Jassy, CFA shows how to calculate R-Squared in Microsoft Excel.
  2. Investing

    Understanding Volatility Measurements

    Learn how to choose a fund with an optimal risk-reward combination. Find more information about standard deviation, beta, and more.
  3. Investing

    A Risk Statistics Case Study (PTTRX)

    Analyze the risk metrics of the mutual fund PTTRX. Find out what standard deviation, capture ratios and R-squared indicate about correlation and volatility.
  4. Financial Advisor

    Calculating Beta: Portfolio Math For The Average Investor

    Beta is a useful tool for calculating risk, but the formulas provided online aren't specific to you. Learn how to make your own.
  5. Investing

    T Rowe Price Capital Appreciation Fund Risk Statistics Case Study (PRWCX)

    Analyze PRWCX using popular risk metrics that are part of modern portfolio theory (MPT). Explore PRWCX's volatility, correlation and return statistics.
  6. Investing

    3 Cases When Beta Does Not Measure Volatility of Stocks

    Examine the theoretical and statistical relationship between beta and volatility to identify three factors that limit beta's explanatory value.
  7. 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.
  8. Investing

    Dodge & Cox's High Quality Bonds Mutual Funds (DODIX)

    Learn about the risk metrics of the DODIX Fund. Discover what standard deviation, capture ratios and R-squared indicate about systematic risk and volatility.
  9. Financial Advisor

    Do Liquid Alts Deliver During Volatile Markets?

    Have liquid alternative funds and ETFs delivered in this volatile stock market and over time? It depends.
  10. Investing

    VGHCX: Vanguard Health Care Fund Risk Statistics Case Study

    Discover one of the largest health care-focused mutual funds, and learn about the fund's historical values for seven key risk measures.
RELATED TERMS
  1. Benchmark For Correlation Values

    A benchmark for correlation values is a point of reference that ...
  2. Risk Measures

    Risk measures give investors an idea of the volatility of a fund ...
  3. Positive Correlation

    Positive correlation is a relationship between two variables ...
  4. Serial Correlation

    Serial correlation is the relationship between a given variable ...
  5. Portfolio Variance

    Portfolio variance is the measurement of how the actual returns ...
  6. Variability

    Variability is the extent to which data points in a statistical ...
Trading Center