I need to do this homework on Excel. This this Finance course

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 A B Question 1 Bond Pricing C D E F G This queshon asks you to calculate the current price of a bond using four dierent methods: three Excel funchons (PV, PRICE, NPV) and annuity and PV factors. You should get the same answer for all four methods. Addihonally, this queshon asks you to do this on the assumphon of (a) annual interest payments and (b) semi-annual interest payments. H I J K a. Bond pricing with annual Interest Bond paying annual interest with the following attributes: YTM NPV Year Maturity 10 Number of years 1 Coupon Rate 0.05 Annual rate in decimal form 2 Par Value $1,000 3 Coupon $50 Annual coupon 4 YTM 0.04 Annual rate in decimal form 5 Redemption 100 Nothing fancy, what value goes here? 6 Frequency 1 Recall: annual payments! 7 Price using PV and PRICE functions: 8 Method 1 PV ($1,081.11) PV(..) function. The prompt "rate" means YTM. The function returns a negative value. 9 Method 2 PRICE $1,081.11 Here, "rate" means coupon rate. Function yields price per $100 FV, so have to multiple by 10 10 You'll have to use two dates ten years apart, e.g. DATE(1,1,2014) and DATE (1,1,2024) Annuity and PV factors for this bond: Annuity fac. 8.110896 BKM p. 299-300. Some algebra. PV factor 0.675564 For "r" and "T" refer to appropriate input cell in above, not 0.05 and 10 Price using the annuity and PV factor PV of coup. $405.54 BKM p. 299-300. Some algebra. PV of FV $675.56 Method 4 Total PV $1,081.11 YTM b. Bond pricing with semi-annual interest NPV An otherwise similar bond paying semi-annual interest: Half-year Method 3 1 No. Periods 20 Number of half years 2 Coupon Rate 0.05 Annual rate 3 Par Value $1,000 4 Coupon $25 Semi-annual coupon in dollar terms 5 YTM 0.04 Annual rate in decimal form 6 Redemption 100 Again, nothing fancy 7 Frequency 2 Now, this changes 8 Price using PV and PRICE functions: 9 Method 1 PV ($1,081.76) The "rate" is now YTM per half year, so divide your input by 2. 10 Method 2 PRICE $1,081.76 11 12 Annuity and PV factors for this bond: 13 14 Annuity fac. 16.351433 Tricky part: you need semi-annual 15 PV factor 0.672971 rates and the new number of periods. 16 17 Price using the annuity and PV factor 18 19 PV of coup. $408.79 20 PV of FV $672.97 Method 4 Total PV $1,081.76 Method 3 Queshon 1.1: The bond paying semi-annual interest has a slightly higher PV, PRICE or NPV. Why? ANSWER: Queshon1.2: Both bonds are examples of "premium" bonds. What does this mean? Explain why they are premium bonds. ANSWER: Queshon1.3: If the applicable YTM increases to 0.06, will they shll be premium bonds? Why or why not? ANSWER: . Question 2 Accrued Interest N 0.04 $1,081.11 Use the NPV function Flow $50 $50 $50 $50 $50 $50 $50 $50 $50 $1,050 0.04 $1,081.76 "Rate" is half-year yield Flow $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $25 $1,025 24-Nov-14 1-Sep-24 0.05 0.04 100 2 24-Dec-14 1-Sep-24 0.05 0.04 100 2 108.072 53 181 0.732 108.804 108.015 84 181 1.160 109.175 107.959 114 181 1.575 109.534 Use the PRICE function Use COUPDAYBS Use COUPDAYS Some algebra Simple addition Queshon 2.1: In this case the at or quoted price of the bond declines over hme, but the invoice price increases. Explain why this is so. Answer: Queshon 2.2- The invoice price of this bond will experience a large movement between February 25 and March 5, 2015. How big is the change? Be sure to explain whether it is posihve or negahve. ANSWER: it Queshon 2.3: What is the at or quoted price of this bond on August 31, 2024 (the day before maturity)? Answer: Queshon 2.4: What will be the invoice (or sales or dirty) price of the bond that day? ANSWER: Question 3 Callable Corporate Bond This queshon asks you to conrm the results in Example 10.6 of BKM, but using Excel. Assume a serlement date of October 15, 2014. Settlement date Maturity/call date Annual coupon rate Bond price Redemption Par Value Semiannual coupon Yield Question 4 Perpetuity 24-Oct-14 1-Sep-24 0.05 0.04 100 2 Flat price (% of par) Days since last coupon Days in coupon period Accrued interest Invoice price M This is based on Spreadsheet 10.1 in BKM, but uses the bond from the previous queshon. Recall: Invoice price = at price + accrued interest We assume that this ten-year bond has a maturity date of Sept 1, 2024. What will be its at price, accrued interest and invoice price at the following serlement dates? Settlement date Maturity date Annual coupon rate Yield to maturity Redemption value(% of par) Coupon payments per year L Yield to: Maturity Call 15-Oct-14 15-Oct-14 15-Oct-44 15-Oct-24 0.08 0.08 115 115 100 110 $1,000 $1,000 $40 $40 0.0682 0.0664 Use the YIELD function. Hint: what is the redemption value for the callable bond? Corporahons have been issuing very long-term bonds, some with terms-to-maturity of one-hundred years. (The most famous being the "Mickey Mouse" bond issued by Disney Corp.). This queshon asks you to parse the sources of value for such a bond. Queshon 4.1: How much would you pay to receive a certain $100 per year for 100 years? Assume a 10% discount rate. Hint: use the annuity factor. T--> ? Queshon 4.2: How much would you pay to receive a certain $100 per year forever? Assume a 10% discount rate. Hint: What happens to the annuity factor as Queshon 4.3: What is the value of $100 forever, starhng 100 years from now, also at 10%? One simple subtrachon, but also verify with some algebra. Q 4.1 $100/year for 100 years at 10% Q 4.2 $100/year forever at 10% Q 4.3 $100/year forever at 10% starting in 100 years O P Q GENERAL INSTRUCTIONS: Assumed or given numbers are currently shown in blue font and yellow background (or should be shown that way aler you have entered them.) Gray cells are ones the you need to ll in. Green background like this is for your guidance only. Do not include it in what you hand in. All numerical cells not in blue font should contain a cell reference (e.g., = F11), equahon (e.g. = A10+B10) or funchon (e.g., =AVERAGE(F10:F15). No "hardcoded" numbers! Keep the cell locahons as shown. For example your Cell C41 should have the YTM of the bond paying semi-annual interest. Formapng and apprearance count. $999.93