Question

In: Finance

Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....

Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $ 1 million of his inheritance to purchase 4 U.S. Treasury​ bonds:

1. An 8.59 %​, ​13-year bond​ that's priced at $ 1,092.14 to yield 7.47 %.

2. A 7.784 %​, ​15-year bond​ that's priced at $ 1023.51 to yield 7.52 %.

3. A​ 20-year stripped Treasury​ (zero coupon)​ that's priced at $ 197.76 to yield 8.27 %.

4. A​ 24-year, 7.49 % bond​ that's priced at $ 965.54 to yield 7.81 %. Note that these bonds are semiannual compounding bonds.

a. Find the duration and the modified duration of each bond.

b. Find the duration of the whole bond portfolio if Elliot puts $ 250,000 into each of the 4 U.S. Treasury bonds.

c. Find the duration of the portfolio if Elliot puts $ 380,000 each into bonds 1 and 3 and $ 120,000 each into bonds 2 and 4.

d. Which portfolio - b or c - should Elliot select if he thinks rates are about to head up and he wants to avoid as much price volatility as​ possible? Explain. From which portfolio does he stand to make more in annual interest​ income? Which portfolio would you​ recommend, and​ why? The duration and modified duration can be calculated using a​ spreadsheet, such as Excel. It gives the precise duration measure because it avoids the​ rounding-off errors, which are inevitable with manual calculations.

Bond​ 1: 13​ years, 8.59 %​, priced to yield 7.47 %.

The duration of this bond is ____ years.  ​

The modified duration of this bond is ____ years.  

Bond​ 2: 15​ years, 7.784 % priced to yield 7.52 %.

The duration of this bond is ____ years. 

The modified duration of this bond is ____ years.  

Bond​ 3: 20​ years, zero​ coupon, priced to yield 8.27 %.

The duration of this bond is ____ years.  

The modified duration of this bond is ____ years.  

Bond​ 4: 24​ years, 7.49 %​, priced to yield 7.81 %.

The duration of this bond is ____ years.  ​

The modified duration of this bond is _____ years.  ​

b. Find the duration of the whole bond portfolio if Elliot puts $ 250,000 into each of the 4 U.S. Treasury bonds.

The duration of this portfolio is ____ years.  

c. Find the duration of the portfolio if Elliot puts $ 380,000 each into bonds 1 and 3 and $ 120,000 each into bonds 2 and 4.

The duration of this portfolio is ____ years.  ​

d. Which portfolio - the portfolio in part b or the portfolio in part c - should Elliot select if he thinks rates are about to head up and he wants to avoid as much price volatility as​ possible?  ​(Choose the best answer​ below.)

A. He should invest in the portfolio in part b. The portfolio in part c has a higher duration than the portfolio in part b. If rates are about to​ rise, then it is safer to invest in the portfolio in part b​, because it would be less price volatile than the other portfolio.

B. He should invest in either portfolio. The portfolio in part c has a higher duration than the portfolio part b. If rates are about to​ rise, then it is equally safe to invest in either​ portfolio, because both portfolios would be equally price volatile.

C. He should invest in the portfolio in part c. The portfolio in part b has a higher duration than the portfolio in part c. If rates are about to​ rise, then it is safer to invest in the portfolio in part c​, because it would be less price volatile than the other portfolio.

D. He should invest in the portfolio in part c. The portfolio in part c has a higher duration than the portfolio in part b. If rates are about to​ rise, then it is riskier to invest in the portfolio in part b​, because it would be more price volatile than the other portfolio.

Please explain how to calculate in excel....

Solutions

Expert Solution

I have used the excel functions DURATION and MDURATION to solve this question. Please see the table below. Please be guided by the second row to understand the mathematics. The cells highlighted in yellow contain your answer. Figures in parenthesis, if any, mean negative values. All financials are in $.

Bond Settlement Maturity Coupon Yield Frequency Basis Duration Modified Duration
A B C D E F '=DURATION(A,B,C,D,E,F) '=MDURATION(A,B,C,D,E,F)
1 1/1/2019 1/1/2032 8.590% 7.47% 2 1 8.30 8.00
2 1/1/2019 1/1/2034 7.784% 7.52% 2 1 9.17 8.84
3 1/1/2019 1/1/2039 0.000% 8.27% 2 1 20.00 19.21
4 1/1/2019 1/1/2043 7.490% 7.81% 2 1 11.27 10.85

Bond​ 1: 13​ years, 8.59 %​, priced to yield 7.47 %.

The duration of this bond is 8.30 years.  ​

The modified duration of this bond is 8.00 years.  

Bond​ 2: 15​ years, 7.784 % priced to yield 7.52 %.

The duration of this bond is 9.17 years. 

The modified duration of this bond is 8.84 years.  

Bond​ 3: 20​ years, zero​ coupon, priced to yield 8.27 %.

The duration of this bond is 20 years.  

The modified duration of this bond is 19.21 years.  

Bond​ 4: 24​ years, 7.49 %​, priced to yield 7.81 %.

The duration of this bond is 11.27 years.  ​

The modified duration of this bond is 10.85 years.  ​

b. Find the duration of the whole bond portfolio if Elliot puts $ 250,000 into each of the 4 U.S. Treasury bonds.

Weight of each of the bond = w = 250,000 / 1,000,000 = 1/4 = 0.25

The duration of this portfolio = weighted average duration = 0.25 x (8.30 + 9.17 + 20.00 + 11.27) = 12.19 years

c. Find the duration of the portfolio if Elliot puts $ 380,000 each into bonds 1 and 3 and $ 120,000 each into bonds 2 and 4.

W1 = W3 = 380,000 / 1,000,000 = 0.38

W2 = W4 = 120,000 / 1,000,000 = 0.12

The duration of this portfolio is = 0.38 x 8.30 + 0.12 x 9.17 + 0.38 x 20.00 + 0.12 x 11.27 = 13.21 years

d. Which portfolio - the portfolio in part b or the portfolio in part c - should Elliot select if he thinks rates are about to head up and he wants to avoid as much price volatility as​ possible?  ​(Choose the best answer​ below.)

The correct answer is the first option i.e. option A. He should invest in the portfolio in part b. The portfolio in part c has a higher duration than the portfolio in part b. If rates are about to​ rise, then it is safer to invest in the portfolio in part b​, because it would be less price volatile than the other portfolio.


Related Solutions

Elliot Karlin is a 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the bank's investments department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In particular, Elliot intends to use $1million of his inheritance to purchase 4 U.S. Treasury bonds: 1. An 8.62% 13-year bond that's priced at $ 1,093.74 to yield 7.48%. 2. A 7.811%, 15-year bond that's priced at $...
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $1million of his inheritance to purchase 4 U.S. Treasury​ bonds: 1. An 8.62%​,​13-year bond​ that's priced at $1,094.61 to yield 7.47%. 2. A 7.771%​, ​15-year bond​ that's priced at $1017.84 to yield...
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $ 1 $1 million of his inheritance to purchase 4 U.S. Treasury​ bonds: 1. An 8.58%​, ​13-year bond​ that's priced at $1,090.45 to yield 7.48%. 2. A 7.865%​, ​15-year bond​ that's priced...
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $ 1million of his inheritance to purchase 4 U.S. Treasury​ bonds: 1. An 8.59 %​, ​13-year bond​ that's priced at $ 1,093.02to yield 7.46 %. 2. A 7.777 %​, ​15-year bond​ that's...
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $ 1 million of his inheritance to purchase 4 U.S. Treasury​ bonds: 1. An 8.69 %​, ​13-year bond​ that's priced at $ 1 comma 100.37 to yield 7.47 %. 2. A 7.821...
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $1 million of his inheritance to purchase 4 U.S. Treasury​ bonds: 1. An 8.59%​, 13-year bond​ that's priced at $1,091.27 to yield 7.48%. 2. A 7.795%​, 15-year bond​ that's priced at $1019.97...
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $ 1 million of his inheritance to purchase 4 U.S. Treasury​ bonds: 1. An 8.66 %​, ​13-year bond​ that's priced at $ 1,096.15 to yield 7.49 %. 2. A 7.776 %​, ​15-year...
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money....
Elliot Karlin is a​ 35-year-old bank executive who has just inherited a large sum of money. Having spent several years in the​ bank's investments​ department, he's well aware of the concept of duration and decides to apply it to his bond portfolio. In​ particular, Elliot intends to use $ 1 million of his inheritance to purchase 4 U.S. Treasury​ bonds: 1. An 8.68%​,​13-year bond​ that's priced at $1,099.55 to yield 7.47%. 2. A 7.852%​,​15-year bond​ that's priced at $ 1028.65$to...
Case: You have just inherited a large sum of money and you are trying to determine...
Case: You have just inherited a large sum of money and you are trying to determine how much you will get after retirement and how much you can spend now. For retirement you will deposit today (January 1,2015) 2,500,000 euros in a bank account paying 4.55% compounded annually. You do not plan on touching this deposit until you retire in five years (January 1, 2020) and you plan on living for 20 additional years and then drop dead on December...
George Johnson recently inherited a large sum of money; he wants to use a portion of...
George Johnson recently inherited a large sum of money; he wants to use a portion of this money to set up a trust fund for his two children. The trust fund has two investment options: (1) a bond fund and (2) a stock fund. The projected returns over the life of the investments are 8% for the bond fund and 20% for the stock fund. Whatever portion of the inheritance George finally decides to commit to the trust fund, he...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT