Q3(30 points) You are an analyst at an asset management fund. The fund currently has five bonds in its portfolio with the attributes shown below.

The fund's founder believes that it is most likely that bond yields stay the same, but that it is possible (but very unlikely) that the yields for all of the bonds could drop as much as 200 basis points or rise by as much as 200 basis points. Your colleague has been working on a Monte Carlo analysis of the price risk faced by these potential interest rate moves. Their simulation model and output are on the following page. The simulated data can be found in the Q3 Data tab of the spreadsheet. Unfortunately, they only sent you a screenshot of the model and the data, so you cannot immediately tell which bond they were analyzing (since they named the input cells and all the bonds are currently trading atpar). Provide your answer in excel sheet format. The FORMATTEXT shall be used to display formulas. Populate results for each Parts (parts a, part b and part c) with explanations for each question.

	1	2	3	4	5
Annual Coupon Rate (CR)	10%	5%	6%	12%	8%
Yield to Maturity (Annualized) (YTM)	10%	5%	6%	12%	8%
Number of Payments / Year (NOP)	1	2	1	2	1
Number of Periods to Maturity (T)	8	10	6	8	8
Face Value (M)	100	100	100	100	100

Please use the simulated data, the simulation output, and the bond concepts discussed in class to answer the following questions.

Part a: If the founder's forecast of the future distribution of interest rates iscorrect, what is the expected gain/loss (in percentage terms) on the bond beinganalyzed?

Part b: Run the following regression using the simulated data and interpret the estimate of the slope coefficient b.

= + +

Part c: Which bond (1, 2, 3, 4 or 5) is it most likely that your colleague is analyzing? Explain your reasoning.

(Hint: You can determine this without setting up a simulation in @Risk. Think about how the regression equation in part (b) relates to the interest rate risk concepts discussed in class for bonds.)

A B C D E F G H 1 Q3 1 2 4 5 Annual Coupon Rate (CR) 10% 5% 6% 12% 8% 4 Yield to Maturity (Annualized) (YTM) 10% 5% 6% 12% Number of Payments / Year (NOP) 1 2 1 2 1 6 Number of Periods to Maturity (T) 10 6 Face Value (M) 100 100 100 100 100 8 9 10 Part a Expected gain 11 Explanation: 12 13 14 15 Part b Slope estimate (b) 16 Interpretation: 17 18 19 20 Part c Your colleague is most likely analyzing bond #: 21 Explanation: 22 23 24A B C D E T F G H 1 2 3 4 5 Annual Coupon Rate (CR) 10% 5% 6% 12% 3% Yield to Maturity (Annualized) (YTM) 10% 5% 6% 12% Number of Payments / Year (NOP) 1 2 1 2 Number of Periods to Maturity (T) 8 10 6 8 Face Value (M) 100 100 100 100 100 Simulation Yield Change 0.09% =RiskTriang(-0.02,0,0.02) 11 Current Price 100.00 =-PV(YTM/NOP, T,CR*M/NOP,M) 12 New Price due to Yield Change $99.54 =-PV((YTM+C10)/NOP, T, CR*M/NOP,M) 13 Percent Change in Bond Price 0.46% =RiskOutput("Percent Change in Bond Price")+(C12-C11)/C11 14 Percent Change in Bond Price -7.02% 7.54% 5.0% 90.0% 5.0% 18 10 19 20 9 21 IN go 23 24 Percent Change in Bond Price 26 27 @RISK Course Version Minimum -9.101% Maximum 10.076% 28 Rice University Mean 0.125% Std Dev 4.377% Values 100 30 31 32 33 34 35 36 37 10% 5% 2% 0% 4% 8% 2% .6% -4% 12% -8% 38 -10% 39 40A B C D 1 @RISK Data 2 Percent Change in Bond 3 Name Price Yield Change 4 Description Output RiskTriang(-0.02,0,0.02) Iteration / Cell 5C$13 SC$10 6 1 -4.0196 0.77% 7 2 -0.249% 0.04% 8 3 -5.15% 1.00% 0.939% -0.17% 10 in -2.649% 0.50%% 11 0.29% 0.059% 12 3.26% 0.609% 13 8 -1.049% 0.209% 14 9 4.57% -0.83% 15 10 -3.619% 0.69% 16 11 -4.76%% 0.92% 17 12 -2.51% 0.48%% 18 13 4.919 -0.89% 19 14 7.89% -1.40% 20 15 3.4696 -0.639% 21 16 0.43% -0.08% 22 17 6.309% -1.13% 23 18 7.06% -1.26% 24 19 -7.559% 1.49%% 25 20 -0.02% 0.009% 26 21 -2.319% 0.449% 27 22 4.99% -0.90%% 28 23 -0.74% 0.14% 29 24 8.30% -1.47%% 30 25 -3.79% 0.73% 31 26 0.06% -0.01% 32 27 2.9296 -0.549% 33 28 -5.59% 1.09% 34 29 -1.93% 0.37% 35 30 -0.34% 0.0696\f\f