Calculation of VAR
Submission mode: Word and Excel files should be uploaded onto the Student Central via FNM21 Assignment submission point. Assignment Part I: Parametric VaR (35%) You are expected to calculate the individual VaR for each asset in your Portfolio 1 and your overall portfolio VaR using MS Excel. Report (part 1) requirements: You should explain your findings and the RiskMetrics approach to the calculation of VaR for your allocated portfolio stating its limitations. The report should be submitted using MS Word or Pdf format. (500 words, excluding references) Note: Use continuous compounding in your calculations where appropriate; RiskMetrics volatilities are reported as 1.65σ. You are required to complete this part from the perspective of a US investor. Assignment Part II: Non-Parametric VaR (35%) You are expected to estimate the 1-day 95% VaR and the 1-day 99% VaR for your equity Portfolio 2 using: 1. Historical simulation 1. Weighted historical simulation, using lambda, = 0.995 1. Stressed scenarios incorporated into your historical simulation; Assume equal weighting for the historical simulation. 1. Monte Carlo simulation, using the Geometric Brownian Motion (GBM) model with 10,000 trials, assuming a normal distribution for random number generation, = 1-day and n = 10. Use Random Seed 5. Note: You are required to complete this part from the perspective of a US investor. Foreign exchange risk and correlations should be ignored. Report (part 2) requirements: You should explain your findings stating the relative advantages and disadvantages of the non-parametric VaR approaches used. Your report should be well-referenced, and you are advised to use the results of your calculations to illustrate some of the points you make. The report should be submitted using MS Word or Pdf format. (500 words, excluding references) Assignment Part III: Non-market Risk (30%) You are expected to provide a critical overview of the approaches available to measure and manage a non-market risk allocated to you. Your essay should be well-referenced. (maximum 1500 words, excluding references) Portfolio NamePortfolio 1: Assets Candidate 119111317 Candidate 2210111317 Candidate 339111417 Candidate 4410111417 Candidate 559121315 Candidate 6610121315 Candidate 779121415 Candidate 8810121415 Sheet1 Assets Forward Rate Agreements AmountStartingEndingAgreed Interest Rate (%pa) 1Borrow$ 4,000,000.003mths9mths3.25% 2Invest$ 9,000,000.001mth7mths3.21% 3Borrow$ 5,000,000.006mths9mths3.26% 4Invest$ 6,000,000.009mths12mths3.29% Foreign Exchange Forward Contracts AmountDateAgreed Rate 5Sell60000006mths1.17$/€ 6Buy700000012mths1.23$/€ 7Buy£ 9,000,000.006mths1.33$/£ 8Sell£ 11,000,000.0012mths1.26$/£ Stock Portfolios AmountbetaStock Index 9115000001.05DAX 1052500000.92DAX 11$ 7,000,000.001.12S&P 12$ 10,600,000.000.81S&P 13£ 3,250,000.000.94FTSE 14£ 4,900,000.001.18FTSE Zero Coupon Bonds Nominal ValueMaturity 15$ 8,000,000.006 years 16$ 6,000,000.008 years 17£ 7,000,000.005 years 18£ 2,000,000.004 years Price-Rate 1.65SD RFPrice/rate1.65s EUR.XS1.1540.787540% GBP.XS1.2370.845959% DEM.SE115701.296366% GBP.SE71270.705534% USD.SE28140.897435% EUR.R1801.1620.003550% EUR.R3601.2280.019768% GBP.R1803.5670.014140% GBP.R3603.5680.045293% USD.R0302.5830.001565% USD.R0902.7970.004952% USD.R1803.0260.011423% USD.R3603.2880.041355% GBP.Z043.2510.219386% GBP.Z053.2780.282606% USD.Z053.1690.413552% USD.Z073.2460.588180% USD.Z093.2880.767961% Correlation Matrix EUR.XSGBP.XSDEM.SEGBP.SEUSD.SEEUR.R180EUR.R360GBP.R180GBP.R360USD.R030USD.R090USD.R180USD.R360GBP.Z04GBP.Z05USD.Z05USD.Z07USD.Z09 EUR.XS10.769083-0.034407-0.133518-0.074109-0.154071-0.179048-0.248687-0.358349-0.02710.1042470.0638080.110428-0.159829-0.0946170.0638290.0610990.058619 GBP.XS0.76908310.1349090.020032-0.119516-0.061561-0.072889-0.465151-0.526037-0.2064480.018909-0.0298140.054561-0.241691-0.1582750.0496820.0666180.07476 DEM.SE-0.0344070.13490910.6543840.359274-0.087593-0.091026-0.230411-0.218674-0.03575-0.274874-0.23513-0.095757-0.298579-0.2391340.0315930.006991-0.006107 GBP.SE-0.1335180.0200320.65438410.214959-0.167183-0.171259-0.183427-0.14072-0.289554-0.248387-0.409431-0.385795-0.262439-0.237147-0.229868-0.231921-0.231347 USD.SE-0.074109-0.1195160.3592740.2149591-0.116965-0.253142-0.257055-0.296901-0.029931-0.20789-0.0072950.162532-0.208813-0.1832750.2892490.2631490.246707 EUR.R180-0.154071-0.061561-0.087593-0.167183-0.11696510.8865180.2756130.4161030.1732380.2841120.3471440.305470.5371440.5345670.2781570.2937850.30067 EUR.R360-0.179048-0.072889-0.091026-0.171259-0.2531420.88651810.309590.475760.0900380.2392810.2587320.2144760.5400210.5432080.1601740.1851140.197947 GBP.R180-0.248687-0.465151-0.230411-0.183427-0.2570550.2756130.3095910.9130810.0747710.1414810.1449930.0535480.5079150.4438410.0191060.0357210.045031 GBP.R360-0.358349-0.526037-0.218674-0.14072-0.2969010.4161030.475760.91308110.1149430.1896290.1973550.0852830.601080.5287990.0401060.0563950.065671 USD.R030-0.0271-0.206448-0.03575-0.289554-0.0299310.1732380.0900380.0747710.11494310.6207710.6682370.4952830.0258460.0052260.3011780.2669840.24635 USD.R0900.1042470.018909-0.274874-0.248387-0.207890.2841120.2392810.1414810.1896290.62077110.766570.3920950.1964160.1898780.1483660.1176280.099957 USD.R1800.063808-0.029814-0.23513-0.409431-0.0072950.3471440.2587320.1449930.1973550.6682370.7665710.8614920.3799410.3770920.6203330.5745350.545573 USD.R3600.1104280.054561-0.095757-0.3857950.1625320.305470.2144760.0535480.0852830.4952830.3920950.86149210.4397230.4555850.8809010.8424450.815573 GBP.Z04-0.159829-0.241691-0.298579-0.262439-0.2088130.5371440.5400210.5079150.601080.0258460.1964160.3799410.43972310.9856180.4415920.4626020.471359 GBP.Z05-0.094617-0.158275-0.239134-0.237147-0.1832750.5345670.5432080.4438410.5287990.0052260.1898780.3770920.4555850.98561810.4575790.4783060.486791 USD.Z050.0638290.0496820.031593-0.2298680.2892490.2781570.1601740.0191060.0401060.3011780.1483660.6203330.8809010.4415920.45757910.9912920.979363 USD.Z070.0610990.0666180.006991-0.2319210.2631490.2937850.1851140.0357210.0563950.2669840.1176280.5745350.8424450.4626020.4783060.99129210.997447 USD.Z090.0586190.07476-0.006107-0.2313470.2467070.300670.1979470.0450310.0656710.246350.0999570.5455730.8155730.4713590.4867910.9793630.9974471 Marking Criteria Title:FNM21 Portfolio 1 - Parametric Value-at-Risk Calculations Name(s): Group number: CalculationsScoreOut ofNotes Value-at-Risk of foreign stocks5Maximum 5 points available Value-at-Risk of US stocks2Maximum 2 points available Value-at-Risk of foreign bond6 or 4Out of 4 points or 6 points if mapping required Value-at-Risk of forward foreign exchange contract or FRA7 or 9Out of 7 points or 9 points if mapping required Value-at-Risk of portfolio4Maximum 4 points available 024 ReportScoreOut ofNotes Explanation of calculations5Maximum 5 points available Limitations of Value-at-Risk models implemented6Maximum 6 points available 011 Overall035Overall score is out of 35 points Percentage0% Comments