Bond 1 Yield to Maturity10%Coupon rate14%Maturity (Years)12Face value1,000Construct a Two-Way Data Table to demonstrate the impact of the coupon rate and the time to maturity on the bond’s duration using:Coupon Rates of 0%, 5%, 10%, 15%, and 20%.Maturities of 2 years, 4 years, 6 years, 8 years, and 10 years.How is a bond’s duration impacted by varying the coupon rate?How is a bond’s duration impacted by varying the time to maturity?What implications would these impacts have for a bond investor if interest rates change?Please use excel to show work

Question
Asked Nov 19, 2019
16 views
Bond 1  
Yield to Maturity 10%
Coupon rate 14%
Maturity (Years) 12
Face value

1,000

  1. Construct a Two-Way Data Table to demonstrate the impact of the coupon rate and the time to maturity on the bond’s duration using:
    1. Coupon Rates of 0%, 5%, 10%, 15%, and 20%.
    2. Maturities of 2 years, 4 years, 6 years, 8 years, and 10 years.
  2. How is a bond’s duration impacted by varying the coupon rate?
  3. How is a bond’s duration impacted by varying the time to maturity?
  4. What implications would these impacts have for a bond investor if interest rates change?

Please use excel to show work 

check_circle

Expert Answer

Step 1

Hello. Since your question has multiple sub-parts, we will solve the first three sub-parts for you. If you want remaining sub-parts to be solved, then please resubmit the whole question and specify those sub-parts you want us to solve.

Step 2

Yield to Maturity (rate) = 10%

Face Value (fv) = $1,000

Coupon Rates = 0%, 5%, 10%, 15% and 20%

Note: Coupon Payment (pmt) is changes from the change in the coupon rate.

 

Two-way data table is prepared by using excel is as follows:

Price of the Bonds is calculated by using PV function of excel.

To open the "PV function" window - MS-Excel --> Formulas --> Financials --> PV.

help_outline

Image Transcriptionclose

Coupon Payments 1 Years to 2 Maturity1000*0% - 1000*15 % =1000 20% 1000 5% - 1000 10 % |=PV(10%,A3.B2,-1000) -PV(10%,A3,C2,-1000) PV(10%,A3,D2,-1000) -PV(10%,A3,E2,-1000) PV(10%,A3,F2,-1000) |-PV(10%,A4,B2,-1000) PV(10%,A4,C2,-1000) -PV(10%,A4,D2,-1000) -PV(10%, A4,E2,- 1000) -PV(109%,A4.F2,-1000) |-PV(10%,A5,B2,-1000) PV(10%,A5,C2,-1000) PV(10%,A5,D2,-1000)-PV(10%,A5,E2,-1000) PV(10%,A5,F2,-1000) |PV(10%,A6,C2,-1000)|=PV(10%,A6, D2,-1000) -PV(10%,A6,E2,-1000) -PV(10%,A6,F2,-1000) |-PV(10%,A7,F2,-1000) |-PV(10%,A$.F2,-1000) 3 0 42 5 4 |-PV(10%,A6,B2,-1000) |PV(10 %,A7,B2 ,-1000) |-PV(10%,A8,B2,-1000) PV(10%,AS,C2,-1000) PV(10%,A8,D2,-1000) PV(10%,A8,E2,-1000) 6 6 PV(10%,A7,C2,-1000) |-PV(10%,A7,D2,-1000) PV(10%,A7,E2,-1000) 7 8 8 10

fullscreen
Step 3

The result of the above ta...

help_outline

Image Transcriptionclose

B F Coupon Payments Years to 2 Maturity $.00 $50.00 $100.00 $150.00 $200.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $566.12 $1,000.00 3 $826.45 $479.34 2 $739.67 $652.89 4 $683.01 $366.03 $207.53 $49.04 4 $524.52 5 $564.47 $346.71 (S306.58) 6 $128.95 (S88.82) ($333.73) (S536.14) 6 $466.51 (S66.99) $199.76 (S600.48) (S843.37) 7 10 $385.54 $78.31 ($228.91) 8 st

fullscreen

Want to see the full answer?

See Solution

Check out a sample Q&A here.

Want to see this answer and more?

Solutions are written by subject experts who are available 24/7. Questions are typically answered within 1 hour.*

See Solution
*Response times may vary by subject and question.
Tagged in

Business

Finance

Related Finance Q&A

Find answers to questions asked by student like you
Show more Q&A
add
question_answer

Q: Jaime found a 3 bedroom house for sale. Listing price is $275,000. He placed a bid of $260,000 on th...

A: The buyer should think of various scenarios to the prevailing market conditionsFirst, he needs to de...

question_answer

Q: A proposed nuclear power plant will cost $2.7 billion to build and then will produce cash flows of $...

A: Part A & B:Calculation of NPV at Discount Rate of 3% and 16%:The NPV at discount rate of 3% is $...

question_answer

Q: Meyer & Co. expects its EBIT to be $115,000 every year forever. The firm can borrow at 7 percent...

A: EBIT = $115,000Cost of Equity = 13% or 0.13Tax Rate = 24% or 0.24 Calculation of the Value of the Un...

question_answer

Q: ​(Calculating project cash flows and​ NPV)   The Guo Chemical Corporation is considering the purchas...

A: Calculate the initial cost, annual after-tax cash flows, and terminal cash flow in year 10 as follow...

question_answer

Q: The Panama Canal Store has preferred stock that was selling last month for $15 per share. The divide...

A: Calculate the current dividend as follows:

question_answer

Q: 8.6 letters a b and c

A: Hi there, Thanks for posting the question. But as per Q&A guidelines, we should answer the first...

question_answer

Q: B. decreased; decreased. C. increased; decreased D. decreased; increased E. decreased; stayed the sa...

A: ) At the time when the loan was taken, exchange rate was 1.2 Swiss Franc per dollar.Therefore, a loa...

question_answer

Q: D. 7.69 percent E 8.26 percent 16. An increase in which of the following would increase the price of...

A: A call option is direct proportional to the stock volatility in the market. if the volatility increa...

question_answer

Q: What must be the price of a $2,000 bond with a 5.7% coupon rate, annual coupons, and 30 years to mat...

A: The price of a bond consists present value of all future coupon payments and the redemption payment....