Spreadsheet Function of the Spreadsheet

Question: Give the solution on a separate sheet (labelled Part a, Part b, and Part cPart d will appear on the Part a tab). spreadsheet should be clearly labelled and easy to understand. Include a description of the function of the spreadsheet. Remember to make sure you clearly identify the inputs and outputs. Please also include the step in each spreadsheet. Answer: Part A The following are the steps and inferences from the assignment given. Probability of S alive is 50% and hence his death probability is 1-50%=50% Probability that S is alive in year 2 is the probability that he is alive in year 1 and he is alive in year 2. Hence the joint probability is 50%*50% or 0.5^2=25%. Hence the probability of his death in year 2 is 1-25% or 75%. Similarly till year 30 S does not receive any coupon. Hence 0 will year 30. In year 35 assuming he is alive, he gets the principal of 1000,000. The expected value of this is P(alive)*1000,000+P(dead)*0. Take the NPV which is 1000,000/(1+7%)^30 or 93663. M bond person receives coupons irrespective of S dead or alive. But principal will be received only if S dead. So multiply P(S dead)*expected value of receipt. The coupon paid semi annually @ rate 9.8% is equivalent to an effective annual rate of 10% calculated as (1+9.8%/2)^2-1. Hence every year, M receives 1000,000*10%. Calculate the PV for 35 years comes to 1,388,441 Since SM bond S bond+M bond the total value is c+d or 1,482,104 Part B is same as Part A except face value is $ 1 and hence the investor needs $ 659 as retirement amount at the end of 35 years. Hence he has to buy 659 bonds Part B is same as Part A except that face value of the bond is 100 instead of 100,000