5. Macaulay Corporation, a fixed-income securities consulting firm, issued bonds with the following characteristics: a) Using the following template, calculate this bond's Macaulay Duration D. b) Using the algebraic formula, calculate this bond's Duration D. Hint: you should get the same answer as [a]. D=y1+yc[(1+y)t1]+y(1+y)+t(cy) where y=periodicmarketratec=periodiccouponratet=numberofperiods y=c=t= D=(a) c) Using the Excel formula, calculate this bond's Macaulay Duration D and Modified Duration D*. PS: the D answer here should be the same as that in [a] and [b]. "Macaulay" DURATION (Settlement Date, Maturity Date, Coupon Rate, Market Rate, Frequency of Coupon PMTs, 0) Notes: - For most classroom purposes, set Settlement Date to "01/01/2020". Then set Maturity Date to Settlement Date "plus number of years of maturity of the bond", e.g. "01/01/2050" if it is a 30-year bond. - Frequency of Coupon PMTs refer to the number of coupon payments per year, e.g. "2" for semi-annual, etc. - The last parameter, "0", refers to the type of day count basis, e.g. US (NASD) 30/360, ActuaV365, etc. - For more info on this formula, use Excel's Help and search for "Duration". - If this function is not available on your Excel, nu the Setup program to install the Analysis ToolPack. After you install the Analysis ToolPack, you must enable it by using the Add-Ins command on the Tools menu. Refer to Excel's Help for detail instruction. - The problem with putting " 1/1/2020 and "1/1/2050" is that it makes its use inflexible when you are using maturity (in number of years) as one of your parameters. One solution is to introduce two cells to your calculation: [i] enter Settlement Date [i] auto-calculate its Maturity Date Then use both in the parameters of the above Excel formula. - PS: Use "Mduration" with same parameters for calculating Modified Duration D*. Autocale Using Excel formula "DURATION", D = use Excel formula and its parameters to get the D value Using Excel formula "MDURATION", D* = use Excel formula and its parameters to get the D value