BOND PROJECT FIN445

Anonymous
timer Asked: Apr 29th, 2016

Question description

1.  [15 points]  First, calculate the value of a corporate bond using the information below. Do this calculation by handand show all of your work!  Second, convert the value to price; round to 3 decimals.

Settlement Date:  5/15/2016

Maturity Date:    5/15/2018

Coupon rate:  4.50%

UST Yield:  2.25%

Spread (bps):  125

Yield to Maturity:  Calculate from Above Information

2.  [15 points]  Calculate by hand on paper (a) the Macaulay duration and (b) the modified duration.  Show all of your work.

3.  [10 points]  Using the modified duration you just calculated, (a) estimate what the price change of the bond would be if the U.S. Treasury yield declines 50 basis points. (b) Now calculate the new price.  Do these calculations on paper.

4.  [20 points]  Using the format and information highlighted in Question #1 as a guide, create a Bond Price calculator in an Excel spreadsheet. Next, in the cell below the “Yield to Maturity” cell, create a cell for PRICE.  To the right of the “Price” label, insert Excel’s PRICE formula.

a.  Using the same inputs from question 1, use the Excel PRICE function to calculate the bond’s price.  Did you get the same answer that you calculated by hand?  Copy and Paste your spreadsheet into your project paper.

b.  Subtract 50 basis points from the U.S. Treasury yield in your Excel model.  (i) Copy and Paste your spreadsheet results into your project paper.  (ii) Compare this price to the new price you calculated in Question 3.  How do they compare?

5.   [10 points]  Reset your Excel model to the original information from question #1.  Now change the maturity date to 5/15/2021.  What happens to the price of the bond versus your answer from Question 1?  Explain in one paragraph what happened to the price when you changed the maturity date and why.  Copy and Paste your spreadsheet into your project paper.

6.  [30 points]  I have placed an Excel spreadsheet on Blackboard (PROJECT BOND FIN 445 2016 Spring.xlsx).  The spread data represents three sectors of the bond market:  Investment Grade U.S. corporate bonds, High Yield U.S. corporate bonds, and U.S. dollar-denominated Emerging Market Corporate bonds.  As an analyst, your job is to determine whether each of these sectors are fairly valued, overvalued, or undervalued.  You will do the following:

a.  Create a separate chart for each of the three data series.

b.  Calculate the average spread for each data series, and plot on the chart.

c.  Calculate +/- 1 standard deviation for each data series, and plot on the chart.

d.  Copy each chart into your project paper.

e.  Given where the current spread is (the last data point), give an explanation for each data series as to whether each sector today is a buy, sell, or wait (assume you don’t own any bonds, so “hold” is not an option).  Each explanation should be no more than half of one page.

f.  What other data or information that is not currently available to you might alter your buy/sell/wait decision?  Maximum one-half page.

g.  In one paragraph, rank the three bond sectors according to their relative value, i.e., if you had to make a choice, which would you buy 1st, 2nd, and 3rd?  Explain your answer.

You must turn in a printed copy in class the day the project is due.  You must also email a copy of your project to hwinvukc@gmail.com prior to the beginning of class the day the project is due.

Use ARIAL type font, 12 point, 1.5-spaced.  Be thorough with your answers.

All guidelines regarding exams, projects, and assignments contained in the syllabus apply to this Project.


Tutor Answer

(Top Tutor) Studypool Tutor
School: New York University
Studypool has helped 1,244,100 students
flag Report DMCA
Similar Questions
Hot Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors