Risk Management and Derivatives Assignment Excel file is for working outs calculations Must show formulas used also Please submit completed assessment on excel spreadsheet and not a pdf Thank you EFB344 Assignment Part A Due Wednesday the 25 th of October, 2022 at 11 59pm Weight 20 of the overall unit Note This is an individual assignment Overview The task you are given is to estimate the market risk for a holding of 10,000 BHP shares (BHP ax) and 1,000 CSL shares (CSL ax), held on September 1 , 2022 (you are working out the risk position assuming that you own these shares before the open of trading that day) You will do this by estimating the Value at Risk for the stock portfolio This will require you to choose the best VaR model by backtesting several methods to determine the most reliable for the task at hand Description You will be asked to calculate the following 10 day VaR for the portfolio of shares at a confidence level of 99 Note This risk estimate applies to the next 10 trading days from September 1, 2022 until September 14, 2022 ( i e it should be a forecast of risk) Based on what you have learnt from EFB344, you are considering several options for how to compute this risk measure, a) the normal distribution using the EWMA, b) the normal distribution using a rolling window, or c ) historical simulation based on a rolling window All methods require choosing parameters to assign weight to past data, for the EWMA and the window length for the other two You will consider the following choices for each Normal EWMA for both volatilities and the covariance Normal Rolling Window Rolling window with 252 trading days Historical Simulation Rolling window with 252 trading days This leaves you with 3 possible models that could be used to provide the VaR measure asked for above You must choose the most appropriate model and report the associated VaR To inform your decision of which to use, you are going to consider the recent historical performance of the three models in calculating 1 day VaR at the confidence level of 99 You will do so by first examining the frequency of instances when the VaR was exceeded by the observed return over the prior five years You will then evaluate the appropriateness of these frequencies over time relative to the Basel traffic light levels discussed in lectures Based on this performance, select the best model and report the required VaR(10, 99 ) for September 1, 2022 Presenting your results You are to conduct your analysis in a copy of the Excel file Assignment Part A Data and Results xlsx provided on Blackboard This file contains the three tabs with the raw data for your analysis as well as a front page for you to summarize your results All working is to be contained in the subsequent tabs The front tab asks you to provide the following Your name and Student number The exceedance probabilities for the three models above A graph summarizing the Basel Traffic Light results (such as the one shown in lecture 3) Which of the 3 models above is your preferred model based on the backtesting results The final VaR(10,99 ) for the portfolio based on your preferred model An evaluation of the relative performance of the various models and a clear justification of which model is superior based on your backtesting This is essentially a discussion of how you should interpret the backtesting results in order to select the most appropriate model This should be no more than 400 words Your excel file should be formatted in a reasonably clear way, so that someone who was given the same job after you would be able to understand your working and replicate what you have done Details of Submission Submit the Excel file through the assignment portal that is available on the EFB344 Risk Management and Derivatives Blackboard site Please include your name and student number in the file name of your document Note that the portal will close after the due date and that any assignments that have been granted official extensions must be emailed to Steve Thiele (sr thiele qut edu au) Also note QUTs late assignment policy Late Assignment No Extension 0 Additional Notes and Instructions I have sourced the raw data for you from Yahoo finance You will need to make it suitable for analysis Your excel spreadsheet must contain the formulas that you have used for all calculations ( i e dont paste the values for the calculations) There is an Excel file available on blackboard (called Excel guide xlsx) that includes instructions for how to do several useful things in excel It also includes some informative examples which might be of interest Please look at this file A few hints that will help stop people going down the wrong path In theory, you should use arithmetic returns as you are constructing a portfolio, but I dont mind if you use log returns (the results will be very similar) You can assume that the mean returns are zero as discussed in the lectures Alternatively, you can estimate them from the data However, you should not use a time varying estimate of the mean The reason is that we dont think the true value changes over time in the same way the volatility does, so any variation is likely to be random noise The portfolio weights should stay fixed throughout your backtesting at the values they are as of September 1, 2022 It is vital that you match the dates for the two stock returns used in your analysis It is not enough to match them on the first day and hope it works out You can only use days where you have a return for each stock You measured covariance will be vastly understated if you allow the two sets of returns to fall out of sync You should initialise your EWMA using the sample variance covariance of the first 252 daily returns This will enable you to produce the same number of potential exceedances for both models While you have around six years of data, your will only be able to produce around five years of VaR estimates, and your Basel Traffic lights can only be calculated for around four years This should become clear as you go through the process of calculating what is asked for Criteria and Standards Sheet for Assignment Part A (30 marks) Marking Criteria High Distinction Distinction Credit Pass Fail Mark KS (1 1) Demonstrate and apply integrated discipline (including technical) knowledge Subject Knowledge Demonstrates comprehensive understanding of relevant finance and risk management concepts and techniques Demonstrates a developed understanding of relevant finance and risk management concepts and techniques Demonstrates a developed understanding of relevant finance and risk management concepts and techniques but with a few errors Demonstrates an adequate understanding of relevant finance and risk management concepts and techniques Insufficient or inaccurate understanding of relevant finance and risk management concepts and techniques 18 KS (1 2) Apply technical and technological skills appropriate and effective for real world business contexts Excel Use and Formatting Document prepared and formatted according to standards required by the subject Document generally prepared and formatted according to standards required by the subject, but with a small number of minor errors Document generally prepared and formatted according to standards required by the subject, but contains some errors Frequent errors, but displays an ability to prepare and format the document according to standards required by the subject Fails to format the document to an appropriate standard required by the subject 5 HO (2 2) Exercise independent judgement and initiative in adapting and applying knowledge and skills for effective problem solving Critical Analysis Provides a clear and well reasoned justification for the choice of superior risk management model that fully considers the empirical findings Provides a strong justification for the choice of superior risk management model that is supported by the empirical findings Provides a solid justification for the choice of superior risk management model which is generally consistent with the empirical findings Provides only weak justification for the choice of superior risk management model which barely takes account of the empirical findings Fails to provide a justification for the choice of superior risk management model and or the arguments are at odds with the empirical findings 7 Overall Grade HD, D, C, P, F Overall Mark Comments An overall mark is awarded to the group, but can be adjusted for individuals at the discretion of the unit coordinator

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Apr 02, 2024

Risk Management and Derivatives Assignment. Excel file is for working outs/calculations. Must show formulas used also. Please submit completed assessment on excel spreadsheet and not

Risk Management and Derivatives Assignment. Excel file is for working outs/calculations. Must show formulas used also. Please submit completed assessment on excel spreadsheet and not a pdf. Thank you! EFB344 Assignment - Part A Due: Wednesday the 25^th of October, 2022 at 11:59pm Weight: 20% of the overall unit Note: This is an individual assignment. Overview The task you are given is to estimate the market risk for a holding of 10,000 BHP shares (BHP.ax) and 1,000 CSL shares (CSL.ax), held on September 1 , 2022 (you are working out the risk position assuming that you own these shares before the open of trading that day). You will do this by estimating the Value-at-Risk for the stock portfolio. This will require you to choose the best VaR model by backtesting several methods to determine the most reliable for the task at hand. Description You will be asked to calculate the following;

10 day VaR for the portfolio of shares at a confidence level of 99%.

Note: This risk estimate applies to the next 10 trading days from September 1, 2022 until September 14, 2022 (i.e. it should be a forecast of risk). Based on what you have learnt from EFB344, you are considering several options for how to compute this risk measure, a) the normal distribution using the EWMA, b) the normal distribution using a rolling window, or c) historical simulation based on a rolling window. All methods require choosing parameters to assign weight to past data, for the EWMA and the window length for the other two. You will consider the following choices for each;

Normal EWMA	for both volatilities and the covariance.
Normal Rolling Window	Rolling window with 252 trading days.
Historical Simulation	Rolling window with 252 trading days.

This leaves you with 3 possible models that could be used to provide the VaR measure asked for above. You must choose the most appropriate model and report the associated VaR. To inform your decision of which to use, you are going to consider the recent historical performance of the three models in calculating 1 day VaR at the confidence level of 99%. You will do so by first examining the frequency of instances when the VaR was exceeded by the observed return over the prior five years. You will then evaluate the appropriateness of these frequencies over time relative to the Basel traffic light levels discussed in lectures. Based on this performance, select the best model and report the required VaR(10, 99%) for September 1, 2022. Presenting your results

You are to conduct your analysis in a copy of the Excel file Assignment_Part_A Data and Results.xlsx provided on Blackboard. This file contains the three tabs with the raw data for your analysis as well as a front page for you to summarize your results. All working is to be contained in the subsequent tabs.
The front tab asks you to provide the following
- Your name and Student number
- The exceedance probabilities for the three models above.
- A graph summarizing the Basel Traffic Light results (such as the one shown in lecture 3).
- Which of the 3 models above is your preferred model based on the backtesting results.
- The final VaR(10,99%) for the portfolio based on your preferred model.
- An evaluation of the relative performance of the various models and a clear justification of which model is superior based on your backtesting. This is essentially a discussion of how you should interpret the backtesting results in order to select the most appropriate model. This should be no more than 400 words.
Your excel file should be formatted in a reasonably clear way, so that someone who was given the same job after you would be able to understand your working and replicate what you have done.

Details of Submission

Submit the Excel file through the assignment portal that is available on the EFB344 Risk Management and Derivatives Blackboard site. Please include your name and student number in the file name of your document. Note that the portal will close after the due date and that any assignments that have been granted official extensions must be emailed to Steve Thiele (sr.thiele@qut.edu.au).
Also note QUTs late assignment policy:

Late Assignment + No Extension = 0% Additional Notes and Instructions

I have sourced the raw data for you from Yahoo finance. You will need to make it suitable for analysis.
Your excel spreadsheet must contain the formulas that you have used for all calculations (i.e. dont paste the values for the calculations).
There is an Excel file available on blackboard (called Excel guide.xlsx) that includes instructions for how to do several useful things in excel. It also includes some informative examples which might be of interest. Please look at this file.
A few hints that will help stop people going down the wrong path;
- In theory, you should use arithmetic returns as you are constructing a portfolio, but I dont mind if you use log returns (the results will be very similar).
- You can assume that the mean returns are zero as discussed in the lectures. Alternatively, you can estimate them from the data. However, you should not use a time-varying estimate of the mean. The reason is that we dont think the true value changes over time in the same way the volatility does, so any variation is likely to be random noise.
- The portfolio weights should stay fixed throughout your backtesting at the values they are as of September 1, 2022.
- It is vital that you match the dates for the two stock returns used in your analysis. It is not enough to match them on the first day and hope it works out. You can only use days where you have a return for each stock. You measured covariance will be vastly understated if you allow the two sets of returns to fall out of sync.
- You should initialise your EWMA using the sample variance/covariance of the first 252 daily returns. This will enable you to produce the same number of potential exceedances for both models.
- While you have around six years of data, your will only be able to produce around five years of VaR estimates, and your Basel Traffic lights can only be calculated for around four years. This should become clear as you go through the process of calculating what is asked for

Criteria and Standards Sheet for Assignment Part A (30 marks)

Marking Criteria	High Distinction	Distinction	Credit	Pass	Fail	Mark
*KS (1.1): Demonstrate and apply integrated discipline (including technical) knowledge*
Subject Knowledge	Demonstrates comprehensive understanding of relevant finance and risk management concepts and techniques.	Demonstrates a developed understanding of relevant finance and risk management concepts and techniques.	Demonstrates a developed understanding of relevant finance and risk management concepts and techniques but with a few errors.	Demonstrates an adequate understanding of relevant finance and risk management concepts and techniques.	Insufficient or inaccurate understanding of relevant finance and risk management concepts and techniques.	/18
*KS (1.2): Apply technical and technological skills appropriate and effective for real world business contexts*
Excel Use and Formatting	Document prepared and formatted according to standards required by the subject.	Document generally prepared and formatted according to standards required by the subject, but with a small number of minor errors	Document generally prepared and formatted according to standards required by the subject, but contains some errors	Frequent errors, but displays an ability to prepare and format the document according to standards required by the subject.	Fails to format the document to an appropriate standard required by the subject	/ 5
*HO (2.2): Exercise independent judgement and initiative in adapting and applying knowledge and skills for effective problem solving*
Critical Analysis	Provides a clear and well-reasoned justification for the choice of superior risk management model that fully considers the empirical findings.	Provides a strong justification for the choice of superior risk management model that is supported by the empirical findings.	Provides a solid justification for the choice of superior risk management model which is generally consistent with the empirical findings.	Provides only weak justification for the choice of superior risk management model which barely takes account of the empirical findings.	Fails to provide a justification for the choice of superior risk management model and/or the arguments are at odds with the empirical findings.	/7

Overall Grade: HD, D, C, P, F Overall Mark: ________ Comments: An overall mark is awarded to the group, but can be adjusted for individuals at the discretion of the unit coordinator.