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. What's the relationship between R-squared and beta?

    Learn about the relationship between R-squared and beta. Explore how the concepts are related and often used in conjunction ... Read Answer >>
  2. Does a negative correlation between two stocks mean anything?

    Learn what the concept of negative correlation means, understand how it is generally calculated and see how it is used in ... Read Answer >>
  3. What does it mean if the correlation coefficient is positive, negative, or zero?

    When a coefficient is greater than zero, it is a positive relationship; when the value is less than zero, it is a negative ... Read Answer >>
  4. What is the difference between positive correlation and inverse correlation?

    Learn the difference between a positive correlation and a negative, or inverse, correlation and the way they apply to the ... Read Answer >>
  5. 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 >>
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

    Financial Ratios Every Investor Should Know

    Explore the risk metrics of mutual fund DODFX. Learn how beta, R-squared, capture ratios and standard deviation measure systematic and volatility risk.
  3. Financial Advisor

    4 Reasons Why Market Correlation Matters

    Learn about how correlation can be used to measure how broader markets move in relation to each other. See how correlation is used to manage risk.
  4. 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.
  5. Investing

    Is the Stock Correlation Strategy Effective?

    The synchronized movement among stocks and markets in recent years is challenging diversification.
  6. 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.
  7. 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.
  8. Trading

    Using Currency Correlations To Your Advantage

    Knowing the relationships between pairs can help control risk exposure and maximize profits.
RELATED TERMS
  1. Coefficient of Determination

    A measure used in statistical model analysis to assess how well ...
  2. Risk Measures

    Risk measures give investors an idea of the volatility of a fund ...
  3. Benchmark For Correlation Values

    A benchmark or point of reference chosen by an investment fund ...
  4. Correlation

    In the world of finance, a statistical measure of how two securities ...
  5. Beta

    Beta is a measure of the volatility, or systematic risk, of a ...
  6. Variance

    Variance is the spread between numbers in a data set and their ...
Hot Definitions
  1. Economies of Scale

    Economies of scale refer to reduced costs per unit that arise from increased total output of a product. For example, a larger ...
  2. Quick Ratio

    The quick ratio measures a company’s ability to meet its short-term obligations with its most liquid assets.
  3. Leverage

    Leverage results from using borrowed capital as a source of funding when investing to expand the firm's asset base and generate ...
  4. Financial Risk

    Financial risk is the possibility that shareholders will lose money when investing in a company if its cash flow fails to ...
  5. Enterprise Value (EV)

    Enterprise Value (EV) is a measure of a company's total value, often used as a more comprehensive alternative to equity market ...
  6. Relative Strength Index - RSI

    Relative Strength Indicator (RSI) is a technical momentum indicator that compares the magnitude of recent gains to recent ...
Trading Center