$A2$

$f_x$ As the bond portfolio manager you are concerned about the impact of changing interest rates on your portfolio value. Use the information below and ex

$1$

Excel Assignment Due: February $25,2024$

As the bond portfolio manager you are concerned about the impact of changing interest rates on your portfolio value. Use

the information below and excel functions to determine the value of the current bond portfolio, the portfolio yield and the

portfolio duration. Assuming that insterest rate changes will impact all bonds equally, what is the value of the bond portfolio if interest rates increase by $0\mathrm{.}25\%$ or $0\mathrm{.}5\%?$ From the additional bonds provided, which bond, if added to the bond portfolio will help insulate the portfolio from interest rate risk?

$3$

$\backslash$table$[[$Issuer Name,Coupon,Coupons$/$Year$,$Maturity,S&P$,$Yield,Date,,Price,Face Value,$\backslash$table$[[$Quantity of Bonds in$],[$Portfolio$]],$Total Value,Portfolio Yield,Duration,Portfolio Duration$],[$Walmart$,7\mathrm{.}550\%,2,\frac{2}{15}\frac{?}{30},$AA$,4\mathrm{.}304\%,\frac{2}{15}\frac{?}{24},$$$,119\mathrm{.}43,$$$,100\mathrm{.}00,450,,,,],[$Wells Fargo,$4\mathrm{.}500\%,2,\frac{12}{15}\frac{?}{25},BBB+,4\mathrm{.}383\%,\frac{2}{15}\frac{?}{24},$$$,100\mathrm{.}09,100\mathrm{.}00,500,,,,],[$Ford Motors,$9\mathrm{.}950\%,2,\frac{2}{15}\frac{?}{32},BB+,7\mathrm{.}508\%,\frac{2}{15}\frac{?}{24},$$$,116\mathrm{.}00,100\mathrm{.}00,680,,,,],[$Johnson & Johnson,$6\mathrm{.}730\%,2,\frac{11}{15}\frac{?}{24},$AAA,$2\mathrm{.}384\%,\frac{2}{15}\frac{?}{24},$$$,103\mathrm{.}20,100\mathrm{.}00,200,,,,],[$Caterpillar$,0\mathrm{.}950\%,2,\frac{1}{10}\frac{?}{25},$A$,5\mathrm{.}284\%,\frac{2}{15}\frac{?}{24},$$$,96\mathrm{.}30,100\mathrm{.}00,800,,,,]]$

What is the value of the bond portfolio?

Answer:

What is the portfolio yield?

Answer:

What is the duration of the bond portfolio?

Answer:

If interest rates increase by $0\mathrm{.}25\%$ what is the value of bond portfolio?

$\backslash$table$[[$Issuer Name,Yield,Price,Total Value$]]$

Walmart

Wells Fargo

Ford Motors

Johnson & Johnson

Caterpillar

$9$ If interest rates increase by $0\mathrm{.}50\%$ what is the value of bond portfolio?

$\backslash$table$[[$Issuer Name,Yield,Price,Total Value$],[$Walmart$,,,]]$

Walmart

Wells Fargo

Ford Motors

Johnson & Johnson

Caterpillar

Answer:

If you wanted to reduce the interest rate sensitivity of your bond portfolio which of the following bonds would you buy? Why?

Issuer Name

Apple

Amazon

Macys

Coupon

Coupons$/$Year

Maturity

S&P $,$ Yield

$3\mathrm{.}250\%$

$2\mathrm{.}730\%$

$\frac{8}{8}\frac{?}{29}$

$\frac{4}{13}\frac{?}{24}$

AAt

$\backslash$table$[[$Yield$,$Date,Price$],[4\mathrm{.}587\%,\frac{2}{15}\frac{?}{24},$$$,92\mathrm{.}58],[4\mathrm{.}975\%,\frac{2}{15}\frac{?}{24},$$$,97\mathrm{.}51],[8\mathrm{.}301\%,\frac{2}{15}\frac{?}{24},$$$,69\mathrm{.}95]]$

Answer: