can you help me out with the base case and scenario problem? Please answer it in an excel, thank you.

FIXED INCOME PROJECT FINANCE 490 SPRING SEMESTER 2017 NAME: DUE DATE: MAY 1, 2017 It is April 13, 2017. Consider the following Treasury yield curve: 2-year 5-year 10-year Maturity 3/31/2019 3/15/2022 2/28/2027 YTM 2.00% 3.50% 4.75% Price 100-00 100-00 100-00 There are 2 portfolios, each with $100MM market value. Portfolio I: 5-year bullet. Portfolio II: 2-year/10-year barbell. 1.) Today, you invest the $100MM in each portfolio, so that the portfolios have the same duration. The trades settle tomorrow. How much is invested in the 2-year and in the 10-year? Suppose on October 14, 2017 the 2-year ytm increases 50bp and the 10-year ytm increases 25 bp. 2.) What are the price and yield for the 5-year that produce the same realized total rate of return for the bullet and the barbell for this 6-month investment period? 3.) Discuss the issues/complexities involved with solving these questions. Answer: 1. Settlement Day 4/14/17 Column1 Column2 Column3 Column4 Column5 Column6 Maturity YTM Price Maturity Coupon(%) Duration Share 2-year 3/31/2019 2.00% 100 1.96 2.00% 5-year 3/15/2022 3.50% 100 4.92 3.50% 10-year 2/28/2027 4.75% 100 9.88 4.75% Assume that the share of 2-year bond is X, to generate the same duration: 1.913X+7.768(1-X)=4.470 X=56.32% Therefore, $56.32MM is invested in 2-year bond, $43.68MM is invested in 10-year bond. 1.913 4.47 7.768 56.32% 43.68% 2 #1 I. ORIGINAL PORTFOLIO: BASE CASE Spreads Spreads to UST Short-Term: Spreads to UST Intermediate-Term: Spreads to UST Long-Term: II. Market AA 0.50% 0.60% 0.80% Spreads A 0.75% 0.85% 1.05% BBB 1.00% 1.10% 1.30% Base AA 0.50% 0.60% 0.80% Case A 0.75% 0.85% 1.05% Spreads BBB 1.00% 1.10% 1.30% Spread to UST Yield Clean Price Spread AA 0.00% 0.00% 0.00% Changes A 0.00% 0.00% 0.00% BBB 0.00% 0.00% 0.00% Portfolio Issue Name UST Short UST Inter UST Long A B C D E F G H I J K L Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date Credit Rating 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB Coupon Rate 0.50% 1.50% 2.00% 0.75% 0.80% 1.25% 2.00% 1.25% 1.30% 1.75% 2.50% 1.50% 1.60% 2.00% 2.75% Payment Payment Frequency Basis 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 UST Bench 0.50% 0.50% 0.50% 0.50% 1.50% 1.50% 1.50% 1.50% 2.00% 2.00% 2.00% 2.00% 0.50% 0.50% 0.75% 1.00% 0.60% 0.60% 0.85% 1.10% 0.80% 0.80% 1.05% 1.30% 0.50% 99.99993 1.50% 99.99938 2.00% 99.99891 1.00% 99.42374 1.00% 99.53893 1.25% 99.99957 1.50% 101.14323 2.10% 94.24740 2.10% 94.58571 2.35% 95.97689 2.60% 99.33417 2.80% 73.54305 2.80% 75.57804 3.05% 79.31503 3.30% 89.50555 Last Coupon Payment Next Coupon Payment 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 # of days # of days # of days in Coupon from Last to Next Period to Settle Coupon 181 181 181 181 181 181 181 181 181 181 181 181 181 181 181 59 59 59 59 59 59 59 59 59 59 59 59 59 59 59 122 122 122 122 122 122 122 122 122 122 122 122 122 122 122 Accrued Interest 0.0008 0.0024 0.0033 0.0012 0.0013 0.0020 0.0033 0.0020 0.0021 0.0029 0.0041 0.0024 0.0026 0.0033 0.0045 Portfolio ORIGINAL PORTFOLIO: BASE CASE AFTER SPREADS WIDEN I. Spreads Market Spreads AA A Spreads to UST Short-Term: 0.50% 0.75% Spreads to UST Intermediate-Term: 0.60% 0.85% Spreads to UST Long-Term: 0.80% 1.05% Full Price 100.0007 100.0018 100.0022 99.4250 99.5402 100.0016 101.1465 94.2494 94.5878 95.9797 99.3382 73.5455 75.5806 79.3183 89.5100 Total Par Amount 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 Market Value 50,000,373 50,000,915 50,001,084 49,712,480 49,770,119 50,000,805 50,573,247 47,124,717 47,293,917 47,989,870 49,669,123 36,772,748 37,790,322 39,659,147 44,755,018 750,000,000 701,113,883 #2 II. BBB 1.00% 1.10% 1.30% Base AA 0.50% 0.60% 0.80% Case A 0.75% 0.85% 1.05% Spreads BBB 1.00% 1.10% 1.30% Spread to UST Yield Clean Price Spread AA 0.00% 0.00% 0.00% Changes A 0.00% 0.00% 0.00% BBB 0.00% 0.00% 0.00% Portfolio Issue Name UST Short UST Inter UST Long A B C D E F G H I J K L Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date Credit Rating 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB Coupon Rate 0.50% 1.50% 2.00% 0.75% 0.80% 1.25% 2.00% 1.25% 1.30% 1.75% 2.50% 1.50% 1.60% 2.00% 2.75% Payment Payment Frequency Basis 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 UST Bench 0.50% 0.50% 0.50% 0.50% 1.50% 1.50% 1.50% 1.50% 2.00% 2.00% 2.00% 2.00% 0.50% 0.50% 0.75% 1.00% 0.60% 0.60% 0.85% 1.10% 0.80% 0.80% 1.05% 1.30% 0.50% 99.99993 1.50% 99.99938 2.00% 99.99891 1.00% 99.42374 1.00% 99.53893 1.25% 99.99957 1.50% 101.14323 2.10% 94.24740 2.10% 94.58571 2.35% 95.97689 2.60% 99.33417 2.80% 73.54305 2.80% 75.57804 3.05% 79.31503 3.30% 89.50555 Last Coupon Payment Next Coupon Payment 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 # of days # of days # of days in Coupon from Last to Next Period to Settle Coupon 181 181 181 181 181 181 181 181 181 181 181 181 181 181 181 59 59 59 59 59 59 59 59 59 59 59 59 59 59 59 122 122 122 122 122 122 122 122 122 122 122 122 122 122 122 Accrued Interest 0.0008 0.0024 0.0033 0.0012 0.0013 0.0020 0.0033 0.0020 0.0021 0.0029 0.0041 0.0024 0.0026 0.0033 0.0045 Portfolio #3 I. ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: BEFORE SPREADS WIDEN Spreads Market Spreads Base Case Spreads Spread Changes Full Price 100.0007 100.0018 100.0022 99.4250 99.5402 100.0016 101.1465 94.2494 94.5878 95.9797 99.3382 73.5455 75.5806 79.3183 89.5100 Total Par Amount 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 Market Value 50,000,373 50,000,915 50,001,084 49,712,480 49,770,119 50,000,805 50,573,247 47,124,717 47,293,917 47,989,870 49,669,123 36,772,748 37,790,322 39,659,147 44,755,018 750,000,000 701,113,883 #1 II. ORIGINAL PORTFOLIO: BASE CASE Portfolio Issue Name UST Short UST Inter UST Long A B C D E F G H I J K L Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 Credit Rating AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB V minus V plus y = 0.20% 100.4662 101.3972 104.6618 99.8855 100.0011 100.4622 101.6087 95.5694 95.9104 97.3002 100.6715 77.0166 79.1062 82.8167 93.1521 99.5379 98.6276 95.5921 98.9670 99.0819 99.5436 100.6868 92.9495 93.2853 94.6792 98.0249 70.2617 72.2444 76.0035 86.0524 Individual Contr to Individual Bond Portfolio Bond Duration Duration Convexity 2.3207 6.9237 22.6738 2.3096 2.3086 2.2965 2.2786 6.9494 6.9381 6.8269 6.6607 22.9616 22.6969 21.4742 19.8293 0.1655 0.4938 1.6170 0.1638 0.1639 0.1638 0.1644 0.4671 0.4680 0.4673 0.4719 1.2043 1.2234 1.2147 1.2658 3.2785 26.4683 312.0042 3.2522 3.2502 3.2246 3.1881 26.5523 26.4941 25.9023 25.0305 318.2389 313.0468 289.3163 257.5458 9.7145 #2 II. UST Short UST Inter UST Long A B C D E F G H I J K L Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 Credit Rating AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB V minus V plus 98.7822 y = 0.20% 100.4662 101.3972 104.6618 99.8855 100.0011 100.4622 101.6087 95.5694 95.9104 97.3002 100.6715 77.0166 79.1062 82.8167 93.1521 99.5379 98.6276 95.5921 98.9670 99.0819 99.5436 100.6868 92.9495 93.2853 94.6792 98.0249 70.2617 72.2444 76.0035 86.0524 Individual Contr to Individual Bond Portfolio Bond Duration Duration Convexity 2.3207 6.9237 22.6738 2.3096 2.3086 2.2965 2.2786 6.9494 6.9381 6.8269 6.6607 22.9616 22.6969 21.4742 19.8293 0.1655 0.4938 1.6170 0.1638 0.1639 0.1638 0.1644 0.4671 0.4680 0.4673 0.4719 1.2043 1.2234 1.2147 1.2658 9.7145 #3 0.2338 1.8876 22.2511 0.2306 0.2307 0.2300 0.2300 1.7847 1.7872 1.7730 1.7732 16.6913 16.8733 16.3654 16.4402 ORIGINAL PORTFOLIO: BASE CASE AFTER SPREADS WIDEN Portfolio Issue Name Contr to Portfolio Convexity ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: BEFORE SPREADS WIDEN 3.2785 26.4683 312.0042 3.2522 3.2502 3.2246 3.1881 26.5523 26.4941 25.9023 25.0305 318.2389 313.0468 289.3163 257.5458 Contr to Portfolio Convexity 0.2338 1.8876 22.2511 0.2306 0.2307 0.2300 0.2300 1.7847 1.7872 1.7730 1.7732 16.6913 16.8733 16.3654 16.4402 98.7822 AA 0.50% 0.60% 0.80% Spreads to UST Short-Term: Spreads to UST Intermediate-Term: Spreads to UST Long-Term: II. A 0.75% 0.85% 1.05% BBB 1.00% 1.10% 1.30% AA 0.50% 0.60% 0.80% A 0.75% 0.85% 1.05% BBB 1.00% 1.10% 1.30% Spread to UST Yield Clean Price AA 0.00% 0.00% 0.00% A 0.00% 0.00% 0.00% BBB 0.00% 0.00% 0.00% Portfolio Issue Name UST Short UST Inter UST Long A B C D E F G H I J K L Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date Credit Rating 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB Coupon Rate 0.50% 1.50% 2.00% 0.75% 0.80% 1.25% 2.00% 1.25% 1.30% 1.75% 2.50% 1.50% 1.60% 2.00% 2.75% Payment Payment Frequency Basis 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 UST Bench 0.50% 0.50% 0.50% 0.50% 1.50% 1.50% 1.50% 1.50% 2.00% 2.00% 2.00% 2.00% 0.50% 0.50% 0.75% 1.00% 0.60% 0.60% 0.85% 1.10% 0.80% 0.80% 1.05% 1.30% 0.50% 99.99993 1.50% 99.99938 2.00% 99.99891 1.00% 99.42374 1.00% 99.53893 1.25% 99.99957 1.50% 101.14323 2.10% 94.24740 2.10% 94.58571 2.35% 95.97689 2.60% 99.33417 2.80% 73.54305 2.80% 75.57804 3.05% 79.31503 3.30% 89.50555 Last Coupon Payment Next Coupon Payment 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 # of days # of days # of days in Coupon from Last to Next Period to Settle Coupon 181 181 181 181 181 181 181 181 181 181 181 181 181 181 181 59 59 59 59 59 59 59 59 59 59 59 59 59 59 59 122 122 122 122 122 122 122 122 122 122 122 122 122 122 122 Accrued Interest 0.0008 0.0024 0.0033 0.0012 0.0013 0.0020 0.0033 0.0020 0.0021 0.0029 0.0041 0.0024 0.0026 0.0033 0.0045 Portfolio ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: AFTER SPREADS WIDEN I. Spreads Market Spreads AA A BBB Spreads to UST Short-Term: 0.50% 0.75% 1.00% Spreads to UST Intermediate-Term: 0.60% 0.85% 1.10% Spreads to UST Long-Term: 0.80% 1.05% 1.30% Full Price 100.0007 100.0018 100.0022 99.4250 99.5402 100.0016 101.1465 94.2494 94.5878 95.9797 99.3382 73.5455 75.5806 79.3183 89.5100 Total Par Amount 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 Market Value 50,000,373 50,000,915 50,001,084 49,712,480 49,770,119 50,000,805 50,573,247 47,124,717 47,293,917 47,989,870 49,669,123 36,772,748 37,790,322 39,659,147 44,755,018 750,000,000 701,113,883 #4 II. Base AA 0.50% 0.60% 0.80% Case A 0.75% 0.85% 1.05% Spreads BBB 1.00% 1.10% 1.30% Spread to UST Yield Clean Price Spread AA 0.00% 0.00% 0.00% Changes A 0.00% 0.00% 0.00% BBB 0.00% 0.00% 0.00% Portfolio Issue Name UST Short UST Inter UST Long A B C D E F G H I J K L Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date Credit Rating 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB Coupon Rate 0.50% 1.50% 2.00% 0.75% 0.80% 1.25% 2.00% 1.25% 1.30% 1.75% 2.50% 1.50% 1.60% 2.00% 2.75% Payment Payment Frequency Basis 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 UST Bench 0.50% 0.50% 0.50% 0.50% 1.50% 1.50% 1.50% 1.50% 2.00% 2.00% 2.00% 2.00% 0.50% 0.50% 0.75% 1.00% 0.60% 0.60% 0.85% 1.10% 0.80% 0.80% 1.05% 1.30% 0.50% 99.99993 1.50% 99.99938 2.00% 99.99891 1.00% 99.42374 1.00% 99.53893 1.25% 99.99957 1.50% 101.14323 2.10% 94.24740 2.10% 94.58571 2.35% 95.97689 2.60% 99.33417 2.80% 73.54305 2.80% 75.57804 3.05% 79.31503 3.30% 89.50555 Last Coupon Payment Next Coupon Payment 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 8/15/2017 # of days # of days # of days in Coupon from Last to Next Period to Settle Coupon 181 181 181 181 181 181 181 181 181 181 181 181 181 181 181 59 59 59 59 59 59 59 59 59 59 59 59 59 59 59 122 122 122 122 122 122 122 122 122 122 122 122 122 122 122 Accrued Interest 0.0008 0.0024 0.0033 0.0012 0.0013 0.0020 0.0033 0.0020 0.0021 0.0029 0.0041 0.0024 0.0026 0.0033 0.0045 Portfolio IV. Your Assignment Full Price 100.0007 100.0018 100.0022 99.4250 99.5402 100.0016 101.1465 94.2494 94.5878 95.9797 99.3382 73.5455 75.5806 79.3183 89.5100 Total Par Amount 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 Market Value 50,000,373 50,000,915 50,001,084 49,712,480 49,770,119 50,000,805 50,573,247 47,124,717 47,293,917 47,989,870 49,669,123 36,772,748 37,790,322 39,659,147 44,755,018 750,000,000 701,113,883 OPTIMIZATION II. Portfolio Issue Name UST Short UST Inter UST Long A B C D E F G H I J K L #4 II. Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 Credit Rating AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB V minus V plus y = 0.20% 100.4662 101.3972 104.6618 99.8855 100.0011 100.4622 101.6087 95.5694 95.9104 97.3002 100.6715 77.0166 79.1062 82.8167 93.1521 99.5379 98.6276 95.5921 98.9670 99.0819 99.5436 100.6868 92.9495 93.2853 94.6792 98.0249 70.2617 72.2444 76.0035 86.0524 UST Short UST Inter UST Long A B C D E F G H I J K L 2.3207 6.9237 22.6738 2.3096 2.3086 2.2965 2.2786 6.9494 6.9381 6.8269 6.6607 22.9616 22.6969 21.4742 19.8293 0.1655 0.4938 1.6170 0.1638 0.1639 0.1638 0.1644 0.4671 0.4680 0.4673 0.4719 1.2043 1.2234 1.2147 1.2658 3.2785 26.4683 312.0042 3.2522 3.2502 3.2246 3.1881 26.5523 26.4941 25.9023 25.0305 318.2389 313.0468 289.3163 257.5458 9.7145 ORIGINAL PORTFOLIO WITH NEW ASSET ALLOCATION: AFTER SPREADS WIDEN Portfolio Issue Name Individual Contr to Individual Bond Portfolio Bond Duration Duration Convexity Settlement Date 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 4/15/2017 Maturity Date 8/15/2019 8/15/2024 8/15/2047 8/15/2019 8/15/2019 8/15/2019 8/15/2019 8/15/2024 8/15/2024 8/15/2024 8/15/2024 8/15/2047 8/15/2047 8/15/2047 8/15/2047 Credit Rating AAA AAA AAA AA AA A BBB AA AA A BBB AA AA A BBB V minus V plus y = 0.20% 100.4662 101.3972 104.6618 99.8855 100.0011 100.4622 101.6087 95.5694 95.9104 97.3002 100.6715 77.0166 79.1062 82.8167 93.1521 99.5379 98.6276 95.5921 98.9670 99.0819 99.5436 100.6868 92.9495 93.2853 94.6792 98.0249 70.2617 72.2444 76.0035 86.0524 Individual Contr to Individual Bond Portfolio Bond Duration Duration Convexity 2.3207 6.9237 22.6738 2.3096 2.3086 2.2965 2.2786 6.9494 6.9381 6.8269 6.6607 22.9616 22.6969 21.4742 19.8293 0.1655 0.4938 1.6170 0.1638 0.1639 0.1638 0.1644 0.4671 0.4680 0.4673 0.4719 1.2043 1.2234 1.2147 1.2658 9.7145 3.2785 26.4683 312.0042 3.2522 3.2502 3.2246 3.1881 26.5523 26.4941 25.9023 25.0305 318.2389 313.0468 289.3163 257.5458 Contr to Portfolio Convexity Contr to Portfolio Duration Contr to Portfolio Convexity Portfolio Weight 0.2338 1.8876 22.2511 0.2306 0.2307 0.2300 0.2300 1.7847 1.7872 1.7730 1.7732 16.6913 16.8733 16.3654 16.4402 0.1547 0.4616 1.5116 0.1540 0.1539 0.1531 0.1519 0.4633 0.4625 0.4551 0.4440 1.5308 1.5131 1.4316 1.3220 0.2186 1.7646 20.8003 0.2168 0.2167 0.2150 0.2125 1.7702 1.7663 1.7268 1.6687 21.2159 20.8698 19.2878 17.1697 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 0.0667 98.7822 10.3632 109.1195 1.0000 Contr to Portfolio Convexity 0.2338 1.8876 22.2511 0.2306 0.2307 0.2300 0.2300 1.7847 1.7872 1.7730 1.7732 16.6913 16.8733 16.3654 16.4402 98.7822 You are a portfolio manager for Vandelay Capital Management's Investment Grade Bond Fund. You think that interest rate spreads are going to widen soon. (BBB-UST, for example.) Change the Spread Matrix in Case #2 to reflect your views. Things to consider: do all spreads widen by the same amount? Or by different amounts. 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 spreads will soon widen. No guts, no glory. 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. Then take the weights from Case #3 and enter them into Case #4. Then change the spread matrix for Case #4, just as you did in Case #2. Compare portfolio duration and portfolio convexity for Cases #1 and #3. Then compare Cases #2 and #4. How much did your aggressive portfolio reallocation save your clients? THE PROJECT IS DUE ON MAY 1, 2017. V. Notes on Portfolio Spreadsheet. This spreadsheet has embedded formulas, so it can be used to experiment with how changes in yield, credit spread, and coupon affect the market value of each bond and the porfolio as a whole. Panel I is the Spread Panel. There are three credit qualities: AA, A, and BBB. There are spreads versus Treasuries across the yield curve: spreads versus short-term Treasuries. spreads versus intermediate-term Treasuries. spreads versus long-term Treasuries. Market Spreads, which are the current spreads, equal Base Case Spreads plus Spread Changes. Calculating Market Spreads this way makes it easy to manipulate the portfolio. Panel II is the Portfolio. There is a risk-free sector, US Treasuries. And there is a credit sector, which is rated less than AAA and trades at spreads versus Treasuries. These credit spreads are functions of credit quality and maturity. There are three generic maturity sectors. Think of the short-term sector as about 2 year, the intermediate sector as about 7 years, and the long-term sector as about 30 years. The coupon rates are supplied by me and can be changed. The Payment Frequency and Payment Basis are needed to compute the Clean Price of the bond. They are required inputs for the Excel "Price" function. Payment Frequency "2" means coupons are paid semi-annually. Payment Basis "1" means the price is calculated based on an exact day count. See the Excel handouts I have supplied. Spread to US Treasuries represents the appropriate spread from the Market Spread table in Panel I. The Yield equals the sum of the Treasury Benchmark yield and the Market Spread. Clean Price is the price given by the Excel function "Price." See the handout