How to Calculate the Equity Risk Premium in Excel

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.

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%.

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.

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.