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 can I use a regression to see the correlation between prices and interest rates?

    Learn how to use linear regression to calculate the correlation between stock prices and interest rates by taking the square ... Read Answer >>
  2. How do I find positive correlation in the stock market?

    Learn how positive correlation is found in the stock market, how correlation is calculated and how positive correlation is ... Read Answer >>
  3. How do I calculate correlation between market indicators and specific stocks?

    Discover how to calculate the correlation coefficient between market indicators and stock prices, a critical skill in technical ... Read Answer >>
  4. How is correlation used differently in finance and economics?

    Take a look at the similarities and differences between how statistical correlation is applied in economics as opposed to ... Read Answer >>
  5. What types of assets lower portfolio variance?

    Learn what type of assets reduce portfolio variance and how modern portfolio theory uses correlation coefficients. 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. 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.
  7. 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.
  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

    How To Trade Currency And Commodity Correlations

    Relationships between currencies and commodities exist throughout the financial markets. Find out how to trade these trends.
RELATED TERMS
  1. Negative Correlation

    In statistics, a perfect negative correlation is a relationship ...
  2. Risk Measures

    Statistical measures that are historical predictors of investment ...
  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. Serial Correlation

    The relationship between a given variable and itself over various ...
  6. Portfolio Variance

    The measurement of how the actual returns of a group of securities ...
Hot Definitions
  1. 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 ...
  2. Gross Profit

    Gross profit is the profit a company makes after deducting the costs of making and selling its products, or the costs of ...
  3. Risk Tolerance

    The degree of variability in investment returns that an individual is willing to withstand. Risk tolerance is an important ...
  4. Donchian Channels

    A moving average indicator developed by Richard Donchian. It plots the highest high and lowest low over the last period time ...
  5. Consumer Price Index - CPI

    A measure that examines the weighted average of prices of a basket of consumer goods and services, such as transportation, ...
  6. Moving Average - MA

    A moving average (MA) is a widely used indicator in technical analysis that helps smooth out price action by filtering out ...
Trading Center