The excel file is large and I couldn't upload it all here. You can use the data up to 25 to do the calculations by noting what I should do in the excel document. Please give me the directions on what to do in the excel. Thank you.
The discount rate for this SFP is the yield on the comparable U.S. government bond plus 200 basis points The Purpose This is an individual assignment designed to enhance students' knowledge in the following areas: an understanding of and ability to identify the financial risks faced by individuals and corporations; an ability to undertake statistical analysis, including calculation of alternative portfolio risk measures and statistics in Excel; familiarity with the construction of tables and graphics and their integration into written reports; and high-level written communication skills. Information: Your employer, an Australian-based investment bank, has issued a Structured Financial Product (SFP) to high net worth investors. You have been requested to determine the risk exposure to the bank and to hedge the market financial risks associated with the payoff at maturity on the SFP. You have the following information on this SFP, as well as other information on key financial variables: You have been provided with time series data (as of the end of June 2020, the date you will assume as 'now/today' for undertaking your calculations) in the form of the yield on U.S. government bonds (% p.a.), the USD/AUD exchange rate, and the value of the S&P 500 Index (see the spreadsheet provided with the Assignment DATA.xlsx The Task Prepare a brief (i.e., no more than 3 pages) report in which you address the following: a) You are informed that there might be resistances within the management to your risk management activities. What could their argument be and what would be your counter arguments? (10 marks) b) Your bank is especially concerned about extreme adverse movements associated with the payoff of this SFP. Provide estimates on the potential exposure(s) associated with the payoff at maturity of the SFP. (40 marks) (Hint 1: the exposure must relate to any amount in excess of USD25,000 in AUD that the investment bank may be required to pay out at maturity and should consider both likely payoffs and the probabilities of these payoffs.) (Hint 2: you should consider measures such as value-at-risk (VaR) to assist you in determining the exposure(s). You can ignore the time value of money for this part since we The face value of the SFP is USD25,000 The payoff is due in one year Payoff at maturity is max{USD25,000, [(SP500-/SP500. )x USD25,000]} SP500 represents the value of the S&P 500 Index at maturity of the investment product and SP500, the value of the S&P 500 Index at inception of the SFP (1.e., the end of June 2020) USD/AUD represents the US dollar/Australian dollar exchange rate (price of AUD 1 in USD) the yield the comparable U.S. government bond plus 200 The discount rate for this basis points are exclusively talking about the payoffs at maturity. Any proper attempts to further assess the extreme risk will be rewarded.) c) Your macroeconomic team has estimated that the escalation or ending of the ongoing U.S.- China trade war in 6 months would significantly impact this SFP. The escalation would cause the S&P500 index to decline by 10% and the AUD to appreciate against the USD by 5%. The ending would have the opposite effect on the equity and currency markets. Briefly outline the value of the SFP given these two scenarios. Propose another scenario that would significantly impact the SFP. (20 marks) (Hint 3: keep the quantitative part simple. You should justify your proposed scenario without calculations.) What to Submit and when? Each student should submit one copy of the following via learnonline: 1. A WORD or PDF file (of no more than 3 pages) by 5pm, 6 Nov 2020. Supporting materials such as tables, figures, references, and appendices do not count towards the page limit. There is no word limit. The word limit shown in the course outline is the 'word-equivalent' limit. It does not strictly apply to word count because we have quantitative elements. 2. An Excel file for calculation references by 5pm, 6 Nov 2020. Note that this file is not graded. The grader may reference the Excel file if the main report is unclear or ambiguous. Therefore, there is minimum expectation in terms of formatting and explanation. d) Provide a brief, but critical, evaluation of the limitations and model risks of each of the methods that you have used to develop the estimates in part a) and b). (20 marks) e) Briefly outline how you would hedge against the market risk exposure(s) associated with the payoff on this structured investment product. (10 marks) (Hint you should identify the portfolio of simple contracts that you would use to hedge this exposure.) 1 1 US Government Liabilities Spot Rates (% p.a.) 2 S&P500 Share Price Index 1191.33 1 year Exchange Rate (USD/AUD) 0.7625 0.7568 3 6 months 3.3400 3.6560 4 1234.18 2 year 3.6660 3.9490 3.9510 4.1270 30-Jun-05 29-Jul-05 31-Aug-05 30-Sep-05 31-Oct-05 1220.33 3.7970 5 6 7 7 0.7550 0.7612 1228.81 1207.01 0.7485 4.3860 30-Nov-05 4.3940 8 9 30-Dec-05 4.3670 10 11 12 13 31-Jan-06 28-Feb-06 31-Mar-06 28-Apr-06 31-May-06 30-Jun-06 31-Jul-06 31-Aug-06 29-Sep-06 31-Oct-06 1249.48 1248.29 1280.08 1280.66 1294.83 1310.61 1270.09 1270.20 1276.66 1303.82 1335.85 1377.94 1400.63 1418.30 0.7385 0.7336 0.7587 0.7425 0.7163 0.7577 0.7524 0.7423 0.7663 0.7640 0.7465 0.7743 14 3.8860 4.2170 4.3140 4.3430 4.6000 4.7540 4.8260 4.9340 5.0360 5.2590 5.1450 5.1360 5.0030 5.1430 5.1210 5.0990 4.5110 4.7330 4.8360 4.9040 4.9750 5.1920 4.9790 4.8250 4.6620 4.7590 15 16 17 18 19 20 30-Nov-06 0.7886 4.6870 4.8000 21 22 29-Dec-06 31-Jan-07 28-Feb-07 0.7893 0.7768 0.7881 1438.24 1406.82 1420.86 4.9660 4.6380 23 24 30-Mar-07 30-Apr-07 31-May-07 0.8086 0.8308 4.5870 4.6660 5.1640 5.1080 5.0680 5.0300 4.9820 25 1482.37 26 1530.62 1rnar 0.8278 nnnn 4.8790 oran 10nrn The discount rate for this SFP is the yield on the comparable U.S. government bond plus 200 basis points The Purpose This is an individual assignment designed to enhance students' knowledge in the following areas: an understanding of and ability to identify the financial risks faced by individuals and corporations; an ability to undertake statistical analysis, including calculation of alternative portfolio risk measures and statistics in Excel; familiarity with the construction of tables and graphics and their integration into written reports; and high-level written communication skills. Information: Your employer, an Australian-based investment bank, has issued a Structured Financial Product (SFP) to high net worth investors. You have been requested to determine the risk exposure to the bank and to hedge the market financial risks associated with the payoff at maturity on the SFP. You have the following information on this SFP, as well as other information on key financial variables: You have been provided with time series data (as of the end of June 2020, the date you will assume as 'now/today' for undertaking your calculations) in the form of the yield on U.S. government bonds (% p.a.), the USD/AUD exchange rate, and the value of the S&P 500 Index (see the spreadsheet provided with the Assignment DATA.xlsx The Task Prepare a brief (i.e., no more than 3 pages) report in which you address the following: a) You are informed that there might be resistances within the management to your risk management activities. What could their argument be and what would be your counter arguments? (10 marks) b) Your bank is especially concerned about extreme adverse movements associated with the payoff of this SFP. Provide estimates on the potential exposure(s) associated with the payoff at maturity of the SFP. (40 marks) (Hint 1: the exposure must relate to any amount in excess of USD25,000 in AUD that the investment bank may be required to pay out at maturity and should consider both likely payoffs and the probabilities of these payoffs.) (Hint 2: you should consider measures such as value-at-risk (VaR) to assist you in determining the exposure(s). You can ignore the time value of money for this part since we The face value of the SFP is USD25,000 The payoff is due in one year Payoff at maturity is max{USD25,000, [(SP500-/SP500. )x USD25,000]} SP500 represents the value of the S&P 500 Index at maturity of the investment product and SP500, the value of the S&P 500 Index at inception of the SFP (1.e., the end of June 2020) USD/AUD represents the US dollar/Australian dollar exchange rate (price of AUD 1 in USD) the yield the comparable U.S. government bond plus 200 The discount rate for this basis points are exclusively talking about the payoffs at maturity. Any proper attempts to further assess the extreme risk will be rewarded.) c) Your macroeconomic team has estimated that the escalation or ending of the ongoing U.S.- China trade war in 6 months would significantly impact this SFP. The escalation would cause the S&P500 index to decline by 10% and the AUD to appreciate against the USD by 5%. The ending would have the opposite effect on the equity and currency markets. Briefly outline the value of the SFP given these two scenarios. Propose another scenario that would significantly impact the SFP. (20 marks) (Hint 3: keep the quantitative part simple. You should justify your proposed scenario without calculations.) What to Submit and when? Each student should submit one copy of the following via learnonline: 1. A WORD or PDF file (of no more than 3 pages) by 5pm, 6 Nov 2020. Supporting materials such as tables, figures, references, and appendices do not count towards the page limit. There is no word limit. The word limit shown in the course outline is the 'word-equivalent' limit. It does not strictly apply to word count because we have quantitative elements. 2. An Excel file for calculation references by 5pm, 6 Nov 2020. Note that this file is not graded. The grader may reference the Excel file if the main report is unclear or ambiguous. Therefore, there is minimum expectation in terms of formatting and explanation. d) Provide a brief, but critical, evaluation of the limitations and model risks of each of the methods that you have used to develop the estimates in part a) and b). (20 marks) e) Briefly outline how you would hedge against the market risk exposure(s) associated with the payoff on this structured investment product. (10 marks) (Hint you should identify the portfolio of simple contracts that you would use to hedge this exposure.) 1 1 US Government Liabilities Spot Rates (% p.a.) 2 S&P500 Share Price Index 1191.33 1 year Exchange Rate (USD/AUD) 0.7625 0.7568 3 6 months 3.3400 3.6560 4 1234.18 2 year 3.6660 3.9490 3.9510 4.1270 30-Jun-05 29-Jul-05 31-Aug-05 30-Sep-05 31-Oct-05 1220.33 3.7970 5 6 7 7 0.7550 0.7612 1228.81 1207.01 0.7485 4.3860 30-Nov-05 4.3940 8 9 30-Dec-05 4.3670 10 11 12 13 31-Jan-06 28-Feb-06 31-Mar-06 28-Apr-06 31-May-06 30-Jun-06 31-Jul-06 31-Aug-06 29-Sep-06 31-Oct-06 1249.48 1248.29 1280.08 1280.66 1294.83 1310.61 1270.09 1270.20 1276.66 1303.82 1335.85 1377.94 1400.63 1418.30 0.7385 0.7336 0.7587 0.7425 0.7163 0.7577 0.7524 0.7423 0.7663 0.7640 0.7465 0.7743 14 3.8860 4.2170 4.3140 4.3430 4.6000 4.7540 4.8260 4.9340 5.0360 5.2590 5.1450 5.1360 5.0030 5.1430 5.1210 5.0990 4.5110 4.7330 4.8360 4.9040 4.9750 5.1920 4.9790 4.8250 4.6620 4.7590 15 16 17 18 19 20 30-Nov-06 0.7886 4.6870 4.8000 21 22 29-Dec-06 31-Jan-07 28-Feb-07 0.7893 0.7768 0.7881 1438.24 1406.82 1420.86 4.9660 4.6380 23 24 30-Mar-07 30-Apr-07 31-May-07 0.8086 0.8308 4.5870 4.6660 5.1640 5.1080 5.0680 5.0300 4.9820 25 1482.37 26 1530.62 1rnar 0.8278 nnnn 4.8790 oran 10nrn
