Questions 1-5 please done in Excel!
Macaulay Duration = 1 x PVCF1 + 2 x PVCF2... tnx PVCFn k x Price Settlement Date: 4/10/2021 weighted avg Modified D Term Yield Bond A B D Portfolio Maturity 2 4/10/2023 $ 5 4/10/2026 $ 7 4/10/2028 $ 10 4/10/2031 $ Par 10,000,000 12,000,000 5,000,000 20,000,000 Coupon 1.65% 2.10% 2.90% 3.45% 1.65% 2.10% 2.90% 3.45% Price Market Value 100.00 $ 10,000,000 100.00 $ 12,000,000 100.00 $ 5,000,000 100.00 $ 20,000,000 $ 47,000,000 Macaulay duration Modified duration = (1 + yield/k) =duration() Macaulay D 1.9756 4.7726 6.3854 8.5417 Modified D 1.9594 4.7230 6.2942 8.3969 5.8655 Settlement Date: 4/10/2021 weighted avg Modified D Term Bond B D Portfolio Maturity 24/10/2023 $ 5 4/10/2026 $ 7 4/10/2028 $ 10 4/10/2031 $ Par 10,000,000 12,000,000 5,000,000 20,000,000 Coupon 1.65% 2.10% 2.90% 3.45% Yield 1.65% 2.10% 2.90% 3.45% Price Market Value 100.00 $ 10,000,000 100.00 $ 12,000,000 100.00 $ 5,000,000 100.00 $ 20,000,000 $ 47,000,000 Yield Shift 0.50% 0.50% 0.50% 0.50% New Yield 2.15% 2.60% 3.40% 3.95% V_-V+ full valuation Modified D 1.9595 4.7236 6.2956 8.4005 5.8674 New Price New MV 99.03 $ 9,902,631 97.67 $ 11,720,380 96.91 $ 4,845,427 95.90 $ 19,180,452 $ 45,648,890 2(V)(4) 5.8674 Term Bond B D Portfolio Maturity 24/10/2023 $ 5 4/10/2026 S 7 4/10/2028 $ 10 4/10/2031 $ Par 10,000,000 12,000,000 5,000,000 20,000,000 Coupon 1.65% 2.10% 2.90% 3.45% Yield 1.65% 2.10% 2.90% 3.45% Price Market Value 100.00 $ 10,000,000 100.00 $ 12,000,000 100.00 $ 5,000,000 100.00 $ 20,000,000 $ $ 47,000,000 Yield Shift -0.50% -0.50% -0.50% -0.50% New Yield 1.15% 1.60% 2.40% 2.95% 100.99 $ 10,098,579 102.39 $ 12,287,212 103.20 $ 5,160,209 104.30 $ 20,860,551 $ 48,406,550 Actual price Question 1 Question 2 Question 3 Question 4 Question 5 Using the formula-derived Modified Duration, what is your estimate of the 10yr bond's market value change for a 50bp backup in rates? Using the formula-derived Modified Duration, what is your estimate of the 10yr bond's market value change for a 50bp rally in rates? Using the full-valuation method, what is your calcuated market value change of the 10yr bond given a 50bp backup in rates? Using the full-valuation method, what is your calcuated market value change of the 10yr bond given a 50bp rally in rates? Why are the answers to question 1 and 3, and question 2 and 4 different? Macaulay Duration = 1 x PVCF1 + 2 x PVCF2... tnx PVCFn k x Price Settlement Date: 4/10/2021 weighted avg Modified D Term Yield Bond A B D Portfolio Maturity 2 4/10/2023 $ 5 4/10/2026 $ 7 4/10/2028 $ 10 4/10/2031 $ Par 10,000,000 12,000,000 5,000,000 20,000,000 Coupon 1.65% 2.10% 2.90% 3.45% 1.65% 2.10% 2.90% 3.45% Price Market Value 100.00 $ 10,000,000 100.00 $ 12,000,000 100.00 $ 5,000,000 100.00 $ 20,000,000 $ 47,000,000 Macaulay duration Modified duration = (1 + yield/k) =duration() Macaulay D 1.9756 4.7726 6.3854 8.5417 Modified D 1.9594 4.7230 6.2942 8.3969 5.8655 Settlement Date: 4/10/2021 weighted avg Modified D Term Bond B D Portfolio Maturity 24/10/2023 $ 5 4/10/2026 $ 7 4/10/2028 $ 10 4/10/2031 $ Par 10,000,000 12,000,000 5,000,000 20,000,000 Coupon 1.65% 2.10% 2.90% 3.45% Yield 1.65% 2.10% 2.90% 3.45% Price Market Value 100.00 $ 10,000,000 100.00 $ 12,000,000 100.00 $ 5,000,000 100.00 $ 20,000,000 $ 47,000,000 Yield Shift 0.50% 0.50% 0.50% 0.50% New Yield 2.15% 2.60% 3.40% 3.95% V_-V+ full valuation Modified D 1.9595 4.7236 6.2956 8.4005 5.8674 New Price New MV 99.03 $ 9,902,631 97.67 $ 11,720,380 96.91 $ 4,845,427 95.90 $ 19,180,452 $ 45,648,890 2(V)(4) 5.8674 Term Bond B D Portfolio Maturity 24/10/2023 $ 5 4/10/2026 S 7 4/10/2028 $ 10 4/10/2031 $ Par 10,000,000 12,000,000 5,000,000 20,000,000 Coupon 1.65% 2.10% 2.90% 3.45% Yield 1.65% 2.10% 2.90% 3.45% Price Market Value 100.00 $ 10,000,000 100.00 $ 12,000,000 100.00 $ 5,000,000 100.00 $ 20,000,000 $ $ 47,000,000 Yield Shift -0.50% -0.50% -0.50% -0.50% New Yield 1.15% 1.60% 2.40% 2.95% 100.99 $ 10,098,579 102.39 $ 12,287,212 103.20 $ 5,160,209 104.30 $ 20,860,551 $ 48,406,550 Actual price Question 1 Question 2 Question 3 Question 4 Question 5 Using the formula-derived Modified Duration, what is your estimate of the 10yr bond's market value change for a 50bp backup in rates? Using the formula-derived Modified Duration, what is your estimate of the 10yr bond's market value change for a 50bp rally in rates? Using the full-valuation method, what is your calcuated market value change of the 10yr bond given a 50bp backup in rates? Using the full-valuation method, what is your calcuated market value change of the 10yr bond given a 50bp rally in rates? Why are the answers to question 1 and 3, and question 2 and 4 different
