Berk DeMarzo. Harford. Problem 6-2 5. v8 Project Description: M8)lnhisproblem, you wit: a) calculate te annualized rate ofreturn of an investment when the YTM is 6%; b) calculate the annualized rateofreturn ofthe investment when the YTM is 7%; c) calculate the amazed rate ofretum ofhemestment when the VTM is 5%; and d) argue whether your investment is risk free if you plan to sel it before maturity Steps to Perform: Points Possible Step Instructions 1Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copylpaste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usualily the Given Data section. Start Excel. Download and open the workbook named: Berk DeMarzo Harford Problem 6-25 Start. In cell D17, by using cell references, calculate the purchase price of the bond Note: The output of the expression or function you typed in this cell is expected as a posibive number. 2In cell D18, by using cel references,calculate the number of periods remaining unti maturity 3In cel D19, by using cel nelterances, calculate the price of the bond when sold (under YTM 1). Note: The outout of the expression or function you typed in this cell is expected as a positive number 4In cell 020, by using cel references, calculate the rate of neturn on the investment Note: Use an expression simlar to Equation (6.2) to compute the rate of return n cell D26, by using cell references, calculate the price of Note: The output of the expression or function you typed in this coell is expected as a positive 5 i the bond when sold (under YTM 2) incet 27, by using cell references, iate the rase of ruum, on the investnent. Note: Use an expression simlar to Equation (6.2) to compute the rate of retum MacBook Pro %Cut Times New ...- 14 ' A^ A, Wrap - Copy Format B I U .AEMerge Preblem 6-25 Suppose you purchase a 30-year, zero-coupon bond with a yield to maturity of 6% You hold the bond for five years before selling it. Note: assume $100 face value Complete the steps below wsing cell roferences to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formla across a row or down a column,an abrolute cell reference or a mixed cell roference may be preferred. If'a specifie Excel Junction is to be used, the directions will specify the use of that function. Do not pe in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below In all cases, unless otherwise directed, wse the earliest appearance of the data in your formxlas, uually the Given Data section 1r the bond's yield to maturity is 6% when you sell it.what is the annualized rate ofretum of your investment? If the bond's yield to maturity is 7%when you sell it, what is the annualized rate ofretum ofyour investment? lfthe bond's yield to maturity is 5% when you sell it, what is the annualized rate ofretum ofyour investment? Bven ifa bond has no chance of default, is your investmment risk free if you plan to sell it before it matures ? Explain. Maturity (years) Face value Yield to maturity Holding period (years) . Ifthe bond's yield to maturity is 6% when you sell it, what se annualized rate ofretum ofyour investment? Purchase price Maturity wben sold (years) Bond price when sold Rate of returs b. lf the bond's yield to maturity i, 7% when you sell it, what the annualized rate ofreturn of your investment? Yield to maturity Bond price when sold Raie of retum irthe bond's yield to-turity . S% when you sell it, wh-is the annualized rate ofreturn of your investment? Yield to maturity Bond price when sold Rate of retore Fren if a bond has no chance of default, is your investment risk free if yeu plan to sell it befoce it matures? Explain If you sell prior to maturity,you are exposed to the risk that the d. may change 6-25 5% Yield to maturity Bond price when sold Rate of retunm even ifa bond has no chance of default, is your investment risk free if you plan to sell it before it matures? Bxplain. may change If you sell prior to maturity, you are exposed to the risk that the 1Requirements 1 In cell D17,by using cell references,calculate the purchase price of the bond (l pt.), Note: The output of the expression or function you typed in this cell is expected as a positive number In cell DIS,by using cell references,calculate the number of periods remaining until maturity ( pt.) In cell D19, by using cell references,calculate the price of the bond when sold (under YTM 1)( pt.). Note: The output 2 of the expression or function you typed in this cell is expected as a positive number similar to Equation (6.2) to compute the rate of return. of the expression or funetion you typed in this cell is expected as a positive number 4 In cell D20, by using cell references, calculate the rate of retum on the investment (1 pt.). Note: Use an expression 5 In cell D26, by using cell references, calculate the price of the bond when sold (under YTM 2)(l pt). Note: The output cell D27.by using cell references, calculate the rate of return on the investment (l pt.).nge: Use an expression similar to Equation (6.2) to compute the rate of return. of the expression or function you typod in this cell is expected as a positive number. imilar to Equation (6.2) to compute the rate of retum In cell F38, type either coupon 3,by using cell referemces, calculate the price of the bond when sold (under YTM 3) l pt) Note: The output 8 In cell D34, by using cell references, calculate the rate of retum on the investment (l pt.) Note: Use an expression rate or YTM as the main risk factor when selling a bond before maturity ( pt.). 6-25 Ready