Please do in Excel. Thanks in advance!
Now 28. 0. Finds Idees Sast Yes Excel File Edit View Insert Format Tools Data Window Hala Sa 3 Wed Feb 3 3-55 PM Autore OFF 21 - TVMI Student Templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share U Comments X Cut Times New Roman - 10 - A A General www ad Aneurs Good News Copy Merge Center $ % Conditional Formu Calculation Check Exploratory Trus Lindal Insert Delta Format Sort & Formatting Table Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you loft off? No Add-in Problem Sorry, we couldn't find Sover.xlam. Is it possible it was moved, renamed or deleted? F72 B D E F H J L M N O P Q 1 1. Find the value of a 5.125% coupon bond with semi-annual payments, 5 years to maturity and a YTM of 7.21% 2 Inputs 3 Face value S1,000 4 Annual coupon rate 5.125% 5 Years to maturity 5 6 Number of payments per year 2 7 Coupon payment/Period $ $ 25.63 8 YTM 7.21% 9 Model 10 Calculation using a time line approach Year 1 Year 2 Year 3 Year 4 Year 5 11 Period (-0.5 Year) 0 1 3 4 s 6 7 8 9 10 12 Cash Flows $ 25.63 $ 25.63 S 25.63 $ 25.63 S 25.63 $ 25.63 $ 25.63 S 25.63 S 1,025.63 13 Present Value of Cash Flows (PVCFs) $ 24.73 $ 23.87 23.87 S S 23.04 $ 22.24 S 21.41 21.47 S 20.72 5 20.00 S 19.30 $ 18.63 S 719.75 14 Output 15 Sum of all PVCFS S913.76 16 Using NPV function 5913.76 17 Using PV function S913.76 Only this will work with #2 below 18 19 2. Create a table that shows the value of the bond at YTM of 1.2%, 2.5%, and 6.10% for annual, semi-annual, quarterly and monthly payments. 20 21 You must use the Data Table function in Excel to construct the following table. 22 Check your model above to make sure that the output in B17 updates automatically with a change in input in both B6 and B& 23 Consult the Excel Help menu for how to construct Data/Table in Excel. The table you are building here is called a Two-Way Table. 24 25 Compounding Interval / Period YTM Questions: 26 Compounding Interval/Period S913.76 5.0% 7.21% 9.0% can only use PV function in B17 for Cell B26. Why? 27 Annual 1 $1,005.41 $914.99 $849.28 28 Semi-Annual 2 $1.005.47 5913.76 S846.69 If you are correct then the cell in yellow must be identical to the cell in B17. Why? Is it a conincidence? 29 Quarterly 4 $1.005.50 $913.12 S845.35 30 Monthly 12 $1,005.52 $912.69 $ 844.44 You do not need to provide an answer to these questions. 31 32 25.63 $ TUN Band Valuation + P 1754 Now *28-O-q Finds Sast Yes Excel File Edit View Insert Format Tools Data Window Hala Sa 3 Wed Feb 3 3-55 PM Autore OFF 1- TVMI Student Templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share U Comments X Cut Times New Roman 10 - A A General www ad Good Neutral Aneurs Copy Merge Cerk $ Conditional Formal F Calon Check Cell Exploratory Trus Lindal Insert Dalene Formu Sort & Idees Formatting Table Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you loft off? No Add-in Problem Sorry, we couldn't find So verxlar. Is it possible it was moved, renamed or deleted? F27 A A B D E F G H J L M N O P 32 33 3. Assume that the answer in B16 is the price you must pay to purchase the bond today and take it as a data input in the following problem. 34 Find the IRR of the bond investment. Refer to Question 1 for the other inputs. 35 Period (-0.5 Year) 0 0 2 3 4 5 6 7 8 9 10 36 Bood investment (5913.76) 37 Expected cash flow from bond investment $ 25.63 $ 25.63 S 25.63 $25.63 S 25.63 S 25.63 $ 25.63 S 25.63 $ 25.63 S 1.025.63 38 Incremental cash flow (Bond INV) (8913.76) $25.63 $25.63 $25.63 $25.63 S25.63 $25.63 S25.63 $25.63 $25.63 $1,025.63 39 Output 40 Using IRR function 3.605% 41 42 Insight 1: IRR is the discount rate that yields a nere NPV for the investment. Prove it. 43 Period (-0.5Year) 0 2 3 4 5 5 6 7 8 9 10 44 Cash Flows $ 25.63 $ 25.63 S 25.63 $ 25.63 S 25.63 $ 25.63 $ 25.63 S 25.63 S 1,025.63 PV of bond using IRR as discount rate S913.76 46 Price of bond $913.76 47 NPV of the investment $0.00 48 49 Insight 2: IRR is the opportunity cost of the investment. 50 To prove it lets assume that we borrow $913.76 at the IRR to make the bond investment, 51 Below is the amortization of the loan plus the cash flow from the bond. 52 IF IRR is indeed the opportunity cost then by the end of the horizon everything should exactly cancel out leaving a zero cad balance. 53 Beginning balance Interest Coupon Par value End Balance 54 ($913.76) (S32.94) $25,63 $ (8921.07) 55 (5921.07) (S33.20) S 25.63 $ (S928.65) 56 ($928.65) (S33.48) $25.63 $ () (S936.50) 57 ($936.50) (833.76) $ 25.63 $ (8944.64) 58 (5944.64) (S34.05) S 25.63 $ ($953.07) 59 ($953.07) (S34.36) S 25.63 $ (S961.80) 60 ($961.80) (834.67) $ 25.63 $ (S970.85) 61 ($970.85) (835.00) $ 25.63 $ (S980.23) 62 (5980.23) (S35.34) S 25.63 S (8989.94) 63 ($989.94) (S35.69) S 25.63 $1,000.00 $0.00 25.63 $ TVM Band Valuation P -- + 175% Insert Format Tools Data Window Hala Sa 3 Wed Feb 3 3-55 PM Excel File Edit View Autore OFF 21 - TVM1 Student Templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share U Comments Times New Roman v 10 - A A General www Aneurs Now ad Good Neutral X Cut Copy F *28-O-q Merge Cerk $ -- Calon Check Exploratory Trus Conditional Formal Formatting Table Lindal Insert Delta Format Sort & Finds Idees Sast Yes No Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you loft off? Add-in Problem Sorry, we couldn't find So veram. Is it possible it was moved, renamed or deleted? F77 A F G H I J L M N 0 P P Q 59 60 61 62 63 ($953.07) (5961.80) ($970.85) ($980.23) ($989.94) B D (81436) $ 25.63 $ (S34.67) S 25.63 $ (S35.00) S 25.63 $ - (S35.34) 25.63 $ (S35.69) $25.63 $1,000,00 E (S961.80) (S970.85) (S980.23) (8989.94) $0.00 Bond Price $1,400.00 $1,200.00 $1,000.00 $800.00 $600.00 Bond Pric $400.00 $200.00 65 4. Create a chart with bond prices on the Y-axis and YTM along the X-axies. 66 Create the chart in the space below. You might find it more effective to use one-way data table to create the data for the chart. 67 YTM Bond Price 6B S913.76 69 1.00% $1,200.69 70 2.00% $1,147.99 71 3.00% $1.097.99 72 4.00% $1,050.53 73 6.50% S942.10 74 7.00% $922.03 75 7.21% S913.76 If you are 76 8.00% S883.41 77 9.00% S846.69 78 10.00% $ 5811.78 79 11.00% S778.58 80 81 82 83 84 85 86 87 88 89 90 $0.00 0 0.02 0.04 0.06 0.08 0.1 0.12 TUN Band Valuation + P -- + 175% Now 28. 0. Finds Idees Sast Yes Excel File Edit View Insert Format Tools Data Window Hala Sa 3 Wed Feb 3 3-55 PM Autore OFF 21 - TVMI Student Templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share U Comments X Cut Times New Roman - 10 - A A General www ad Aneurs Good News Copy Merge Center $ % Conditional Formu Calculation Check Exploratory Trus Lindal Insert Delta Format Sort & Formatting Table Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you loft off? No Add-in Problem Sorry, we couldn't find Sover.xlam. Is it possible it was moved, renamed or deleted? F72 B D E F H J L M N O P Q 1 1. Find the value of a 5.125% coupon bond with semi-annual payments, 5 years to maturity and a YTM of 7.21% 2 Inputs 3 Face value S1,000 4 Annual coupon rate 5.125% 5 Years to maturity 5 6 Number of payments per year 2 7 Coupon payment/Period $ $ 25.63 8 YTM 7.21% 9 Model 10 Calculation using a time line approach Year 1 Year 2 Year 3 Year 4 Year 5 11 Period (-0.5 Year) 0 1 3 4 s 6 7 8 9 10 12 Cash Flows $ 25.63 $ 25.63 S 25.63 $ 25.63 S 25.63 $ 25.63 $ 25.63 S 25.63 S 1,025.63 13 Present Value of Cash Flows (PVCFs) $ 24.73 $ 23.87 23.87 S S 23.04 $ 22.24 S 21.41 21.47 S 20.72 5 20.00 S 19.30 $ 18.63 S 719.75 14 Output 15 Sum of all PVCFS S913.76 16 Using NPV function 5913.76 17 Using PV function S913.76 Only this will work with #2 below 18 19 2. Create a table that shows the value of the bond at YTM of 1.2%, 2.5%, and 6.10% for annual, semi-annual, quarterly and monthly payments. 20 21 You must use the Data Table function in Excel to construct the following table. 22 Check your model above to make sure that the output in B17 updates automatically with a change in input in both B6 and B& 23 Consult the Excel Help menu for how to construct Data/Table in Excel. The table you are building here is called a Two-Way Table. 24 25 Compounding Interval / Period YTM Questions: 26 Compounding Interval/Period S913.76 5.0% 7.21% 9.0% can only use PV function in B17 for Cell B26. Why? 27 Annual 1 $1,005.41 $914.99 $849.28 28 Semi-Annual 2 $1.005.47 5913.76 S846.69 If you are correct then the cell in yellow must be identical to the cell in B17. Why? Is it a conincidence? 29 Quarterly 4 $1.005.50 $913.12 S845.35 30 Monthly 12 $1,005.52 $912.69 $ 844.44 You do not need to provide an answer to these questions. 31 32 25.63 $ TUN Band Valuation + P 1754 Now *28-O-q Finds Sast Yes Excel File Edit View Insert Format Tools Data Window Hala Sa 3 Wed Feb 3 3-55 PM Autore OFF 1- TVMI Student Templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share U Comments X Cut Times New Roman 10 - A A General www ad Good Neutral Aneurs Copy Merge Cerk $ Conditional Formal F Calon Check Cell Exploratory Trus Lindal Insert Dalene Formu Sort & Idees Formatting Table Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you loft off? No Add-in Problem Sorry, we couldn't find So verxlar. Is it possible it was moved, renamed or deleted? F27 A A B D E F G H J L M N O P 32 33 3. Assume that the answer in B16 is the price you must pay to purchase the bond today and take it as a data input in the following problem. 34 Find the IRR of the bond investment. Refer to Question 1 for the other inputs. 35 Period (-0.5 Year) 0 0 2 3 4 5 6 7 8 9 10 36 Bood investment (5913.76) 37 Expected cash flow from bond investment $ 25.63 $ 25.63 S 25.63 $25.63 S 25.63 S 25.63 $ 25.63 S 25.63 $ 25.63 S 1.025.63 38 Incremental cash flow (Bond INV) (8913.76) $25.63 $25.63 $25.63 $25.63 S25.63 $25.63 S25.63 $25.63 $25.63 $1,025.63 39 Output 40 Using IRR function 3.605% 41 42 Insight 1: IRR is the discount rate that yields a nere NPV for the investment. Prove it. 43 Period (-0.5Year) 0 2 3 4 5 5 6 7 8 9 10 44 Cash Flows $ 25.63 $ 25.63 S 25.63 $ 25.63 S 25.63 $ 25.63 $ 25.63 S 25.63 S 1,025.63 PV of bond using IRR as discount rate S913.76 46 Price of bond $913.76 47 NPV of the investment $0.00 48 49 Insight 2: IRR is the opportunity cost of the investment. 50 To prove it lets assume that we borrow $913.76 at the IRR to make the bond investment, 51 Below is the amortization of the loan plus the cash flow from the bond. 52 IF IRR is indeed the opportunity cost then by the end of the horizon everything should exactly cancel out leaving a zero cad balance. 53 Beginning balance Interest Coupon Par value End Balance 54 ($913.76) (S32.94) $25,63 $ (8921.07) 55 (5921.07) (S33.20) S 25.63 $ (S928.65) 56 ($928.65) (S33.48) $25.63 $ () (S936.50) 57 ($936.50) (833.76) $ 25.63 $ (8944.64) 58 (5944.64) (S34.05) S 25.63 $ ($953.07) 59 ($953.07) (S34.36) S 25.63 $ (S961.80) 60 ($961.80) (834.67) $ 25.63 $ (S970.85) 61 ($970.85) (835.00) $ 25.63 $ (S980.23) 62 (5980.23) (S35.34) S 25.63 S (8989.94) 63 ($989.94) (S35.69) S 25.63 $1,000.00 $0.00 25.63 $ TVM Band Valuation P -- + 175% Insert Format Tools Data Window Hala Sa 3 Wed Feb 3 3-55 PM Excel File Edit View Autore OFF 21 - TVM1 Student Templates Home Insert Draw Page Layout Formulas Data Review View Tell me Share U Comments Times New Roman v 10 - A A General www Aneurs Now ad Good Neutral X Cut Copy F *28-O-q Merge Cerk $ -- Calon Check Exploratory Trus Conditional Formal Formatting Table Lindal Insert Delta Format Sort & Finds Idees Sast Yes No Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you loft off? Add-in Problem Sorry, we couldn't find So veram. Is it possible it was moved, renamed or deleted? F77 A F G H I J L M N 0 P P Q 59 60 61 62 63 ($953.07) (5961.80) ($970.85) ($980.23) ($989.94) B D (81436) $ 25.63 $ (S34.67) S 25.63 $ (S35.00) S 25.63 $ - (S35.34) 25.63 $ (S35.69) $25.63 $1,000,00 E (S961.80) (S970.85) (S980.23) (8989.94) $0.00 Bond Price $1,400.00 $1,200.00 $1,000.00 $800.00 $600.00 Bond Pric $400.00 $200.00 65 4. Create a chart with bond prices on the Y-axis and YTM along the X-axies. 66 Create the chart in the space below. You might find it more effective to use one-way data table to create the data for the chart. 67 YTM Bond Price 6B S913.76 69 1.00% $1,200.69 70 2.00% $1,147.99 71 3.00% $1.097.99 72 4.00% $1,050.53 73 6.50% S942.10 74 7.00% $922.03 75 7.21% S913.76 If you are 76 8.00% S883.41 77 9.00% S846.69 78 10.00% $ 5811.78 79 11.00% S778.58 80 81 82 83 84 85 86 87 88 89 90 $0.00 0 0.02 0.04 0.06 0.08 0.1 0.12 TUN Band Valuation + P -- + 175%
