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
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
points] Calculate by hand
on paper (a) the Macaulay duration and
(b) the modified duration. Show all of your work.
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.
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.
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.
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
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.
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:
Create a separate chart for each of the three
Calculate the average spread for each data
series, and plot on the chart.
Calculate +/- 1 standard deviation for each data
series, and plot on the chart.
Copy each chart into your project paper.
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.
What other data or information that is not currently
available to you might alter your buy/sell/wait decision? Maximum one-half page.
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 firstname.lastname@example.org 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