questiuse excel to answer 5 fiexed income question relate to coupon payments

FIN\t472\t-\tPROBLEM\tSET\t1\t(20\tpoints) Solve\teach\tproblem\tin\tEXCEL.\tOnce\tyou\tfinished\tplease\tupload\tthe\tEXCEL\ton\tBlackboard.\tSolve each\tproblem\tin\ta\tnew\tsheet,\tclearly\tnaming\tthe\tsheet\t(for\texample\tProblem\t1/\tHmw1,\tetc) DUE\tDATE:\t09/30/2017\tby\tmidnight. 1. Valuing\tbonds\t(4\tpoints) Draw\tthe\tconstant-yield\tprice\ttrajectories\t(per\t100\tof\tpar\tvalue)\tfor\ta\t10-year\tbond\twhen\tthe market\tinterest\trate\tis\t10%\tunder\tthe\tfollowing\tassumptions:\t5%,\t10%\tand\t15%\tcoupon\trates. 2. Finding\tthe\tfull\tprice\t(6\tpoints) Suppose\tthat\tthere\tare\tfive\tsemiannual\tcoupon\tpayments\tremaining\tfor\ta\t10%\tcoupon\tbond (per\t100\tof\tpar\tvalue).\tAlso\tassume\tthe\tfollowing: 1. 78\tdays\tbetween\tthe\tsettlement\tdate\tand\tthe\tnext\tcoupon\tpayment\tdate 2. 182\tdays\tin\tthe\tcoupon\tperiod a) Find\tthe\tfull\tprice. b) Find\tthe\taccrued\tinterest. c) Find\tthe\tclean\tprice. 3. Matrix\tpricing\t(3\tpoints) Assume\tthat\tan\tanalyst\tneeds\tto\tvalue\ta\t4-year,\t5%\tannual\tcoupon\tpayment\tbond,\tBond-K,\twhich is not actively traded. The analyst could find some other bonds, however, with a comparable credit\tquality. Bond-L:\t3-year,\t5.5%\tannual\tcoupon\tpayment\tbond\tat\ta\tprice\tof\t108 Bond-M:\t5-year,\t4.5%\tannual\tcoupon\tpayment\tbond\tat\ta\tprice\tof\t105 Find\tthe\tprice\tof\tthe\tbond. 4. Effective\tannual\trate\t(2\tpoints) A\tbond\twith\ta\tface\tvalue\tof\t$1,500\tcan\tbe\tpurchased\tfor\t$800.\tThe\tbond\twill\tmature\tfive\tyears from\tnow\tand\tthe\tbond\tdividend\trate\tis\t12%.\tIf\tdividends\tare\tpaid\tevery\tthree\tmonths,\twhat effective\tinterest\trate\twould\tan\tinvestor\treceive\tif\tshe\tpurchases\tthe\tbond? 1 5. Discount\trate\tand\tAdd-on\trate\t(5\tpoints) Money Market Quotation\tbasis Instrument A Discount\trate B Discount\trate C Add-on\trate D Add-on\trate Assumed number of days\tin\tone\tyear 360 365 360 365 Quoted\trate 4.33% 4.36% 4.35% 4.45% Calculate\tthe\tbond\tyield\tequivalent\tfor\teach\tinstrument.\tWhich\tinstrument\toffers\tthe\tinvestor the\thighest\trate\tof\treturn\tif\tthe\tcredit\tis\tthe\tsame?\tRank\tthe\tbonds\tfrom\tthe\thighest\tto\tthe\tlowest rate\tof\treturn. 2