FIN 310 Excel Assignment #2
• Using the provided "Bond Valuation" template, complete the following:
1. Save the template in the following format: lastname_firstname_Assign2.xlsx
2. Calculate the Coupon Pmt for each Yield for Bond A by referencing the appropriate cells in the Given section.
3. Calculate the Total Periods for Bond A by subtracting the Settlement Date from the Maturity Date then dividing that result by 365 then multiplying that result by the Coupon P/Y (P/Y = payments per year) to get total periods. Also, within these cells I want you to ROUND your result to ZERO decimal places by using the "=ROUND" function in Excel. This will give us nice even numbered periods.* if you can't get the =ROUND function to work, you may alternatively use the button in the toolbar to round to 0 decimals.
4. Calculate the Price of Bond A at each YTM using the "=PV" function in Excel (NOT the =PRICE function). Hint: You will want to use absolute references for all references to the Given info so you can copy and drag formula across from C12 to G12.
5. Calculate the Duration of Bond A using the "=DURATION" function in Excel for each of the given YTMs. Use appropriate absolute and relative references. This result will be measured in years. Use a "basis" setting of 0 (zero)
6. Calculate the Modified Duration (Mod-Duration) of Bond A using the "=MDURATION" function in Excel for each of the given YTMs. Use appropriate absolute and relative references. This result will be measured in years. Use a "basis" setting of 0 (zero)
7. Repeat steps 16 through 19 for Bond B as well making appropriate adjustments. Remember column H should be blank as it is a separator between Bond A and Bond B information.
8. Insert a 2-D Line Chart and move/resize it to fit from cell E17 to K37
9. On this line chart add two lines. One that plots the Price of Bond A and one that plots the Price of Bond B. Your Horizontal Axis should be the YTMs (2,6,10,14,18%. You can reference these from above).
10. Since we are using just a few data points we want to "smooth" the curves so apply the "Smoothed Line" setting to each of these lines. Hint: You will find this in the "Format Data Series" menu if you right click on the line.
11. You choose how to add labels to the chart and lines as you deem appropriate.
12. Answer the following questions (you may insert a textbox, add a separate worksheet, or complete these in a separate Word document).
a. Define Duration, Modified Duration and Bond Convexity. What are these used for and how do they differ?
b. What does Modified Duration suggest about the relationship between bond price and bond yields?
c. Describe how bond convexity affects this relationship.
d. What are the implications of bond convexity for estimating changes in bond prices?
• Overall format and presentation count towards your grade! Proofread for spelling errors, check for proper labels on your graph and that your columns are wide enough for all text to be visible.
• Submit your assignment to dropbox on Canvas