Project 2: Duration and Convexity You must submit this assignment on Blackboard. This assignment should be done individually The solutions must be turned in before Feb. 19th by 11:59pm. Late projects will not be accepted and the first version posted is the one I will grade. The file name should be your last name. For example: HOOD.xlsx. Excel formulas and functions should be used for this project. Use the following information as your starting point. There are five notes and one bond Theoretical on-the-run Treasury yields for a 1/23/18 settlement date are given with the maturity. Assume each security is priced at par Note/Bond 2-Year Note 3-Year Note 5-Year Note 7-Year Note 10-Year Note 30-Year Bond Maturity 1/23/2020 1/23/2021 1/23/2023 1/23/2025 1/23/2028 1/23/2048 Yield (%) 2.00 2.20 2.40 2.60 2.80 3.00 Calculate the modified duration in years for each security using the MDURATION function in excel Calculate the modified duration and convexity measure in years using the approximation in the chapter 4 notes and book (not the big formula). Use 10 basis points (0.10%) as the plus and minus change in yield. Calculate the portfolio weighted average duration, convexity, and yield for two different portfolios 1) 2) 3) Portfolio 1:22% weight in 3-year, 51.5% weight in 5-year, 26.5% weight in 7-year Portfolio 2: 75% weight in 2-year, 15% weight in 10-year, 10% weight in 30-year 4) Calculate the approximate percent change in price for each portfolio using duration and convexity for two scenarios: a parallel shift in all yields of-0.51 % and +0.86 %, which portfolio has a more favorable price change and why? Discuss the difference in weighted average yield between the two portfolios. Which portfolio earns a higher return if there were no parallel shift in yields? Include your comments in the spreadsheet. Project 2: Duration and Convexity You must submit this assignment on Blackboard. This assignment should be done individually The solutions must be turned in before Feb. 19th by 11:59pm. Late projects will not be accepted and the first version posted is the one I will grade. The file name should be your last name. For example: HOOD.xlsx. Excel formulas and functions should be used for this project. Use the following information as your starting point. There are five notes and one bond Theoretical on-the-run Treasury yields for a 1/23/18 settlement date are given with the maturity. Assume each security is priced at par Note/Bond 2-Year Note 3-Year Note 5-Year Note 7-Year Note 10-Year Note 30-Year Bond Maturity 1/23/2020 1/23/2021 1/23/2023 1/23/2025 1/23/2028 1/23/2048 Yield (%) 2.00 2.20 2.40 2.60 2.80 3.00 Calculate the modified duration in years for each security using the MDURATION function in excel Calculate the modified duration and convexity measure in years using the approximation in the chapter 4 notes and book (not the big formula). Use 10 basis points (0.10%) as the plus and minus change in yield. Calculate the portfolio weighted average duration, convexity, and yield for two different portfolios 1) 2) 3) Portfolio 1:22% weight in 3-year, 51.5% weight in 5-year, 26.5% weight in 7-year Portfolio 2: 75% weight in 2-year, 15% weight in 10-year, 10% weight in 30-year 4) Calculate the approximate percent change in price for each portfolio using duration and convexity for two scenarios: a parallel shift in all yields of-0.51 % and +0.86 %, which portfolio has a more favorable price change and why? Discuss the difference in weighted average yield between the two portfolios. Which portfolio earns a higher return if there were no parallel shift in yields? Include your comments in the spreadsheet