Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Would you mind show the formulas please B C D E F Years to Maturity Ticker MRK MRK MRK MRK MRK MRK MRK MRK MRK
Would you mind show the formulas please
B C D E F Years to Maturity Ticker MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK 1 Name 2 Merck & Co Inc 3 Merck & Co Inc 4 Merck & Co Inc 5 Merck & Co Inc 6 Merck & Co Inc 7 Merck & Co Inc 8 Merck Sharp & Dohme Corp 9 Merck & Co Inc 10 Merck & Co Inc 11 Merck & Co Inc 12 Merck & Co Inc 13 Merck Sharp & Dohme Corp 14 Merck & Co Inc 15 Merck Sharp & Dohme Corp 16 Merck & Co Inc 17 Merck & Co Inc 18 Merck & Co Inc 19 Merck & Co Inc 20 Merck Sharp & Dohme Corp 21 Merck & Co Inc 22 Merck & Co Inc 23 Merck Sharp & Dohme Corp 24 Merck & Co Inc 25 Merck & Co Inc 26 Merck & Co Inc 27 Merck & Co Inc 28 Merck Sharp & Dohme Corp 29 Merck Sharp & Dohme Corp 30 Merck Sharp & Dohme Corp 31 Merck Sharp & Dohme Corp 32 Merck Sharp & Dohme Corp 33 Merck Sharp & Dohme Corp 34 Merck Sharp & Dohme Corp 35 Merck Sharp & Dohme Corp 36 Merck Sharp & Dohme Corp 37 Merck Sharp & Dohme Corp 38 Merck Sharp & Dohme Corp 39 Merck Sharp & Dohme Corp Issue Date Moody's 6/24/2020 A1 6/24/2020 A1 2/10/2015 A1 5/20/2013 A1 6/24/2020 A1 3/7/2019 A1 6/25/2009 A1 6/24/2020 A1 9/13/2012 A1 2/10/2015 A1 2/10/2015 A1 11/30/1998 A1 3/7/2019 A1 3/2/1998 A1 3/7/2019 A1 10/15/2014 A1 10/15/2014 A1 3/7/2019 A1 1/8/1996 A1 9/13/2012 A1 9/17/2007 A1 5/13/1997 A1 11/2/2016 A1 5/20/2013 A1 11/2/2016 A1 11/26/2003 A1 11/14/2006 A1 12/21/2000 A1 11/28/2001 A1 8/23/2002 A1 2/6/2001 A1 6/29/2001 A1 12/27/2000 A1 2/12/2004 A1 2/18/2003 A1 11/27/2000 A1 7/18/2001 A1 11/16/2001 A1 MRK MRK MRK MRK MRK MRK MRK Maturity 6/24/2050 2/24/2026 2/10/2025 5/18/2023 6/24/2030 3/7/2029 6/30/2039 6/24/2040 9/15/2022 2/10/2045 2/10/2022 12/1/2028 3/7/2049 3/1/2028 3/7/2024 10/15/2034 10/15/2026 3/7/2039 1/1/2026 9/15/2042 9/15/2037 5/3/2037 11/2/2024 5/18/2043 11/2/2036 12/1/2033 11/15/2036 12/21/2040 11/28/2041 8/22/2042 2/6/2041 6/21/2041 12/27/2040 2/12/2044 2/18/2043 11/27/2040 7/18/2041 12/21/2041 MRK MRK MRK MRK MRK MRK MRK MRK MRK 2222222 MRK MRK MRK MRK A B C D E F G Years to Maturity PEP PEP WFC 1 Issuer Name Ticker Moody Rtg Maturity 2 Morgan Stanley MS A1 10/20/2032 3 PepsiCo Inc A1 10/21/2031 4 Morgan Stanley MS A1 10/21/2025 5 PepsiCo Inc PEP A1 10/21/2051 6 PepsiCo Inc PEP A1 10/21/2041 7 Intel Corp INTC A1 8/12/2051 8 Bank of New York Mellon Corp/The BK A1 1/25/2029 9 Morgan Stanley MS A1 7/21/2032 10 Bank of New York Mellon Corp/The BK A1 10/25/2024 11 Intel Corp INTC A1 8/12/2031 12 PepsiCo Inc A1 3/19/2050 13 Amazon.com Inc AMZN A1 5/12/2031 14 PepsiCo Inc PEP A1 4/14/2046 15 Amazon.com Inc AMZN A1 5/12/2051 16 PepsiCo Inc PEP A1 2/25/2031 17 Bank of New York Mellon Corp/The BK A1 10/25/2024 18 Toyota Motor Credit Corp TOYOTA A1 9/12/2031 19 Morgan Stanley MS A1 7/20/2027 20 Wells Fargo & Co A1 10/23/2026 21 Coca-Cola Co/The KO A1 1/5/2032 22 PepsiCo Inc PEP A1 3/19/2040 23 Merck & Co Inc MRK A1 6/24/2050 24 Wells Fargo & Co WFC A1 4/4/2031 25 3M Co MMM A1 4/15/2030 26 Amazon.com Inc AMZN A1 5/12/2026 27 Toyota Motor Credit Corp TOYOTA A1 10/24/2025 28 Bank of New York Mellon Corp/The BK A1 10/24/2024 29 Intel Corp INTC A1 8/12/2028 30 Morgan Stanley MS A1 1/20/2023 31 Toyota Motor Credit Corp TOYOTA A1 6/18/2026 32 Bank of New York Mellon Corp/The BK A1 10/15/2026 33 Atmos Energy Corp ATO A1 2/15/2052 34 Morgan Stanley MS A1 1/25/2052 35 Amazon.com Inc AMZN A1 6/3/2050 36 Coca-Cola Co/The KO A1 3/5/2051 37 Wells Fargo & Co WFC A1 4/4/2051 38 PepsiCo Inc PEP A1 7/29/2049 39 Amazon.com Inc AMZN A1 5/12/2061 40 Coca-Cola Co/The KO A1 3/15/2028 Yld to Mty (Mid) Mid Price 2.402028123 100.0715 2.004594686 99.5085 1.143790337 99.548 2.620842964 102.6695 2.502300504 101.919 2.946818188 102.0335 1.861333462 100.2615 2.385303773 97.8355 0.910263161 99.823 2.166185637 98.541 2.627934314 119.8555 2.027226667 100.6275 2.660959066 132.006 2.738813273 107.281 1.897933017 95.7655 0.221312072 100.0865 2.114539195 98.098 1.666748763 98.6085 1.765663911 105.857 2.107484619 101.298 2.496211887 114.7245 2.663619619 95.7365 2.526298216 115.9715 2.019762341 107.9705 1.172033003 99.243 1.560626112 107.9385 0.903245719 103.5115 1.770461801 98.9145 0.711041049 100.04 1.412572161 98.715 1.466515596 98.016 2.919347069 98.6125 2.809971788 99.2655 2.708973749 95.859 2.689829076 106.2645 3.034993086 138.1245 2.684633898 113.442 2.872041444 108.8965 1.644109655 96.118 Assume the valuation date is 2021-10-28 Please winsorize the years-to-maturity and yield-to-maturity of all A1-Moody's-rated bonds at their 0.05 and 0.95 quantiles before running the ploynomial model Yld to Mty are shown in percentage values Attention: the data entry errors in columns E and F, like "#N/A Field Not Applicable" or "#N/A N/A" will prevent you from calculating the quantiles using the percentile() functions. -->> Solution: apply Data | Filter to uncheck all numbers, only check rows containing such data entry errors, then delete these rows, lastly, undo Data Filter to bring back all the normal data entry. You can only apply percentile() function after removing those data entry errors. A B C D E F. G I . ) Please plot the polynomial yield curve model here 2 3 4 o co ou WNP 5 6 7 8 10 11 12 13 14 15 16 17 18 For D20: Calculate Merck's average years-to-maturity in MRK_bond 19 Cost of Debt rD for MRK 20 Average years to maturity of MRK's outstanding bond 21 Impute cost of debt for MRK 22 23 B C D E F Years to Maturity Ticker MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK MRK 1 Name 2 Merck & Co Inc 3 Merck & Co Inc 4 Merck & Co Inc 5 Merck & Co Inc 6 Merck & Co Inc 7 Merck & Co Inc 8 Merck Sharp & Dohme Corp 9 Merck & Co Inc 10 Merck & Co Inc 11 Merck & Co Inc 12 Merck & Co Inc 13 Merck Sharp & Dohme Corp 14 Merck & Co Inc 15 Merck Sharp & Dohme Corp 16 Merck & Co Inc 17 Merck & Co Inc 18 Merck & Co Inc 19 Merck & Co Inc 20 Merck Sharp & Dohme Corp 21 Merck & Co Inc 22 Merck & Co Inc 23 Merck Sharp & Dohme Corp 24 Merck & Co Inc 25 Merck & Co Inc 26 Merck & Co Inc 27 Merck & Co Inc 28 Merck Sharp & Dohme Corp 29 Merck Sharp & Dohme Corp 30 Merck Sharp & Dohme Corp 31 Merck Sharp & Dohme Corp 32 Merck Sharp & Dohme Corp 33 Merck Sharp & Dohme Corp 34 Merck Sharp & Dohme Corp 35 Merck Sharp & Dohme Corp 36 Merck Sharp & Dohme Corp 37 Merck Sharp & Dohme Corp 38 Merck Sharp & Dohme Corp 39 Merck Sharp & Dohme Corp Issue Date Moody's 6/24/2020 A1 6/24/2020 A1 2/10/2015 A1 5/20/2013 A1 6/24/2020 A1 3/7/2019 A1 6/25/2009 A1 6/24/2020 A1 9/13/2012 A1 2/10/2015 A1 2/10/2015 A1 11/30/1998 A1 3/7/2019 A1 3/2/1998 A1 3/7/2019 A1 10/15/2014 A1 10/15/2014 A1 3/7/2019 A1 1/8/1996 A1 9/13/2012 A1 9/17/2007 A1 5/13/1997 A1 11/2/2016 A1 5/20/2013 A1 11/2/2016 A1 11/26/2003 A1 11/14/2006 A1 12/21/2000 A1 11/28/2001 A1 8/23/2002 A1 2/6/2001 A1 6/29/2001 A1 12/27/2000 A1 2/12/2004 A1 2/18/2003 A1 11/27/2000 A1 7/18/2001 A1 11/16/2001 A1 MRK MRK MRK MRK MRK MRK MRK Maturity 6/24/2050 2/24/2026 2/10/2025 5/18/2023 6/24/2030 3/7/2029 6/30/2039 6/24/2040 9/15/2022 2/10/2045 2/10/2022 12/1/2028 3/7/2049 3/1/2028 3/7/2024 10/15/2034 10/15/2026 3/7/2039 1/1/2026 9/15/2042 9/15/2037 5/3/2037 11/2/2024 5/18/2043 11/2/2036 12/1/2033 11/15/2036 12/21/2040 11/28/2041 8/22/2042 2/6/2041 6/21/2041 12/27/2040 2/12/2044 2/18/2043 11/27/2040 7/18/2041 12/21/2041 MRK MRK MRK MRK MRK MRK MRK MRK MRK 2222222 MRK MRK MRK MRK A B C D E F G Years to Maturity PEP PEP WFC 1 Issuer Name Ticker Moody Rtg Maturity 2 Morgan Stanley MS A1 10/20/2032 3 PepsiCo Inc A1 10/21/2031 4 Morgan Stanley MS A1 10/21/2025 5 PepsiCo Inc PEP A1 10/21/2051 6 PepsiCo Inc PEP A1 10/21/2041 7 Intel Corp INTC A1 8/12/2051 8 Bank of New York Mellon Corp/The BK A1 1/25/2029 9 Morgan Stanley MS A1 7/21/2032 10 Bank of New York Mellon Corp/The BK A1 10/25/2024 11 Intel Corp INTC A1 8/12/2031 12 PepsiCo Inc A1 3/19/2050 13 Amazon.com Inc AMZN A1 5/12/2031 14 PepsiCo Inc PEP A1 4/14/2046 15 Amazon.com Inc AMZN A1 5/12/2051 16 PepsiCo Inc PEP A1 2/25/2031 17 Bank of New York Mellon Corp/The BK A1 10/25/2024 18 Toyota Motor Credit Corp TOYOTA A1 9/12/2031 19 Morgan Stanley MS A1 7/20/2027 20 Wells Fargo & Co A1 10/23/2026 21 Coca-Cola Co/The KO A1 1/5/2032 22 PepsiCo Inc PEP A1 3/19/2040 23 Merck & Co Inc MRK A1 6/24/2050 24 Wells Fargo & Co WFC A1 4/4/2031 25 3M Co MMM A1 4/15/2030 26 Amazon.com Inc AMZN A1 5/12/2026 27 Toyota Motor Credit Corp TOYOTA A1 10/24/2025 28 Bank of New York Mellon Corp/The BK A1 10/24/2024 29 Intel Corp INTC A1 8/12/2028 30 Morgan Stanley MS A1 1/20/2023 31 Toyota Motor Credit Corp TOYOTA A1 6/18/2026 32 Bank of New York Mellon Corp/The BK A1 10/15/2026 33 Atmos Energy Corp ATO A1 2/15/2052 34 Morgan Stanley MS A1 1/25/2052 35 Amazon.com Inc AMZN A1 6/3/2050 36 Coca-Cola Co/The KO A1 3/5/2051 37 Wells Fargo & Co WFC A1 4/4/2051 38 PepsiCo Inc PEP A1 7/29/2049 39 Amazon.com Inc AMZN A1 5/12/2061 40 Coca-Cola Co/The KO A1 3/15/2028 Yld to Mty (Mid) Mid Price 2.402028123 100.0715 2.004594686 99.5085 1.143790337 99.548 2.620842964 102.6695 2.502300504 101.919 2.946818188 102.0335 1.861333462 100.2615 2.385303773 97.8355 0.910263161 99.823 2.166185637 98.541 2.627934314 119.8555 2.027226667 100.6275 2.660959066 132.006 2.738813273 107.281 1.897933017 95.7655 0.221312072 100.0865 2.114539195 98.098 1.666748763 98.6085 1.765663911 105.857 2.107484619 101.298 2.496211887 114.7245 2.663619619 95.7365 2.526298216 115.9715 2.019762341 107.9705 1.172033003 99.243 1.560626112 107.9385 0.903245719 103.5115 1.770461801 98.9145 0.711041049 100.04 1.412572161 98.715 1.466515596 98.016 2.919347069 98.6125 2.809971788 99.2655 2.708973749 95.859 2.689829076 106.2645 3.034993086 138.1245 2.684633898 113.442 2.872041444 108.8965 1.644109655 96.118 Assume the valuation date is 2021-10-28 Please winsorize the years-to-maturity and yield-to-maturity of all A1-Moody's-rated bonds at their 0.05 and 0.95 quantiles before running the ploynomial model Yld to Mty are shown in percentage values Attention: the data entry errors in columns E and F, like "#N/A Field Not Applicable" or "#N/A N/A" will prevent you from calculating the quantiles using the percentile() functions. -->> Solution: apply Data | Filter to uncheck all numbers, only check rows containing such data entry errors, then delete these rows, lastly, undo Data Filter to bring back all the normal data entry. You can only apply percentile() function after removing those data entry errors. A B C D E F. G I . ) Please plot the polynomial yield curve model here 2 3 4 o co ou WNP 5 6 7 8 10 11 12 13 14 15 16 17 18 For D20: Calculate Merck's average years-to-maturity in MRK_bond 19 Cost of Debt rD for MRK 20 Average years to maturity of MRK's outstanding bond 21 Impute cost of debt for MRK 22 23Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started