Calculate the coupon pmt for each yield for bond

Reference no: EM131073346

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

Reference no: EM131073346

Questions Cloud

What are some mechanisms for protecting the environment : What are some mechanisms for protecting the environment

What positions or people do you need to develop : What employee attributes do you need to develop to achieve your learning objectives? How will you build and maintain development methods?

Problem regarding the quality control : One of the philosophies mentioned in your text includes the strategy of Plan, Do, Check, and Act. Please keep this philosophy in mind when considering the information required below.

Change in profit if the hospital accepts the proposal : General Hospital, a not-for-profit acute care facility, has the following cost structure for its inpatient services: Fixed costs $946189 Variable cost per inpatient day $17 Charge (revenue) per inpatient day $90 The hospital expects to have a patient..

Calculate the coupon pmt for each yield for bond : 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..

About the invoice price of the bond : A bond with a coupon rate of 8% makes semiannual coupon payments on January 15 and July 15 of each year. The Wall Street Journal reports the ask price for the bond on January 30 at 100:08. What is the invoice price of the bond? The coupon period has ..

What could be responsible for this pattern : What could be responsible for this pattern

Examples of possible outcomes from a focus group : Explain the purpose of a focus group and provide three examples of possible outcomes from a focus group. List the components of STP and explain the importance of each component.

Futures contracts generally trade on organized exchange : One advantage of forward contracts is that they are default free. Futures contracts generally trade on an organized exchange and are marked to market daily. Goods are never delivered under forward contracts but are almost always delivered under futur..

User Account

All Pages