Appendix: Bond Portfolio

Bond #	Issuer	Type	Rating	Par Value (mil)	Coupon	Maturity Date	Reference UST YTM	Bond Value Value (mil)
A	UST	MM	AAA	$1,000	N/A	3/1/2023	0.550%	$994.26
B	UST	MM	AAA	$1,250	N/A	3/15/2023	0.575%	$1,242.21
C	UST	MM	AAA	$750	N/A	3/20/2023	0.600%	$745.06
D	UST	CPN	AAA	$500	1.000%	9/1/2023	1.090%	$501.67
E	UST	CPN	AAA	$650	1.250%	3/1/2024	1.360%	$652.39
F	UST	CPN	AAA	$250	1.500%	6/30/2024	1.480%	$250.62
G	UST	CPN	AAA	$750	1.650%	3/15/2027	1.765%	$751.16
H	UST	CPN	AAA	$1,000	1.750%	6/15/2029	1.882%	$994.13
I	UST	CPN	AAA	$450	2.000%	6/15/2032	1.947%	$453.82
J	UST	CPN	AAA	$350	2.250%	3/15/2051	2.245%	$353.76

The following questions are related to a bond portfolio which is described in the Appendix. Please model up all the bonds in Excel, exactly the way we did in class, before attempting the problems. It is strongly recommended you build this model from scratch, like you will have to do on an exam, and not just use the models from class to solve the homework. While Bond Value is an output of your model, Bond Value has been provided here so that you can verify that your model is working properly before you attempt the questions. For all problems, assume a settlement date of 2/18/2022
12	What is the total market value of the portfolio (in millions $x,xxx.xx)?
13	What % of the total market value of the portfolio is money-market securities (expressed to two decimals xx.xx%)?
14	What % of the total market value of the portfolio is comprised of premium bonds (xx.xx%)?
15	What is the modified duration of the portfolio (expressed to two decimals x.xx)?
16	Using the portfolio duration, estimate the % change in the portfolio for a 1 bps change in YTM (xx.xx%)?
17	Please shock the entire yield curve down by -1 bp; put -1 into the Spread for every bond. How much does the portfolio gain (loss) in value (in millions, $x,xxx.xx and please enter as a negative number if portfolio lost value, positive if gained)?
18	Please shock the entire yield curve up by +10 bps; put 10 into the Spread for every bond. How much does the portfolio gain (loss) in value (in millions, $x,xxx.xx and please enter as a negative number if portfolio lost value, positive if gained)?
19	Leaving the curve shocked +10 bps, what is the new portfolio duration (x.xx)?
20	Please bring the curve back to its original state by setting spread back to 0. Looking at bond J only, what is the duration of bond J (x.xx)?
21	Based on the duration of bond J, estimate the % change in the portfolio for a -100 bps change in YTM (xx.xx%)?