please help! i have no clue what im doing

roblem 1. Catastrophe Bonds: Cape Lookout Re Ltd. (Series 2022-1) (a) In March 2022, $330,000,000 notional of CAT bond was issued and listed in Bermuda Stock Exchange. You can check the listing here. The bond pays the coupon of (Floating Rate) +5%, unless the "indemnity" trigger is activated. The trigger is defined as the sponsor's (the ceding insurance company - North Carolina Insurance Underwriting Association) loss from North Carolina named storm \& severe thunderstorm exceeding $1.85 billion (this is called the "attachment point"), and up to $330 million will be covered. You can read the full description of this deal from here. Assume that you manage a billion dollar fund in a hedge fund, and you are considering to purchase the entire $330 million bond yourself. For simplicity, assume that the CAT bond is 1 year (instead of 3-year in reality) bond with annual coupon payment at the end of the year, and the annualized floating rate is 0.5% (i.e. the total coupon is 5.5% of notional). What is the cashflow and the realized retum on your investment when the total loss from North Carolina's named storm \& severe thunderstorms is (i) \$1 billion (ii) \$2 billion, and (iii) 3 billion? Assume that all the losses occur the day before the maturity, which means you receive the 1-year worth of coupon for the protection you provided during the year. (b) The modeling agent for the deal is RMS (recently acquired by Moody's), and they estimated with their quantitative catastrophe model that the expected loss above $1.85 billion attachment point (up to $330 million) is 1.54% of the notional (i.e. $330 million * 1.54%=5.08 million). One simplified measure that investors look at is how much "multiples" that they expect on coupon compared to the expected loss. For example, in the current Cape Lookout RE Ltd deal, the multiple is (Spread)/(Expected Loss) =5.0%/1.54%=3.25(x). The higher the multiple, the more extra return investors ask for the compensation of the catastrophe risk, so it shows the investors' risk appetite in the market. See here for the yearly time-trend of the market average "multiples". Why do you think we observe the overall downward trend over the years? What can be a possible explanation for the reversed trend after 2017? Problem 2. Correlation between ILS and Stock \& Bond Markets The major investment thesis for investing in ILS (mainly CAT bonds) is the diversification of your investment portfolio. This is achieved because CAT bonds show low correlation with major financial asset classes, such as stocks and bonds. Test this statement yourself by doing the following exercise: (a) In Excel, generate a graph in style of below screenshot, but using different data source and period (from the beginning of 2006 to December 2020; normalize with Beginning 2006=100% ). For ILS, use the Eurekahedge ILS Advisers Index monthly index returns from here. You have to start from 100% at inception, then generate the monthly Index value yourself (For example, if January 2006 return is 0.65%, then End-of-Janury 2006 index value is 100%(1+0.65%)=100.65%, and so on). For Stock index, use S\&P 500 Total Return from Yahoo Finance linked here, but make sure that Dec 01, 2021 value is End-of-Dec2021 index value, not the beginning; you can check it from the daily index values). For bond returns, use ICE BofA US High Yield Index Total Return Index from FRED here, with applying the setting the Period =20060101 to 2020-12-31 and "Edit Graph" setting to be "Monthly" with "End-of-Period" option (12-01-2020 in Excel file again means the End-of-December2021 index value). Then combine all series in one Excel file, normalize to 100% at inception accordingly, then generate the graph similar to below. (You don't have to perfectly mimic the style, but it will be a good practice for you) (b) Report three pairwise correlation numbers. That is, fill in the correlation table below (only the lower-left triangle, as they are symmetric). You can easily do this using "CORREL" function in Excel. roblem 1. Catastrophe Bonds: Cape Lookout Re Ltd. (Series 2022-1) (a) In March 2022, $330,000,000 notional of CAT bond was issued and listed in Bermuda Stock Exchange. You can check the listing here. The bond pays the coupon of (Floating Rate) +5%, unless the "indemnity" trigger is activated. The trigger is defined as the sponsor's (the ceding insurance company - North Carolina Insurance Underwriting Association) loss from North Carolina named storm \& severe thunderstorm exceeding $1.85 billion (this is called the "attachment point"), and up to $330 million will be covered. You can read the full description of this deal from here. Assume that you manage a billion dollar fund in a hedge fund, and you are considering to purchase the entire $330 million bond yourself. For simplicity, assume that the CAT bond is 1 year (instead of 3-year in reality) bond with annual coupon payment at the end of the year, and the annualized floating rate is 0.5% (i.e. the total coupon is 5.5% of notional). What is the cashflow and the realized retum on your investment when the total loss from North Carolina's named storm \& severe thunderstorms is (i) \$1 billion (ii) \$2 billion, and (iii) 3 billion? Assume that all the losses occur the day before the maturity, which means you receive the 1-year worth of coupon for the protection you provided during the year. (b) The modeling agent for the deal is RMS (recently acquired by Moody's), and they estimated with their quantitative catastrophe model that the expected loss above $1.85 billion attachment point (up to $330 million) is 1.54% of the notional (i.e. $330 million * 1.54%=5.08 million). One simplified measure that investors look at is how much "multiples" that they expect on coupon compared to the expected loss. For example, in the current Cape Lookout RE Ltd deal, the multiple is (Spread)/(Expected Loss) =5.0%/1.54%=3.25(x). The higher the multiple, the more extra return investors ask for the compensation of the catastrophe risk, so it shows the investors' risk appetite in the market. See here for the yearly time-trend of the market average "multiples". Why do you think we observe the overall downward trend over the years? What can be a possible explanation for the reversed trend after 2017? Problem 2. Correlation between ILS and Stock \& Bond Markets The major investment thesis for investing in ILS (mainly CAT bonds) is the diversification of your investment portfolio. This is achieved because CAT bonds show low correlation with major financial asset classes, such as stocks and bonds. Test this statement yourself by doing the following exercise: (a) In Excel, generate a graph in style of below screenshot, but using different data source and period (from the beginning of 2006 to December 2020; normalize with Beginning 2006=100% ). For ILS, use the Eurekahedge ILS Advisers Index monthly index returns from here. You have to start from 100% at inception, then generate the monthly Index value yourself (For example, if January 2006 return is 0.65%, then End-of-Janury 2006 index value is 100%(1+0.65%)=100.65%, and so on). For Stock index, use S\&P 500 Total Return from Yahoo Finance linked here, but make sure that Dec 01, 2021 value is End-of-Dec2021 index value, not the beginning; you can check it from the daily index values). For bond returns, use ICE BofA US High Yield Index Total Return Index from FRED here, with applying the setting the Period =20060101 to 2020-12-31 and "Edit Graph" setting to be "Monthly" with "End-of-Period" option (12-01-2020 in Excel file again means the End-of-December2021 index value). Then combine all series in one Excel file, normalize to 100% at inception accordingly, then generate the graph similar to below. (You don't have to perfectly mimic the style, but it will be a good practice for you) (b) Report three pairwise correlation numbers. That is, fill in the correlation table below (only the lower-left triangle, as they are symmetric). You can easily do this using "CORREL" function in Excel