The Excel file Immunization Using Individual Bonds contains information about three bonds. Use this data to:Yield to maturity (Expected/Current)7%  Number of Years to Future Liability8  Future Liability $     3,000.00           Bond 1Bond 2Bond 3Coupon rate8.00%9.000%7.00%Maturity92415Face value1,0001,0001,000Explain which bond you prefer to use to attempt to immunize this obligation which is due in 8 years.Analyze each bond’s performance in attempting to achieve immunizationPlease show work in excel and functions/equations used.

Question
Asked Dec 6, 2019
76 views

The Excel file Immunization Using Individual Bonds contains information about three bonds. Use this data to:

Yield to maturity (Expected/Current) 7%    
Number of Years to Future Liability 8    
Future Liability  $     3,000.00    
       
       
  Bond 1 Bond 2 Bond 3
Coupon rate 8.00% 9.000% 7.00%
Maturity 9 24 15
Face value 1,000 1,000 1,000

Explain which bond you prefer to use to attempt to immunize this obligation which is due in 8 years.

Analyze each bond’s performance in attempting to achieve immunization

Please show work in excel and functions/equations used.

check_circle

Expert Answer

Step 1

Bond 1:

Yield to maturity = 7%

Coupon Rate = 8%

 

Calculation of Bond duration using excel is as follows:

help_outline

Image Transcriptionclose

Coupon Payments 1 Period Face Value of Bond $80.00 $74.77 $0.00 S74.77 3 2 $0.00 S69.88 $139.75 4 3 $0.00 $65.30 $195.91 S0.00 $61.03 $244.13 4 $285.19 $0.00 $57.04 S0.00 $53.31 $319.84 $0.00 $49.82 $348.74 $0.00 $46.56 $372.49 10 S587.45 S5,287.04 12 Present Value of Cash Flow $1,065.15 | S7,267.85 6.82 6.38 11 9 $1,000.00 13 Macaulay Duration 14 Modified Duration 2.

fullscreen
Step 2

Formula used in the above table is as follows:

help_outline

Image Transcriptionclose

Coupon Payments Face Value of Bond Period 2 |=1000*8% |=C2/1.07^A3 |=C2/1.07^A4 |=C2/1.07^A5 |=C2/1.07^A6 |=C2/1.07^A7 |=C2/1.07^A8 |=C2/1.07^A9 |=C2/1.07^A10 |=B11+C2)/1.07^A11 |=A11*C11 |=SUM(C3:C11) =D12/C12 ЕC13Л.07 -АЗ*СЗ =A4*C4 =A5*C5 =A6*C6 =A7*C7 =A8*C8 =A9*C9 =A10*C10 з 1 4 2 5 3 9 7 10 8 1000 12 Present Value of Cash Flow 11 9 |=SUM(D3:D11) 13 Macaulay Duration 14 Modified Duration

fullscreen
Step 3

Bond 2:

Yield to maturity = 7%

Coupon Rate = 9%

 

Calculation of Bon...

help_outline

Image Transcriptionclose

Coupon Payments $90.00 Period Face Value of Bond S0.00 S0.00 $0.00 $84.11 $84.11 $157.22 3 S78.61 3 $73.47 $220.40 S0.00 S68.66 $274.64 4 S0.00 $64.17 $320.84 S0.00 S0.00 S0.00 $59.97 $359.82 8. $56.05 $392.33 9. $52.38 $419.05 10 S0.00 $48.95 $440.59 11 S0.00 $45.75 10 $457.51 12 $0.00 $42.76 11 $470.34 13 $39.96 12 S0.00 $479.53 14 $37.35 13 S0.00 $485.51 15 14 $0.00 $34.90 $488.65 16 $0.00 15 $32.62 $489.30 17 S0.00 $30.49 $487.78 16 18 S0.00 $28.49 $484.36 17 19 S0.00 S0.00 $26.63 $479.30 18 20 $24.89 $472.83 19 21 20 $0.00 $23.26 $465.15 22 S0.00 S0.00 S0.00 $1,000.00 27 Present Value of Cash Flow 28 Macaulay Duration $21.74 $456.46 21 23 $20.31 $446.91 22 24 $436.66 23 $18.99 25 $214.89 S5,157.36 S1,229.39 $14,426.66 11.73 10.97 24 26 29 Modified Duration 2. 5n 7.

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: Find the effective interest rate for a $20,000 for three years if the interest is compounded quarter...

A: Effective interest rate:Effective interest rate is also called as annual equivalent rate which is co...

question_answer

Q: What effect would the calculation performed have in terms of shareholder value? In other words, supp...

A: The increase in cash dividend would increase the dividend yield of the stock. For Year 2016, dividen...

question_answer

Q: A company expects dividends to grow at 18 percent per year for the next 10 years before leveling off...

A: The computation of stock price is as follows:  

question_answer

Q: cost of debt 8%       unlevered cost of capital 10%       systematic risk of asset 1.5       1...

A: As per our policy, since the question has multiple sub-parts,we will answer first three subpart of q...

question_answer

Q: Discuss the relationship between Credit View and Monetary Policy.

A: Monetary Policy:Monetary Policy is a policy that is planned and implemented by the central bank of a...

question_answer

Q: The simplified balance sheet for the Dutch manufacturer Rensselaer Felt (figures in € thousands) is ...

A: Cost of equity =12.75%Cost of equity will not be affected by capital reconstruction. It will remain ...

question_answer

Q: What is the weighted average cost of capital (WACC) for a company with 30% equity, 70% debt, an aver...

A: Weighted average cost of capital is the average return which the business is expected to pay its inv...

question_answer

Q: please answer c) only   Question 1. Consider a two-step binomial tree, where a stock that pays no di...

A: c.Strike Price = 106Spot or Current Price = 100Call Price = 23.73 Calculation of Price of a 2-year 1...

question_answer

Q: Mobray Corp. is experiencing rapid growth. Dividends are expected to grow at 25 percent per year dur...

A: Calculation of Projected Dividends:The projected dividends are $2.14.Since there is no dividends pro...