PLEASE WORK IT ON EXCEL AND SHOW WHAT YOU FILL IN THE BOXEES AND HOW U ENTER IT .

A B D E F G H . I J ITU 111 Problem 2: We want to calculate the Internal Rate of Return (IRR), or the Yield to Maturity (YTM), of a bond. Its par value is $1 112 matures in four years, and it pays a coupon rate of 8%. Payments are made semi-annually (total of 8 payments). 113 Given: Notation: 114 115 116 117 118 119 120 121 Definition Par Value Maturity in Years Coupon Rate Number of Payments per year Cash Flows Yield to Maturity par t CR Inputs: 1,000 4 8% 2 ? ? INT YTM 122 123 Finance Concept: IRR = YTM = The rate of return earned 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. 124 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: Notation: 125 126 127 128 129 130 131 132 133 134 135 136 137 Definition Par Value Maturity in Years Coupon Rate Interest Number of Payments per year Internal Rate of Return par t CR INT Inputs: 1,000 4 8% ? 2 ? IRR 138 Note: There are 8 payments over a four year period. Each payment is 1/2 the annual coupon. 139 140 PI 142 143 Payment Periods Time Today Cash Flow -1000 0 Purchase 1 40 144 145 146 147 148 149 150 2 40 40 3 4 40 5 40 B D E F G H J 140 - 142 143 Payment Periods Time Today Cash Flow -1000 0 Purchase 1 40 2 40 40 3 4 40 40 144 145 146 147 148 149 150 151 152 153 154 155 156 157 5 6 40 40 7 8 End of Year 2 > (M + INT) 1040 IRR => 159 It is very easy to find the IRR of the cash flows by using the Excel Wizard function: Step 1: Step 2: 160 161 162 163 164 165 166 167 168 169 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%. 170 171 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 17 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). A B D E F G I ITS 173 Test Your Skills: EJ Corp bond carries a 9 percent coupon, paid semi-annually. The par value is $1,000, and th 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). 17 180 181 Payment Periods Time Cash Flow Today 0 PV> 1 2 3 4 5 6 7 8 9 10 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 11 12 13 14 15 16 17 18 19 20 21 22 23 207 Add FV to PMT ==> 24 208 IRR => 209 18 201 202 19 20 21 203 204 205 206 207 208 209 210 211 22 23 24 Add FV to PMT ==> IRR => 213 214 215 The YTM is 2*IRR ===>