# several bond calculations, Writing Assignment Homework Help

**Question description**

BOND PROJECT FIN 445

FALL 2015

This project will take you through several bond calculations so that you have a better understanding of bond dynamics and bond valuation.

You must turn in the project on the due date, both on paper and in electronic format. You must copy and paste your Excel screens as noted below into your project paper

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

**[15 points]**First, calculate the value of a corporate bond using the information below. Do this calculation**by hand**and 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*

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

**[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.

**[20 points]**Using the format 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 compare?

**[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.

**[30 points]**I have placed an Excel spreadsheet on Blackboard (PROJECT BOND FIN 445 2015 Fall.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 data series.

- 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 me an explanation
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.*for each data series*

- 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 1
^{st}, 2^{nd}, and 3^{rd}? Explain your answer.

## Tutor Answer

Hi MIAZZZZ, Please check the attached file for detail and let me know if you have any questions, thank you. Best, James,

BOND PROJECT FIN 445

1. [15 points] First, calculate the value of a corporate bond using the information

below. Do this calculation by hand and 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

The yield of bond is

2.25% +125*0.01% = 3.5%

Assume the par value of the bond is $1000. Present value of 1st coupon payment:

1000*4.5%/1.035 = 43.478

Present value of 2nd coupon payment

1000*4.5%/1.0352 = 42.008

Present value of principle payment

1000/1.0352 = 933.511

Market price of bond is the sum of the three:

45

45

1000

2

(1 0.035) (1 0.035)

(1 0.035) 2

43.478 42.008 933.511

PV

1018.997

Yield to Maturity: Calculate from Above Information

Yield to maturity = UST Yield: 2.25%+ spreading /10000 = 2.25%+1.25% = 3/5%

1. [15 points] Calculate on paper (a) the Macaulay duration and (b) the modified

duration. Show all of your work.

Macaulay duration

1 * PV 1 2 * PV 2 43.478 *1 (42.008 933.511) * 2

PV

1018.997

1.957 y

Modified duration

Macaulay duration

yield _ to _ maturity

1

number _ of _ coupon _ periods _ peryear

1.957

0.035

1

1

1.891

1. [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.

a.)

The yield now change to

y= 1.75%+1.25% = 3.00%

y=-0.5% = -0.005

Using the relationship

dP 1

= –modified duration.

dy P

dP

1.891 p 1.891 * 1018.997 1926.923

dy

P 1926.923 * y

1927 * (0.005)

$9.635

price increases by $9.635

b) from a) the price is

1018.997+9.635 = 1028.63

1. [20 points] Using the format 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.

See attach excel file sheet1

1. 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.

Settlement

date

5/15/2016

Maturity date

5/15/2018

Couple rate

4.50%

UST Yield

2.25%

Spread (bps)

125

Yield to

Maturity

Price

3.500%

$1,019.00

The above result are the same as we what we calculated by hand.

1. 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?

Settlement

date

5/15/2016

Maturity date

5/15/2018

Couple rate

4.50%

UST Yield

2.25%

Spread (bps)

75

Yield to

Maturity

Price

3.000%

$1,028.70

This price is $0.07 different from the one calculated in question 3 ($1028.63).

1. [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.

Settlement

date

5/15/2016

Maturity date

5/15/2021

Couple rate

4.50%

UST Yield

2.25%

Spread (bps)

125

Yield to

Maturity

Price

3.500%

$1,045.15

The price increases to $1045.15, which increases by $26.15. The bond price increase

when we change the matur...

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