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:


  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. 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 >>
  3. 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 >>
  4. 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 >>
  5. 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 >>
Related Articles
  1. 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!
  2. 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.
  3. 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.
  4. 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.
  5. Investing

    5 Ways To Measure Mutual Fund Risk

    These statistical measurements highlight how to mitigate risk and increase rewards.
  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. Investing

    Tales From The Trenches: Perfectly Negative Profitability

    Use correlations to profit when two specific instruments move in opposite directions.
  8. Investing

    ETF Tracking Errors: Protect Your Returns

    Tracking errors tend to be small, but they can still adversely affect your returns. Learn how to protect against them.
  9. Tech

    Are Bitcoin Price And Equity Markets Returns Correlated?

    Is there a correlation between bitcoin's price and the equity markets? We investigate.
  10. Investing

    How does crude oil affect gas prices?

    Understand the origins of oil, how its price is determined and where its correlation with gas prices falls in the global economy.
  1. Coefficient of Determination

    A measure used in statistical model analysis to assess how well ...
  2. Negative Correlation

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

    A measure that determines the degree to which two variable's ...
  4. Inverse Correlation

    A contrary relationship between two variables such that they ...
  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. Call Option

    An agreement that gives an investor the right (but not the obligation) to buy a stock, bond, commodity, or other instrument ...
  2. Standard Deviation

    A measure of the dispersion of a set of data from its mean, calculated as the square root of the variance. The more spread ...
  3. Entrepreneur

    An entrepreneur is an individual who founds and runs a small business and assumes all the risk and reward of the venture.
  4. Money Market

    The money market is a segment of the financial market in which financial instruments with high liquidity and very short maturities ...
  5. Perfect Competition

    Pure or perfect competition is a theoretical market structure in which a number of criteria such as perfect information and ...
  6. Compound Interest

    Compound Interest is interest calculated on the initial principal and also on the accumulated interest of previous periods ...
Trading Center