Question

A Treasury bond that settles on October 18, 2016, matures on March 30, 2035. The coupon rate is 6.30 percent and the bond has a yield to maturity of 5.85 percent. What are the Macaulay duration and modified duration? (Use the duration function in Excel to solve the problem. Do not round intermediate calculations. Round your answers to 4 decimal places.) Macaulay duration Modified duration

Answer 1

To calculate Macaulay, please use the below values in Duration function in excel, as shown in screenshot below:

SUM X fc =DURATION(B2,B3,B5,B6,B4) B C A D E F G H I J K L M 18 October 2016 30 March 2035 Function Arguments 2 Settlement date 3 Maturity Date 4. Payment Frequency 5 Coupon Rate 6 Yield 7 ? X 6.30% 5.85% DURATION Settlement B2 9 =DURATION(B2,B3,B5,B6,B4) ! Maturity Coupon Yld Frequency 1 1 1 1 = 42661 = 49398 = 0.063 = 0.0585 BG B4 = 11.2254097 Returns the annual duration of a security with periodic interest payments. Yld is the security's annual yield. Formula result = 11.2254097 Help on this function ок | Cancel

Macaulay duration = 11.23 years

To calculate modified duration, use Mduration function on excel and the same values, as shown in the screenshot below :

A10 T X for EMDURATION(B2,B3,B5,B6,B4) C D A А E F G H I J K L M 1 18 October 2016 30 March 2035 2 Settlement date 3 Maturity Date 4 Payment Frequency 5 Coupon Rate 6 Yield Function Arguments ? X 6.30% 5.85% MDURATION Settlement Maturity 1 = 42661 B3 1 = 49398 11.2254097 =MDURATION(B2,B3,B5,B6,B4) ! 1 Coupon Yld = 0.063 = 0.0585 B6 1 Frequency B4 = 10.60501625 Returns the Macauley modified duration for a security with an assumed par value of $100. Yld is the security's annual yield. Formula result = 10.60501625 Help on this function OK Cancel

Modified duration = 10.61 years