Calculating the equity risk premium for a security using Microsoft Excel is rather straightforward. Before entering anything into the spreadsheet, find the expected rate of return for the security and a relevant risk-free rate in the market. Once those numbers are known, enter a formula that subtracts the risk-free value from the expected value. By using Microsoft Excel spreadsheets, you can quickly swap out and compare multiple security rates.
Understanding Equity Risk Premium
Equity risk premium is the return from a stock or portfolio that is above the risk-free rate of government bonds or cash. It is one of the basic tenets of investing: if you want growth, buy stocks, but if you do not want risk, hold cash or invest in Treasury bills, notes, or bonds.
Key Takeaways
- Equity risk premium refers to the return on stocks that is greater than the return from holding risk-free securities.
- Subtracting the risk-free rate from the expected rate of return yields the equity risk premium.
- Treasury bonds—specifically, TIPS—can be used as an input for the risk-free rate.
- The expected rate of return for bonds is the same as the current yield and, for equities, it is an estimate based on likely outcomes.
Find the Expected Rate of Return
For fixed-rate securities, the expected rate of return is the same calculation as the current yield. To find the current yield, divide the interest (coupon) payment by the purchase price. For example, a bond with a $50 coupon purchased at $975 has a current yield of 975/50 or 5.10%.
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% (.10 + .013 – .038).
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 yield. These so-called TIPS are government bonds with values tied to inflation, as measured by the Consumer Price Index (CPI). The value of the bond increases with inflation and decreases with deflation.
Calculating Risk Premium in Excel
You may have already used Microsoft Excel spreadsheets 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. For example, you can enter the risk-free rate in cell B2 of the spreadsheet and the expected return in cell B3. In cell C3, you might add the following formula: =(B3-B2). The result is the risk premium.
Using the earlier examples, assuming that the risk-free rate (using current yields for TIPs) is .3% and the expected return on a basket of equities is 7.5%. Subtract .3% (B2) from 7.5% (B3) and the result is 7.2% (C3), your equity risk premium.