1. Allow the user to input the current price of the bond as a percentage of par with 3 decimal places (usewhole numbers so that 100.000 represents a bond trading at par or face value), the annual coupon rate, the settlement date (day traded), the maturity date, and the percentage increment to use in the table and graph for changes in the discount rate. All calculations in the remaining steps should reference these inputs.
2. Using the inputs, calculate the yield to maturity (YTM), maturity, Macaulay’s duration, modified
duration, and accrued interest (as of the settlement date) of the bond. You will find the yield() function
useful for calculating YTM, the yearfrac() function useful for calculating maturity, the duration()
function useful for calculating Macaulay’s duration, and the couppcd() function useful for calculating
accrued interest. Assume 2 payments per year and a 30/360 basis. Do not use the mduration() function for modified duration, as it is incorrect. Instead, calculate the modified duration as the duration divided by (one plus the YTM that is calculated) or MD = D / (1+ytm).
3. Create a table in which each row contains a change in interest rates, the value of the bond given the
change in interest rates, the actual percentage change in the bond value given the change in interest rates, and the percentage change in the bond price predicted by the duration model for the change in interest rates. You will find the price() function useful for calculating values. The change predicted by the duration model is the negative of the modified duration multiplied by the change in interest rates or
?PV/PV ? -MD × ?i. Your table should include a change of 0% and then +/- 1, 2, 3, and 4 times the
interest rate increment input from step one. For example, if the interest rate increment is 0.50%, the
interest rate changes in the table from top row to bottom row would be -2.00%, -1.50%, -1.00%, -0.50%, 0.00%, +0.50%, +1.00%, +1.50%, and +2.00%.
4. Create a line graph of the actual and predicted percentage changes in value, with appropriate formatting of the axes, labels on the x and y axes, title and legend, etc. In particular, keep the font sizes small, so that most of the graph space is the actual graph, not the labeling.
