Question

Need help with excel. The right answers are shown. Dont know what to input to get those answers.

3) Assume that the answer in B20 is the price you must pay to purchase the bond today and take it as a data input in the following problem. Find the IRR of the bond investment. Refer to Question 1 for the other inputs. Period (0.5Year) Bond investment Expected cash flow from bond investment Incremental cash flow (Bond INV Output Using IRR function 4 10 $914.95 31.1331.13 31.13 31.13|$ 1,031.13 $914.95) 31.13 S 31.13 S 31.13S 31.13 S 31.13 S 31.13 S 31.13 S 31.13 S 31.13 S 1,031.13 4.170% Insight 1: IRR is the discount rate that yields a zero NPV for the investment. Prove it. Period (0.5Year) Cash Flows NPV of bond using IRR as discount rate Price of bond NPV of the investment 4 10 $ 31.13 $ 31.13$ 31.1331.13$ 31.13 31.13 31.13 31.13 $ 31.13$ 1,031.13 $914.95 $914.95 ($0.00) Insight 2: IRR is the opportunity cost of the investment. To prove it lets assume that we borrow $914.95 at the IRR to make the bond investment. Below is the amortization of the loan plus the cash flow from the bond If IRR is indeed the opportunity cost then by the end of the horizon everything should exactly cancel out leaving a zero end balance Par value End Balance ($921.98) ($929.30) ($936.93) ($944.87) Beginning balance Interest Coupon ($914.95)($38.15) $ 31.13 $ ($921.98)($38.45) $ 31.13 $ ($929.30)($38.75) $ 31.13 $ ($936.93) ($39.07)$ 31.13 $ ($944.87) ($39.40) $ 31.13 $ ($953.15) ($961.77)(S40.11) S 31.13 S ($970.75 (40.48) S 31.13 S ($980.10(S40.87) S 31.13 ($989.85)($41.28) 31.13 1,000.00 ($39.75) 5 31 ($961.77) ($970.75) ($989.85 0.00

Answer 1