It is pretty easy to calculate the fairness threat premium for a safety utilizing Microsoft Excel. Before getting into something into Excel, discover the anticipated fee of return for the safety and a related risk-free fee in the market. Once these are identified, enter a method that subtracts the risk-free worth from the anticipated worth. By utilizing Excel, you’ll be able to swap out and examine a number of safety charges rapidly.
Find the Expected Rate of Return
For fastened fee securities, substitute the present yield for the anticipated fee of return. Find the present yield by dividing the curiosity (coupon) cost by the buy worth. For instance, a bond with a $50 coupon bought at $975 has a present yield of 5.13%.
The traditional methodology for locating the anticipated fee of return for an fairness safety includes guessing the probability of a potential acquire or loss. Suppose you thought that there was a 50% likelihood of a inventory gaining 20%, a 25% likelihood that it might acquire 5% and a 25% likelihood that it might lose 15%. By multiplying and including the possibilities – Zero.5*Zero.2 + Zero.25*Zero.05 + Zero.25*-Zero.15 – you’ll be able to estimate an anticipated return of seven.5%.
Find the Risk-Free Rate of Return
The risk-free fee nearly all the time refers to the yield of U.S. Treasury bonds. To discover the actual yield (as opposed to nominal yield), use the Treasury Inflation Protected Security (TIPS) yield.
Calculating Risk Premium in Excel
You might have already used Excel to calculate the anticipated fee of return. If so, merely use the worth in that cell to symbolize the anticipated return in the threat premium method. If not, enter the anticipated fee into any empty cell.
Next, enter the risk-free fee in a separate empty cell. Suppose you enter the risk-free fee in cell B2 and the anticipated return in cell B3. In cell C3, you would possibly add the following method: =(B3-B2). The result’s the threat premium.