You own 50,000 shares in XYZ Corporation.

The current price of XYZ is S0 = 35 and the stocks price is lognormally distributed with mean = 25% and standard deviation = 50%. The Togo Bank has given you a loan of $2,000,000.

The loan conditions are: Loan term: 10 years. The loan is a bullet loan, meaning that there is no payment of interest or principal until year 10. Loan interest: 8% annually. In year 10 you will owe Togo Bank $2,000,000*(1.08)^10. The loan is a non-recourse loan, secured only by your stock package. This means that if the final payment on the loan is more than the value in year 10 of the shares, Star Bank will get the package of stocks.

Instructions: Simulate one path of the stock price and to compute the rate of return on the loan to the bank. Simulate (using the familiar Data Table technique) 25 possible outcomes to compute Togo Banks expected rate of return and the standard deviation of this return. (need to fill in yellow!)

A B D E F G I I 1 ASSET-BACKED LOAN 35.00 25% 50% 50,000 2,000,000 8.00% Average Sigma Compute the average and sigma of Star Bank return on the loan for the 20 simulations Simulation 1 2 3 4 Stock price WNO 2 Current stock price 3 Mean 4 Sigma 5 Number of shares 6 Loan principal 7 Loan interest 8 9 Year 10 11 1 12 2 13 3 14 4 15 5 16 6 17 7 18 8 19 9 20 10 21 22 Value of stock package 23 Loan + interest 24 Payment to Star Bank 25 Star Bank rate of return 26 27 28 29 30 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 A B D E F G I I 1 ASSET-BACKED LOAN 35.00 25% 50% 50,000 2,000,000 8.00% Average Sigma Compute the average and sigma of Star Bank return on the loan for the 20 simulations Simulation 1 2 3 4 Stock price WNO 2 Current stock price 3 Mean 4 Sigma 5 Number of shares 6 Loan principal 7 Loan interest 8 9 Year 10 11 1 12 2 13 3 14 4 15 5 16 6 17 7 18 8 19 9 20 10 21 22 Value of stock package 23 Loan + interest 24 Payment to Star Bank 25 Star Bank rate of return 26 27 28 29 30 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20