assumes 0 correlation between compound successes Portfolio of Compounds Investment Statistics Input cell Calculated Single Compound Investment Statistics 207.23% 150 Expected Total return Expected Annual Return # Compounds in Portfolio Capital Commitment ($ Billions 11.88% $30.00 Total Standard Deviation 1339.18% 11.88% Expected Annual Return Annual Standard Deviation Sharpe Ratio 0.34 Annual Standard Deviatior 423.49% 34.58% ) Investment in Single Compound Success Failure Probability 5.00% 95.00% Total Return 6044.57% -100.00% Annual Returr 50.96% Year Disc Rate 10.00% Cash Flow DCF Total DCF Cash Flow 0 -$0.20 -$0.20 1 $0.00 $0.00 2 $0.00 $0.00 3 $0.00 $0.00 4 $0.00 $0.00 5 $0.00 $0.00 6 $0.00 $0.00 7 $0.00 $0.00 8 $0.00 $0.00 9 $0.00 $0.00 10 $0.00 $12.29 $0.00 11 $2.00 $1.82 $0.00 12 $2.00 $1.65 $0.00 13 $2.00 $1.50 $0.00 14 $2.00 $1.37 $0.00 15 $2.00 $1.24 $0.00 16 $2.00 $1.13 $0.00 17 $2.00 $1.03 $0.00 18 $2.00 $0.93 $0.00 19 $2.00 $0.85 $0.00 20 $2.00 $0.77 $0.00 Probability of Compound Succe 5.00% Probability of Compound Failure 95.00% Debt Face Value $24.58 # Compounds in Portfolio # of Compound Successes $0.00 0 1 1 2 3 4 5 6 6 7 8 9 $147.47 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 150 Annuity Discount Rate 10% Probability Probability of At Leas Annual S (billions) Generated by the # of Success Value of Annuity at t = 1 Discount Rat Value at t= 0 $ to Debt Holder $ to Equity Holders 0.05% 0 0 0.36% 99.95% $2.00 $12.29 3.00% $9.14 $0.04 1.41% 99.59% $4.00 S24.58 3.85% $16.85 $24.48 $0.00 3.66% 98.18% $6.00 $36.87 5.00% $22.63 $0.45 7.08% 94.52% $8.00 $49.16 $1.74 10.88% 87.44% $10.00 $61.45 $4.01 13.84% 76.56% $12.00 $73.73 $6.81 14.99% 62.71% $14.00 S86.02 $9.21 14.10% 47.72% $16.00 $98.31 $10.40 11.71% 33.62% $18.00 $110.60 $10.07 8.69% 21.91% $20.00 $122.89 S8.54 5.82% 13.22% $22.00 $135.18 $6.44 3.55% 7.40% $24.00 $4.36 1.98% 3.85% $26.00 $159.76 $2.68 1.02% 1.87% $28.00 $172.05 $1.51 0.49% 0.85% $30.00 $184.34 $0.78 0.22% 0.36% $32.00 $196.63 $0.37 0.09% 0.14% $34.00 $208.92 SO.17 0.03% 0.05% $36.00 $221.20 $0.07 0.01% 0.02% $38.00 $0.03 0.00% 0.01% $40.00 $245.78 $0.01 0.00% 0.00% $42.00 $258.07 $0.00 0.00% 0.00% $44.00 $270.36 $0.00 0.00% 0.00% $46.00 $282.65 $0.00 0.00% 0.00% $48.00 $294.94 $0.00 0.00% 0.00% $50.00 $307.23 $0.00 0.00% 0.00% $52.00 $319.52 S0.00 0.00% 0.00% $54.00 $331.81 $0.00 $24.52 S67.65 $233.49 % Equity 43.85% Debt/Equity 1.281 Sharpe Ratios Debt Financing Equity Financing Total Financing Amount ($) Expected Return Standard Deviation $16.85 '@ 3.85% $13.15 17.79% 78.86% $30.00 11.88% 34.58% 0.23 0.34 Summary Tab Question: What effect does changing the number of compounds have on the security's return/risk? Capital Structure nitial Structure Question: What is the effect of the bond's interest rate on equity risk? Question: Does using debt financing here require that various investors have different preferences? Question: How could you incorporate a Credit Enhancement into the capital structure? What effect would this have on the risk/return of debt/equity? Assignment: Create a capital structure with a subordinated debt tranche. Question: What happens to the risk/return on equity (what about the Sharpe ratio)? Question: What happened to the amount of equity needed? Question: Do you think you could sell this equity? assumes 0 correlation between compound successes Portfolio of Compounds Investment Statistics Input cell Calculated Single Compound Investment Statistics 207.23% 150 Expected Total return Expected Annual Return # Compounds in Portfolio Capital Commitment ($ Billions 11.88% $30.00 Total Standard Deviation 1339.18% 11.88% Expected Annual Return Annual Standard Deviation Sharpe Ratio 0.34 Annual Standard Deviatior 423.49% 34.58% ) Investment in Single Compound Success Failure Probability 5.00% 95.00% Total Return 6044.57% -100.00% Annual Returr 50.96% Year Disc Rate 10.00% Cash Flow DCF Total DCF Cash Flow 0 -$0.20 -$0.20 1 $0.00 $0.00 2 $0.00 $0.00 3 $0.00 $0.00 4 $0.00 $0.00 5 $0.00 $0.00 6 $0.00 $0.00 7 $0.00 $0.00 8 $0.00 $0.00 9 $0.00 $0.00 10 $0.00 $12.29 $0.00 11 $2.00 $1.82 $0.00 12 $2.00 $1.65 $0.00 13 $2.00 $1.50 $0.00 14 $2.00 $1.37 $0.00 15 $2.00 $1.24 $0.00 16 $2.00 $1.13 $0.00 17 $2.00 $1.03 $0.00 18 $2.00 $0.93 $0.00 19 $2.00 $0.85 $0.00 20 $2.00 $0.77 $0.00 Probability of Compound Succe 5.00% Probability of Compound Failure 95.00% Debt Face Value $24.58 # Compounds in Portfolio # of Compound Successes $0.00 0 1 1 2 3 4 5 6 6 7 8 9 $147.47 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 150 Annuity Discount Rate 10% Probability Probability of At Leas Annual S (billions) Generated by the # of Success Value of Annuity at t = 1 Discount Rat Value at t= 0 $ to Debt Holder $ to Equity Holders 0.05% 0 0 0.36% 99.95% $2.00 $12.29 3.00% $9.14 $0.04 1.41% 99.59% $4.00 S24.58 3.85% $16.85 $24.48 $0.00 3.66% 98.18% $6.00 $36.87 5.00% $22.63 $0.45 7.08% 94.52% $8.00 $49.16 $1.74 10.88% 87.44% $10.00 $61.45 $4.01 13.84% 76.56% $12.00 $73.73 $6.81 14.99% 62.71% $14.00 S86.02 $9.21 14.10% 47.72% $16.00 $98.31 $10.40 11.71% 33.62% $18.00 $110.60 $10.07 8.69% 21.91% $20.00 $122.89 S8.54 5.82% 13.22% $22.00 $135.18 $6.44 3.55% 7.40% $24.00 $4.36 1.98% 3.85% $26.00 $159.76 $2.68 1.02% 1.87% $28.00 $172.05 $1.51 0.49% 0.85% $30.00 $184.34 $0.78 0.22% 0.36% $32.00 $196.63 $0.37 0.09% 0.14% $34.00 $208.92 SO.17 0.03% 0.05% $36.00 $221.20 $0.07 0.01% 0.02% $38.00 $0.03 0.00% 0.01% $40.00 $245.78 $0.01 0.00% 0.00% $42.00 $258.07 $0.00 0.00% 0.00% $44.00 $270.36 $0.00 0.00% 0.00% $46.00 $282.65 $0.00 0.00% 0.00% $48.00 $294.94 $0.00 0.00% 0.00% $50.00 $307.23 $0.00 0.00% 0.00% $52.00 $319.52 S0.00 0.00% 0.00% $54.00 $331.81 $0.00 $24.52 S67.65 $233.49 % Equity 43.85% Debt/Equity 1.281 Sharpe Ratios Debt Financing Equity Financing Total Financing Amount ($) Expected Return Standard Deviation $16.85 '@ 3.85% $13.15 17.79% 78.86% $30.00 11.88% 34.58% 0.23 0.34 Summary Tab Question: What effect does changing the number of compounds have on the security's return/risk? Capital Structure nitial Structure Question: What is the effect of the bond's interest rate on equity risk? Question: Does using debt financing here require that various investors have different preferences? Question: How could you incorporate a Credit Enhancement into the capital structure? What effect would this have on the risk/return of debt/equity? Assignment: Create a capital structure with a subordinated debt tranche. Question: What happens to the risk/return on equity (what about the Sharpe ratio)? Question: What happened to the amount of equity needed? Question: Do you think you could sell this equity