A:

In the financial world, R-squared is a statistical measure that represents the percentage of a fund 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 single 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 correlation, then square it. Both are shown below:

 

RELATED FAQS
  1. What's the difference between r-squared and correlation?

    Discover how R-squared calculations determine the practical usefulness of beta and alpha correlations between individual ... Read Answer >>
  2. 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 >>
  3. How is correlation used to measure volatility?

    See how the correlation between an asset and its benchmark index can be used as a proxy to determine the relative volatility ... Read Answer >>
  4. How can you calculate correlation using Excel?

    Find out how to calculate the Pearson correlation coefficient between two data arrays in Microsoft Excel through the CORREL ... Read Answer >>
  5. How do fund managers use correlation to create portfolio diversity?

    Read about how contemporary investment fund managers use the concept of correlation to add diversification among assets in ... 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

    R-Squared

    Learn more about this statistical measurement used to represent movement between a security and its benchmark.
  3. Financial Advisor

    Does Your Investment Manager Measure Up?

    These key stats will reveal whether your advisor is a league leader or a benchwarmer.
  4. Investing

    Understanding Volatility Measurements

    How do you choose a fund with an optimal risk-reward combination? We teach you about standard deviation, beta and more!
  5. 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.
  6. 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.
  7. 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.
  8. Investing

    Understanding the Oil & Gas Price Correlation

    Learn how the correlation between the commodity prices for natural gas and oil changed from 2004 to 2015 due to increased natural gas production.
  9. 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.
RELATED TERMS
  1. R-Squared

    A statistical measure that represents the percentage of a fund ...
  2. Negative Correlation

    In statistics, a perfect negative correlation is a relationship ...
  3. Positive Correlation

    A relationship between two variables in which both variables ...
  4. Correlation Coefficient

    A measure that determines the degree to which two variable's ...
  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. Nostro Account

    A bank account held in a foreign country by a domestic bank, denominated in the currency of that country. Nostro accounts ...
  2. Retirement Planning

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

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

    A transaction that can cancel out a forward contract that has the same value date.
  5. 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 ...
  6. Solvency

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