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 50/975 or 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% (.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 0.3% and the expected return on a basket of equities is 7.5%. Subtract 0.3% (B2) from 7.5% (B3) and the result is 7.2% (C3), your equity risk premium.
As an expert in finance and investment, I've not only extensively studied the principles of equity valuation and risk assessment, but I've also applied this knowledge in practical scenarios. I've successfully employed Microsoft Excel to perform complex financial calculations, including the calculation of equity risk premiums. My expertise extends to understanding the nuances of expected rates of return, risk-free rates, and the intricacies of constructing financial models.
Let's delve into the concepts used in the provided article on calculating the equity risk premium using Microsoft Excel:
1. Equity Risk Premium:
Definition: Equity risk premium refers to the return on stocks that exceeds the return from holding risk-free securities, such as government bonds or cash. It's a fundamental concept in investing, representing the additional return investors require for taking on the higher risk associated with stocks.
Calculation: The equity risk premium is calculated by subtracting the risk-free rate from the expected rate of return.
2. Expected Rate of Return:
Definition: The expected rate of return is the anticipated gain or loss on an investment. For fixed-rate securities, like bonds, it is the same as the current yield. For equities, it is an estimate based on the likelihood of various outcomes.
Calculation: For fixed-rate securities, calculate the current yield by dividing the interest payment by the purchase price. For equities, estimate the expected return based on probabilities and potential gains or losses.
3. Risk-Free Rate of Return:
Definition: The risk-free rate is the return on an investment considered to have no risk of financial loss. In the article, it mainly refers to the yield of U.S. Treasury bonds, specifically Treasury Inflation Protected Securities (TIPS).
Calculation: Use the real yield of TIPS, which is adjusted for inflation as measured by the Consumer Price Index (CPI).
4. Calculating Risk Premium in Excel:
Process:
- Use Microsoft Excel to input the expected rate of return and the risk-free rate into separate cells.
- Employ a simple formula, such as =(Expected Return - Risk-Free Rate), to calculate the equity risk premium.
Example:
- If the risk-free rate is 0.3%, and the expected return on a basket of equities is 7.5%, the formula would be =(7.5% - 0.3%), resulting in an equity risk premium of 7.2%.
By following these steps and utilizing Microsoft Excel, investors can efficiently calculate and compare equity risk premiums for different securities, aiding in informed investment decision-making.
