A:

It is fairly straightforward to calculate the equity risk premium for a security using Microsoft Excel. Before entering anything into Excel, find the expected rate of return for the security and a relevant risk-free rate in the market. Once those are known, enter a formula that subtracts the risk-free value from the expected value. By using Excel, you can swap out and compare multiple security rates quickly.

Find the Expected Rate of Return

For fixed rate securities, substitute the current yield for the expected rate of return. Find the current yield by dividing the interest (coupon) payment by the purchase price. For example, a bond with a $50 coupon purchased at $975 has a current yield of 5.13%.

The usual method for finding the expected rate of return for an equity security involves guessing the likelihood of a possible gain or loss. Suppose you thought that there was a 50% chance of a stock gaining 20%, a 25% chance that it would gain 5% and a 25% chance that it would lose 15%. By multiplying and adding the probabilities – 0.5*0.2 + 0.25*0.05 + 0.25*-0.15 – you can estimate an expected return of 7.5%.

There are other, more complex methods of estimating expected total return.

Find the Risk-Free Rate of Return

The risk-free rate almost always refers to the yield of U.S. Treasury bonds. To find the real yield (as opposed to nominal yield), use the Treasury Inflation Protected Security (TIPS) yield.

Calculating Risk Premium in Excel

You may have already used Excel to calculate the expected rate of return. If so, simply use the value in that cell to represent the expected return in the risk premium formula. If not, enter the expected rate into any empty cell.

Next, enter the risk-free rate in a separate empty cell. Suppose you enter the risk-free rate in cell B2 and the expected return in cell B3. In cell C3, you might add the following formula: =(B3-B2). The result is the risk premium.

RELATED FAQS
  1. How are bond yields affected by monetary policy?

    Learn about how bond yields are affected by monetary policy. Find out how this determines the risk-free rate of return and ... Read Answer >>
  2. How can I calculate a bond's coupon rate in Excel?

    Find out how to use Microsoft Excel to calculate the coupon rate of a bond using its par value and the amount and frequency ... Read Answer >>
  3. How do I calculate stock value using the Gordon Grown Model in Excel?

    Learn about the Gordon Growth Model, also known as the dividend discount model. See how to use Microsoft Excel to determine ... Read Answer >>
  4. How do I calculate yield in Excel?

    Learn about yield as it pertains to bonds and how to calculate this prospective valuation of an investment's total return ... Read Answer >>
  5. How do I calculate fixed asset depreciation using Excel?

    Learn what depreciation is and how to use Excel to calculate depreciation for fixed assets with the straight line and sum ... Read Answer >>
  6. What is the formula for calculating return on assets (ROA) in Excel?

    Find out more about the return on assets (ROA) ratio and the formula for calculating a company's return on assets in Excel. Read Answer >>
Related Articles
  1. Investing

    Calculating the Equity Risk Premium

    See the model in action with real data and evaluate whether its assumptions are valid. Here is how to calculate the equity risk premium.
  2. Financial Advisor

    Risk-Free & 20% Return? More Like 100% Scam

    An investment that promises a risk-free return of 20% is 100% likely to be a scam.
  3. Investing

    What Investors Should Know About Interest Rates

    Understanding interest rates helps you answer the fundamental question of where to put your money. Learn about the relationship between rates and stocks.
  4. Investing

    How to calculate required rate of return

    The required rate of return is used by investors and corporate-finance professionals to evaluate investments. In this article, we explore the various ways it can be calculated and put to use.
  5. Small Business

    The Importance of Excel in Business

    Microsoft Excel is an indispensable tool for any business.
  6. Investing

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
  7. Trading

    How & Why Interest Rates Affect Futures

    There are at least four factors that affect change in futures prices, including risk free-interest rates, particularly in a no-arbitrage environment.
RELATED TERMS
  1. Risk-Free Return

    Risk-free return is the theoretical return attributed to an investment ...
  2. Market Risk Premium

    Market risk premium is the difference between the expected return ...
  3. Risk Premium

    A risk premium is the return in excess of the risk-free rate ...
  4. Capital Allocation Line - CAL

    The capital allocation line on a graph shows all possible mixes ...
  5. Capital Asset Pricing Model - CAPM

    Capital Asset Pricing Model is a model that describes the relationship ...
  6. Required Rate Of Return - RRR

    The required rate of return is the minimum return an investor ...
Trading Center