5.11 Chapter 5 HW - Spreadsheet Mastery // Bond Valuation // Problem 2 Please fill out the tables & list it's formulas. The bolded areas also need to be filled. 
E G A B D E F H I J K 111 Problem 2: We want to calculate the Intemal Rate of Retum (IRR), or the Yield to Maturity (YTM), of a bond. Its par value is 112 $1,000, it matures in four years, and it pays a coupon rate of 8%. Payments are made semi-annually (total of 8 payments). 113 114 Given: Definition Notation: Inputs: 115 Par Value par 1,000 116 Maturity in Years 4 117 Coupon Rate CR 8% 118 Number of Payments per year 2 119 Cash Flows INT ? 120 Yield to Maturity YTM ? 121 t 122 123 124 125 126 127 Finance Concept: IRR = YTM = The rate of retur eamed on a bond if it is held to maturity. The YTM is the interest rate for which the PV of the bond cash flows (coupons and face value) equals the bond price. Numerical Solution: With unequal cash flows, the IRR or YTM can be found by trial-and-error, or by using a financial calculator or Excel Spreadsheet Solution: 128 129 130 131 132 133 t Definition Par Value Maturity in Years Coupon Rate Interest Number of Payments per year Internal Rate of Return Notation: Inputs: par 1,000 4 CR 8% INT ? 2 IRR ? 134 135 136 137 138 139 140 Note: There are 8 payments over a four year period. Each payment is 1/2 the annual coupon. A B D 11 LL G H 1 J K 142 143 144 Cash Flow Time Today 145 Payment Periods 0 1 2 Purchase -1000 146 40 147 2 40 148 3 40 149 4 40 150 5 40 6 151 152 153 7 40 40 1040 End of Year 2 > 8 (M + INT) IRR => 154 155 156 157 It is very easy to find the IRR of the cash flows by using the Excel Wizard function: Step 1: Step 2: 159 160 161 162 163 164 165 166 167 168 169 170 171 Place your cursor in cell G154 and open the function wizard. Go to financial, and click on IRR Place the cursor in the first empty box (values) and then drag down the cash flow column including the purchase price (a negative number) and the last payment interest + return of principal). Click OK. Alternatively: In cell G154 enter. =IRR(G145:G153) With the inputs given, your IRR should be 4%. By convention, the bond's YTM would be quoted as 2* 4%, or 8%. A B E F H 172 173 Test Your Skills: EJ Corp bond carries a 9 percent coupon, paid semi-annually. The par value is $1.000. and the bond matures in 12 years. If the bond currently sells for $905.50 (PV), what is its yield to maturity? 174 175 176 177 178 Enter PV as a negative number. Calculate equal 24 payments ($1,000*.09/2) and enter. Remember the last payment is the return of principal plus PMT. Use IRR(cash flows). 180 181 182 Cash Flow Time Today 183 Payment Periods 0 1 2 PV> 184 185 186 3 ma 187 4 188 5 189 6 190 7 8 191 192 9 193 10 194 11 195 12 196 13 197 14 198 15 199 16 200 17 201 18 202 19 203 20 204 21 205 22 23 206 207 Add FV to PMT=> 24 208 IRR => 209 210 211 The YTM is 2*IRR ===> 213 214 215 216 217
