excel questions due in 4 hours, 1 page double spaced.

profilelawrence5567
FIFinalSemesterProjectSpring2022033022.xlsx
Home>Business & Finance homework help>Accounting homework help>excel questions due in 4 hours, 1 page double spaced.

Base Case & Scenario

#1 ORIGINAL PORTFOLIO: BASE CASE #1 ORIGINAL PORTFOLIO: BASE CASE
I. Spreads Market Spreads Base Case Spreads Spread Changes
AA A BBB AA A BBB AA A BBB
Spreads to UST Short-Term: 0.60% 0.85% 1.10% 0.60% 0.85% 1.10% 0.00% 0.00% 0.00%
Spreads to UST Intermediate-Term: 0.70% 0.95% 1.20% 0.70% 0.95% 1.20% 0.00% 0.00% 0.00%
Spreads to UST Long-Term: 0.90% 1.15% 1.40% 0.90% 1.15% 1.40% 0.00% 0.00% 0.00%
II. Portfolio II. Portfolio Individual Contr to Individual Contr to
V V Bond Portfolio Bond Portfolio
Issue Settlement Maturity Credit Coupon Payment Payment UST Spread Clean Last Next # of days # of days # of days Issue Settlement Maturity Credit minus plus Duration Duration Convexity Convexity
Name Date Date Rating Rate Frequency Basis Bench to UST Yield Price Coupon Coupon in Coupon from Last to Next Accrued Full Par Market Name Date Date Rating
Payment Payment Period to Settle Coupon Interest Price Amount Value Δy = 0.20%
UST Short 4/5/22 2/15/24 AAA 2.875% 2 1 2.890% 99.97093 2/15/22 8/15/22 181 49 132 0.0039 99.9748 50,000,000 49,987,409 UST Short 4/5/22 2/15/24 AAA 100.3363 99.6150 1.8035 0.1212 2.0877 0.1403
UST Inter 4/5/22 2/15/29 AAA 3.000% 2 1 3.050% 99.69018 2/15/22 8/15/22 181 49 132 0.0041 99.6942 50,000,000 49,847,122 UST Inter 4/5/22 2/15/29 AAA 100.9312 98.4745 6.1606 0.4127 21.6353 1.4495
UST Long 4/5/22 2/15/52 AAA 3.375% 2 1 3.360% 100.27862 2/15/22 8/15/22 181 49 132 0.0046 100.2832 50,000,000 50,141,595 UST Long 4/5/22 2/15/52 AAA 104.1383 96.6165 18.7512 1.2637 234.8898 15.8302
A 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 A 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386
B 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 B 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386
C 4/5/22 2/15/24 A 3.625% 2 1 2.875% 0.850% 3.725% 99.81817 2/15/22 8/15/22 181 49 132 0.0049 99.8231 50,000,000 49,911,537 C 4/5/22 2/15/24 A 100.1807 99.4671 1.7873 0.1199 2.0568 0.1380
D 4/5/22 2/15/24 BBB 4.000% 2 1 2.875% 1.000% 3.875% 100.21900 2/15/22 8/15/22 181 49 132 0.0054 100.2244 50,000,000 50,112,208 D 4/5/22 2/15/24 BBB 100.5824 99.8681 1.7818 0.1200 2.0472 0.1379
E 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 E 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017
F 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 F 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017
G 4/5/22 2/15/29 A 3.875% 2 1 3.000% 0.950% 3.950% 99.54915 2/15/22 8/15/22 181 49 132 0.0052 99.5544 50,000,000 49,777,196 G 4/5/22 2/15/29 A 100.7521 98.3732 5.9739 0.3997 20.6891 1.3842
H 4/5/22 2/15/29 BBB 4.125% 2 1 3.000% 1.200% 4.200% 99.55250 2/15/22 8/15/22 181 49 132 0.0056 99.5581 50,000,000 49,779,043 H 4/5/22 2/15/29 BBB 100.7455 98.3870 5.9224 0.3962 20.4301 1.3669
I 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 I 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036
J 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 J 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036
K 4/5/22 2/15/52 A 4.375% 2 1 3.375% 1.150% 4.525% 97.55146 2/15/22 8/15/22 181 49 132 0.0059 97.5574 50,000,000 48,778,692 K 4/5/22 2/15/52 A 100.8353 94.4298 16.4145 1.0762 192.6335 12.6295
L 4/5/22 2/15/52 BBB 4.625% 2 1 3.375% 1.400% 4.775% 97.62076 2/15/22 8/15/22 181 49 132 0.0063 97.6270 50,000,000 48,813,509 L 4/5/22 2/15/52 BBB 100.8109 94.5870 15.9382 1.0457 184.1992 12.0851
Portfolio Total 750,000,000 744,006,132 8.2182 74.6494
#2 ORIGINAL PORTFOLIO: BASE CASE AFTER SPREADS CHANGE. #2 ORIGINAL PORTFOLIO: BASE CASE AFTER SPREADS CHANGE.
I. Spreads Market Spreads Base Case Spreads Spread Changes
AA A BBB AA A BBB AA A BBB
Spreads to UST Short-Term: 0.60% 0.85% 1.10% 0.60% 0.85% 1.10% 0.00% 0.00% 0.00%
Spreads to UST Intermediate-Term: 0.70% 0.95% 1.20% 0.70% 0.95% 1.20% 0.00% 0.00% 0.00%
Spreads to UST Long-Term: 0.90% 1.15% 1.40% 0.90% 1.15% 1.40% 0.00% 0.00% 0.00%
II. Portfolio II. Portfolio Individual Contr to Individual Contr to
V V Bond Portfolio Bond Portfolio
Issue Settlement Maturity Credit Coupon Payment Payment UST Spread Clean Last Next # of days # of days # of days Issue Settlement Maturity Credit minus plus Duration Duration Convexity Convexity
Name Date Date Rating Rate Frequency Basis Bench to UST Yield Price Coupon Coupon in Coupon from Last to Next Accrued Full Par Market Name Date Date Rating
Payment Payment Period to Settle Coupon Interest Price Amount Value Δy = 0.20%
UST Short 4/5/22 2/15/24 AAA 2.875% 2 1 2.890% 99.97093 2/15/22 8/15/22 181 49 132 0.0039 99.9748 50,000,000 49,987,409 UST Short 4/5/22 2/15/24 AAA 100.3363 99.6150 1.8035 0.1212 2.0877 0.1403
UST Inter 4/5/22 2/15/29 AAA 3.000% 2 1 3.050% 99.69018 2/15/22 8/15/22 181 49 132 0.0041 99.6942 50,000,000 49,847,122 UST Inter 4/5/22 2/15/29 AAA 100.9312 98.4745 6.1606 0.4127 21.6353 1.4495
UST Long 4/5/22 2/15/52 AAA 3.375% 2 1 3.360% 100.27862 2/15/22 8/15/22 181 49 132 0.0046 100.2832 50,000,000 50,141,595 UST Long 4/5/22 2/15/52 AAA 104.1383 96.6165 18.7512 1.2637 234.8898 15.8302
A 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 A 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386
B 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 B 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386
C 4/5/22 2/15/24 A 3.625% 2 1 2.875% 0.850% 3.725% 99.81817 2/15/22 8/15/22 181 49 132 0.0049 99.8231 50,000,000 49,911,537 C 4/5/22 2/15/24 A 100.1807 99.4671 1.7873 0.1199 2.0568 0.1380
D 4/5/22 2/15/24 BBB 4.000% 2 1 2.875% 1.000% 3.875% 100.21900 2/15/22 8/15/22 181 49 132 0.0054 100.2244 50,000,000 50,112,208 * D 4/5/22 2/15/24 BBB 100.5824 99.8681 1.7818 0.1200 2.0472 0.1379
E 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 E 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017
F 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 F 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017
G 4/5/22 2/15/29 A 3.875% 2 1 3.000% 0.950% 3.950% 99.54915 2/15/22 8/15/22 181 49 132 0.0052 99.5544 50,000,000 49,777,196 G 4/5/22 2/15/29 A 100.7521 98.3732 5.9739 0.3997 20.6891 1.3842
H 4/5/22 2/15/29 BBB 4.125% 2 1 3.000% 1.200% 4.200% 99.55250 2/15/22 8/15/22 181 49 132 0.0056 99.5581 50,000,000 49,779,043 H 4/5/22 2/15/29 BBB 100.7455 98.3870 5.9224 0.3962 20.4301 1.3669
I 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 I 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036
J 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 J 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036
K 4/5/22 2/15/52 A 4.375% 2 1 3.375% 1.150% 4.525% 97.55146 2/15/22 8/15/22 181 49 132 0.0059 97.5574 50,000,000 48,778,692 K 4/5/22 2/15/52 A 100.8353 94.4298 16.4145 1.0762 192.6335 12.6295
L 4/5/22 2/15/52 BBB 4.625% 2 1 3.375% 1.400% 4.775% 97.62076 2/15/22 8/15/22 181 49 132 0.0063 97.6270 50,000,000 48,813,509 L 4/5/22 2/15/52 BBB 100.8109 94.5870 15.9382 1.0457 184.1992 12.0851
Portfolio Total 750,000,000 744,006,132 8.2182 74.6494
#3 ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: BEFORE SPREADS CHANGE. #3 ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: BEFORE SPREADS CHANGE.
I. Spreads Market Spreads Base Case Spreads Spread Changes
AA A BBB AA A BBB AA A BBB
Spreads to UST Short-Term: 0.60% 0.85% 1.10% 0.60% 0.85% 1.10% 0.00% 0.00% 0.00%
Spreads to UST Intermediate-Term: 0.70% 0.95% 1.20% 0.70% 0.95% 1.20% 0.00% 0.00% 0.00% OPTIMIZATION USING
Spreads to UST Long-Term: 0.90% 1.15% 1.40% 0.90% 1.15% 1.40% 0.00% 0.00% 0.00% SOLVER
II. Portfolio II. Portfolio Individual Contr to Individual Contr to Contr to Contr to
V V Bond Portfolio Bond Portfolio Portfolio Portfolio Portfolio
Issue Settlement Maturity Credit Coupon Payment Payment UST Spread Clean Last Next # of days # of days # of days Issue Settlement Maturity Credit minus plus Duration Duration Convexity Convexity Duration Convexity Weight
Name Date Date Rating Rate Frequency Basis Bench to UST Yield Price Coupon Coupon in Coupon from Last to Next Accrued Full Par Market Name Date Date Rating
Payment Payment Period to Settle Coupon Interest Price Amount Value Δy = 0.20%
UST Short 4/5/22 2/15/24 AAA 2.875% 2 1 2.890% 99.97093 2/15/22 8/15/22 181 49 132 0.0039 99.9748 50,000,000 49,987,409 UST Short 4/5/22 2/15/24 AAA 100.3363 99.6150 1.8035 0.1212 2.0877 0.1403 0.1202 0.1392 0.0667
UST Inter 4/5/22 2/15/29 AAA 3.000% 2 1 3.050% 99.69018 2/15/22 8/15/22 181 49 132 0.0041 99.6942 50,000,000 49,847,122 UST Inter 4/5/22 2/15/29 AAA 100.9312 98.4745 6.1606 0.4127 21.6353 1.4495 0.4107 1.4424 0.0667
UST Long 4/5/22 2/15/52 AAA 3.375% 2 1 3.360% 100.27862 2/15/22 8/15/22 181 49 132 0.0046 100.2832 50,000,000 50,141,595 UST Long 4/5/22 2/15/52 AAA 104.1383 96.6165 18.7512 1.2637 234.8898 15.8302 1.2501 15.6593 0.0667
A 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 A 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386 0.1195 0.1378 0.0667
B 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 B 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386 0.1195 0.1378 0.0667
C 4/5/22 2/15/24 A 3.625% 2 1 2.875% 0.850% 3.725% 99.81817 2/15/22 8/15/22 181 49 132 0.0049 99.8231 50,000,000 49,911,537 C 4/5/22 2/15/24 A 100.1807 99.4671 1.7873 0.1199 2.0568 0.1380 0.1192 0.1371 0.0667
D 4/5/22 2/15/24 BBB 4.000% 2 1 2.875% 1.000% 3.875% 100.21900 2/15/22 8/15/22 181 49 132 0.0054 100.2244 50,000,000 50,112,208 D 4/5/22 2/15/24 BBB 100.5824 99.8681 1.7818 0.1200 2.0472 0.1379 0.1188 0.1365 0.0667
E 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 E 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017 0.4017 1.3968 0.0667
F 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 F 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017 0.4017 1.3968 0.0667
G 4/5/22 2/15/29 A 3.875% 2 1 3.000% 0.950% 3.950% 99.54915 2/15/22 8/15/22 181 49 132 0.0052 99.5544 50,000,000 49,777,196 G 4/5/22 2/15/29 A 100.7521 98.3732 5.9739 0.3997 20.6891 1.3842 0.3983 1.3793 0.0667
H 4/5/22 2/15/29 BBB 4.125% 2 1 3.000% 1.200% 4.200% 99.55250 2/15/22 8/15/22 181 49 132 0.0056 99.5581 50,000,000 49,779,043 H 4/5/22 2/15/29 BBB 100.7455 98.3870 5.9224 0.3962 20.4301 1.3669 0.3948 1.3620 0.0667
I 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 I 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036 1.1275 13.4360 0.0667
J 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 J 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036 1.1275 13.4360 0.0667
K 4/5/22 2/15/52 A 4.375% 2 1 3.375% 1.150% 4.525% 97.55146 2/15/22 8/15/22 181 49 132 0.0059 97.5574 50,000,000 48,778,692 K 4/5/22 2/15/52 A 100.8353 94.4298 16.4145 1.0762 192.6335 12.6295 1.0943 12.8422 0.0667
L 4/5/22 2/15/52 BBB 4.625% 2 1 3.375% 1.400% 4.775% 97.62076 2/15/22 8/15/22 181 49 132 0.0063 97.6270 50,000,000 48,813,509 L 4/5/22 2/15/52 BBB 100.8109 94.5870 15.9382 1.0457 184.1992 12.0851 1.0625 12.2799 0.0667
Portfolio Total 750,000,000 744,006,132 8.2182 74.6494 8.2664 75.3191 1.0000
#4 ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: AFTER SPREADS CHANGE. #4 ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: AFTER SPREADS CHANGE.
I. Spreads Market Spreads Base Case Spreads Spread Changes
AA A BBB AA A BBB AA A BBB
Spreads to UST Short-Term: 0.60% 0.85% 1.10% 0.60% 0.85% 1.10% 0.00% 0.00% 0.00%
Spreads to UST Intermediate-Term: 0.70% 0.95% 1.20% 0.70% 0.95% 1.20% 0.00% 0.00% 0.00%
Spreads to UST Long-Term: 0.90% 1.15% 1.40% 0.90% 1.15% 1.40% 0.00% 0.00% 0.00%
II. Portfolio II. Portfolio Individual Contr to Individual Contr to
V V Bond Portfolio Bond Portfolio
Issue Settlement Maturity Credit Coupon Payment Payment UST Spread Clean Last Next # of days # of days # of days Issue Settlement Maturity Credit minus plus Duration Duration Convexity Convexity
Name Date Date Rating Rate Frequency Basis Bench to UST Yield Price Coupon Coupon in Coupon from Last to Next Accrued Full Par Market Name Date Date Rating
Payment Payment Period to Settle Coupon Interest Price Amount Value Δy = 0.20%
UST Short 4/5/22 2/15/24 AAA 2.875% 2 1 2.890% 99.97093 2/15/22 8/15/22 181 49 132 0.0039 99.9748 50,000,000 49,987,409 UST Short 4/5/22 2/15/24 AAA 100.3363 99.6150 1.8035 0.1212 2.0877 0.1403
UST Inter 4/5/22 2/15/29 AAA 3.000% 2 1 3.050% 99.69018 2/15/22 8/15/22 181 49 132 0.0041 99.6942 50,000,000 49,847,122 UST Inter 4/5/22 2/15/29 AAA 100.9312 98.4745 6.1606 0.4127 21.6353 1.4495
UST Long 4/5/22 2/15/52 AAA 3.375% 2 1 3.360% 100.27862 2/15/22 8/15/22 181 49 132 0.0046 100.2832 50,000,000 50,141,595 UST Long 4/5/22 2/15/52 AAA 104.1383 96.6165 18.7512 1.2637 234.8898 15.8302
A 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 A 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386
B 4/5/22 2/15/24 AA 3.375% 2 1 2.875% 0.600% 3.475% 99.81808 2/15/22 8/15/22 181 49 132 0.0046 99.8227 50,000,000 49,911,326 B 4/5/22 2/15/24 AA 100.1813 99.4656 1.7924 0.1202 2.0665 0.1386
C 4/5/22 2/15/24 A 3.625% 2 1 2.875% 0.850% 3.725% 99.81817 2/15/22 8/15/22 181 49 132 0.0049 99.8231 50,000,000 49,911,537 C 4/5/22 2/15/24 A 100.1807 99.4671 1.7873 0.1199 2.0568 0.1380
D 4/5/22 2/15/24 BBB 4.000% 2 1 2.875% 1.000% 3.875% 100.21900 2/15/22 8/15/22 181 49 132 0.0054 100.2244 50,000,000 50,112,208 D 4/5/22 2/15/24 BBB 100.5824 99.8681 1.7818 0.1200 2.0472 0.1379
E 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 E 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017
F 4/5/22 2/15/29 AA 3.625% 2 1 3.000% 0.700% 3.700% 99.54571 2/15/22 8/15/22 181 49 132 0.0049 99.5506 50,000,000 49,775,310 F 4/5/22 2/15/29 AA 100.7587 98.3592 6.0260 0.4031 20.9521 1.4017
G 4/5/22 2/15/29 A 3.875% 2 1 3.000% 0.950% 3.950% 99.54915 2/15/22 8/15/22 181 49 132 0.0052 99.5544 50,000,000 49,777,196 G 4/5/22 2/15/29 A 100.7521 98.3732 5.9739 0.3997 20.6891 1.3842
H 4/5/22 2/15/29 BBB 4.125% 2 1 3.000% 1.200% 4.200% 99.55250 2/15/22 8/15/22 181 49 132 0.0056 99.5581 50,000,000 49,779,043 H 4/5/22 2/15/29 BBB 100.7455 98.3870 5.9224 0.3962 20.4301 1.3669
I 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 I 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036
J 4/5/22 2/15/52 AA 4.125% 2 1 3.375% 0.900% 4.275% 97.47897 2/15/22 8/15/22 181 49 132 0.0056 97.4846 50,000,000 48,742,275 J 4/5/22 2/15/52 AA 100.8607 94.2656 16.9131 1.1080 201.5406 13.2036
K 4/5/22 2/15/52 A 4.375% 2 1 3.375% 1.150% 4.525% 97.55146 2/15/22 8/15/22 181 49 132 0.0059 97.5574 50,000,000 48,778,692 K 4/5/22 2/15/52 A 100.8353 94.4298 16.4145 1.0762 192.6335 12.6295
L 4/5/22 2/15/52 BBB 4.625% 2 1 3.375% 1.400% 4.775% 97.62076 2/15/22 8/15/22 181 49 132 0.0063 97.6270 50,000,000 48,813,509 L 4/5/22 2/15/52 BBB 100.8109 94.5870 15.9382 1.0457 184.1992 12.0851
Portfolio Total 750,000,000 744,006,132 8.2182 74.6494
Your Assignment.
Part 1.) You are a portfolio manager for Vandelay Capital Management's Investment Grade Bond Fund.
You've been pondering the Investment Clock for a few days, and you now believe that
the "time" is 8:30. The Investment Clock is posted on Blackboard.
a.) Change the Quality Spread Matrix in Case #2 to reflect your views.
Explain why you changed the spread matrix the way you did.
The key parts of this problem involve Cases #3 and #4.
You want to change your porfolio to reflect your views that quality spreads will soon change.
Remember: No guts, no glory.
b.) Change your asset allocation by adjusting Case #3.
You can do this in two ways. The first is: adjust the par amounts, i.e., change the equal par weighting.
The second way is: choose the portfolio duration and portfolio convexity you want.
Then use Solver to find the asset weights that attain your portfolio objectives.
Explain why you changed the portfolio as you did.
c.) Then take the weights from Case #3 and enter them into Case #4.
Then change the quality spread matrix for Case #4, just as you did in Case #2.
d.) Compare portfolio duration and portfolio convexity for Cases #1 and #3.
Then compare portfolio duration and portfolio convexity for Cases #2 and #4.
How much did your aggressive portfolio reallocation make for your clients?
Part 2.) Bonds A through L are non-Treasury fixed income securities, but, other than that information,
they are not defined. Suppose that bonds A, B, E, F, I, and J are callable Corporate bonds, and the
call price for each bond is par. And suppose that bonds C, D, G, H, K, and L are Agency
Residential MBS.
a.) What are the issues here that you, as the portfolio manager, must deal with to position
your fund to take advantage of your insight that the time is 8:30?
Provide some detail for your answer to this question. No calculations are required for Part 2.
One final point about the entire project:
Remember, what I am always interested in is thoughtfulness. Just getting a correct
numerical answer is not your only objective. Write a project that shows me you have
thought about these questions and the issues surrounding solving them. Good Luck.
All your answers must be typed and double spaced.