4. Duration Worksheet: Copy the information for the Walmart Inc, bond into cells B3 to B11 of the Duration worksheet, (Note: B11 is the actual yield from Method of Averages worksheet (this differs from previous two worksheets). (a) In column A (starting at cell A15), enter every coupon payment date for the Walmart Inc. bond (including the maturity date which is the final coupon date) using the EDATE(..) function in Excel (Note: Assume the first coupon is one period after the settlement purchase date.) (b) In cell B15, enter a formula that determines the first coupon payment for the bond. Complete the rest of the column B. [Note: Include the redemption value in the Inst payment (c) Complete column (starting at cell C15) which represents the number of compound periods since the settlement date for the bond. (a) In cell D15, enter a formula (using the actual yield and compound interest) for the discounted value to the settlement date) of the first coupon payment. Complete the rest of column D (e. find the discounted value of each coupon payment to the settlement date). (c) In cell E3, sum the cash flow discounted values in column D (you should get the price of the bond w we were sentially treating each coupon payment as a zero coupon bond and finding its price). (f) In cell E15, enter a formula for the percentage of the bood's purchase price that can be attributed to the discounted value of the first coupon payment. Complete the rest of column E for the remaining coupou payments (s) In cell F1s, enter a formula for the year of the first coupon payment (ie, 1/2 a year) times the percentage determined in cell E15 from part (f) for the bond (the result is called the weighted time). Complete the rest of column F for the remaining coupon payments. (1) In cell EA, sum the weighted times in column F to get what's called the duration of the boud. Note: In cell Es, use the DURATION(...) command to check that the result in part (1) is correct. 4. Duration Worksheet: Copy the information for the Walmart Inc, bond into cells B3 to B11 of the Duration worksheet, (Note: B11 is the actual yield from Method of Averages worksheet (this differs from previous two worksheets). (a) In column A (starting at cell A15), enter every coupon payment date for the Walmart Inc. bond (including the maturity date which is the final coupon date) using the EDATE(..) function in Excel (Note: Assume the first coupon is one period after the settlement purchase date.) (b) In cell B15, enter a formula that determines the first coupon payment for the bond. Complete the rest of the column B. [Note: Include the redemption value in the Inst payment (c) Complete column (starting at cell C15) which represents the number of compound periods since the settlement date for the bond. (a) In cell D15, enter a formula (using the actual yield and compound interest) for the discounted value to the settlement date) of the first coupon payment. Complete the rest of column D (e. find the discounted value of each coupon payment to the settlement date). (c) In cell E3, sum the cash flow discounted values in column D (you should get the price of the bond w we were sentially treating each coupon payment as a zero coupon bond and finding its price). (f) In cell E15, enter a formula for the percentage of the bood's purchase price that can be attributed to the discounted value of the first coupon payment. Complete the rest of column E for the remaining coupou payments (s) In cell F1s, enter a formula for the year of the first coupon payment (ie, 1/2 a year) times the percentage determined in cell E15 from part (f) for the bond (the result is called the weighted time). Complete the rest of column F for the remaining coupon payments. (1) In cell EA, sum the weighted times in column F to get what's called the duration of the boud. Note: In cell Es, use the DURATION(...) command to check that the result in part (1) is correct
