Requirements are in the attachments. Please help with the assignment. Price can be adjusted.

Date 31-Mar-88 28-Apr-88 31-May-88 30-Jun-88 29-Jul-88 31-Aug-88 29-Sep-88 31-Oct-88 30-Nov-88 29-Dec-88 31-Jan-89 28-Feb-89 30-Mar-89 28-Apr-89 31-May-89 29-Jun-89 31-Jul-89 31-Aug-89 28-Sep-89 31-Oct-89 30-Nov-89 31-Dec-89 31-Jan-90 28-Feb-90 30-Mar-90 30-Apr-90 30-May-90 29-Jun-90 31-Jul-90 29-Aug-90 28-Sep-90 31-Oct-90 28-Nov-90 31-Dec-90 30-Jan-91 27-Feb-91 29-Mar-91 30-Apr-91 29-May-91 30-Jun-91 31-Jul-91 Annualized 1-month FinancialFinancial Risk-free Asset X Asset Y (cnt cmp) $93.29 $90.31 $92.00 $93.47 $96.71 $98.21 $100.82 $98.74 $97.13 $97.01 $97.45 $94.52 $93.16 $93.02 $88.33 $86.01 $89.12 $90.16 $90.10 $85.43 $86.11 $85.02 $85.08 $86.97 $80.46 $82.85 $84.89 $86.59 $88.32 $86.28 $86.25 $83.98 $84.98 $83.07 $80.82 $80.63 $83.09 $86.90 $83.98 $89.33 $89.46 $217.32 $216.52 $216.72 $217.97 $223.12 $219.69 $223.10 $224.03 $227.39 $234.07 $240.86 $232.69 $232.39 $237.78 $239.08 $237.85 $236.17 $240.20 $248.02 $243.22 $244.25 $248.95 $253.12 $254.33 $246.51 $254.24 $252.86 $255.10 $252.40 $258.69 $264.94 $272.30 $269.08 $270.26 $278.22 $277.96 $297.56 $324.31 $328.57 $343.38 $349.47 6.497% 6.049% 6.332% 6.505% 6.517% 7.535% 8.019% 8.536% 8.973% 8.605% 9.526% 9.526% 9.626% 9.569% 9.668% 8.931% 9.098% 9.891% 10.055% 11.520% 10.274% 9.344% 11.129% 13.616% 15.051% 9.421% 7.830% 6.201% 7.473% 9.136% 10.795% 11.151% 14.477% 12.379% 13.919% 13.972% 13.017% 13.819% 16.146% 14.411% 14.761% 30-Aug-91 30-Sep-91 30-Oct-91 29-Nov-91 31-Dec-91 29-Jan-92 26-Feb-92 31-Mar-92 30-Apr-92 29-May-92 30-Jun-92 30-Jul-92 31-Aug-92 30-Sep-92 29-Oct-92 30-Nov-92 31-Dec-92 29-Jan-93 26-Feb-93 31-Mar-93 29-Apr-93 31-May-93 30-Jun-93 29-Jul-93 31-Aug-93 30-Sep-93 29-Oct-93 30-Nov-93 30-Dec-93 31-Jan-94 28-Feb-94 30-Mar-94 29-Apr-94 31-May-94 29-Jun-94 29-Jul-94 31-Aug-94 28-Sep-94 31-Oct-94 30-Nov-94 30-Dec-94 31-Jan-95 28-Feb-95 31-Mar-95 28-Apr-95 $87.45 $82.28 $81.76 $84.51 $81.97 $83.34 $84.67 $86.43 $86.16 $84.74 $82.32 $82.43 $81.46 $85.63 $84.67 $86.36 $92.33 $91.50 $98.14 $110.24 $117.07 $110.53 $116.39 $119.24 $111.96 $114.63 $123.63 $125.53 $127.70 $131.73 $125.55 $125.45 $126.69 $121.15 $123.69 $115.41 $122.10 $130.53 $133.38 $134.24 $136.40 $142.73 $144.98 $147.37 $153.38 $348.18 $318.48 $318.36 $319.50 $332.31 $338.94 $323.13 $304.97 $314.39 $313.25 $306.64 $297.71 $303.06 $300.23 $311.05 $334.73 $346.88 $338.46 $341.02 $355.76 $360.99 $363.87 $375.17 $381.86 $384.91 $396.01 $390.79 $391.88 $420.83 $445.89 $441.65 $500.01 $495.12 $442.81 $454.44 $435.20 $467.13 $472.58 $495.53 $501.49 $528.23 $533.27 $559.34 $573.22 $553.62 14.968% 13.404% 12.718% 10.244% 9.249% 11.929% 11.915% 13.506% 12.273% 11.455% 11.509% 8.919% 6.211% 6.974% 7.703% 7.628% 8.060% 8.037% 7.425% 8.545% 8.111% 8.077% 8.381% 8.919% 8.321% 8.755% 8.446% 8.505% 8.643% 8.884% 8.372% 9.553% 9.263% 7.790% 9.358% 9.650% 10.719% 10.433% 8.576% 7.462% 7.598% 7.514% 7.259% 8.091% 7.353% 31-May-95 28-Jun-95 31-Jul-95 30-Aug-95 29-Sep-95 31-Oct-95 29-Nov-95 31-Dec-95 31-Jan-96 28-Feb-96 29-Mar-96 30-Apr-96 30-May-96 28-Jun-96 31-Jul-96 29-Aug-96 30-Sep-96 31-Oct-96 28-Nov-96 31-Dec-96 30-Jan-97 27-Feb-97 31-Mar-97 30-Apr-97 29-May-97 30-Jun-97 31-Jul-97 29-Aug-97 30-Sep-97 30-Oct-97 28-Nov-97 31-Dec-97 30-Jan-98 27-Feb-98 31-Mar-98 29-Apr-98 29-May-98 30-Jun-98 29-Jul-98 31-Aug-98 30-Sep-98 30-Oct-98 30-Nov-98 30-Dec-98 29-Jan-99 $168.64 $179.55 $170.66 $186.22 $198.20 $223.06 $217.33 $246.76 $248.53 $242.84 $265.11 $278.78 $250.89 $246.72 $232.83 $266.18 $249.25 $252.70 $263.39 $259.83 $228.25 $215.09 $222.25 $221.67 $217.35 $231.87 $245.07 $248.29 $241.88 $186.63 $159.28 $156.56 $145.62 $169.74 $166.33 $164.81 $167.66 $176.92 $182.94 $182.35 $195.56 $203.71 $198.21 $207.94 $208.56 $573.15 $590.24 $564.00 $578.81 $583.76 $586.70 $569.61 $599.12 $591.79 $617.32 $740.58 $724.52 $747.62 $774.35 $822.28 $899.80 $921.51 $846.51 $892.55 $944.96 $1,056.70 $1,069.97 $1,132.13 $1,260.97 $1,286.40 $1,215.10 $1,196.08 $1,274.24 $1,266.40 $1,106.50 $1,099.51 $1,022.33 $1,132.87 $1,224.19 $1,272.93 $1,292.99 $1,236.11 $1,269.93 $1,318.26 $1,254.26 $1,289.04 $1,298.50 $1,378.98 $1,420.51 $1,478.36 6.321% 6.631% 6.954% 6.739% 7.068% 7.148% 6.678% 5.905% 6.784% 6.454% 6.272% 5.456% 5.771% 5.885% 5.632% 4.194% 5.111% 5.130% 5.151% 4.683% 5.581% 5.367% 5.202% 2.507% 5.387% 4.927% 5.601% 5.661% 6.424% 4.759% 3.513% 3.370% 3.259% 5.172% 5.120% 5.415% 5.864% 6.087% 5.557% 7.342% 7.065% 6.741% 6.455% 5.608% 7.883% 26-Feb-99 31-Mar-99 28-Apr-99 31-May-99 30-Jun-99 30-Jul-99 31-Aug-99 29-Sep-99 29-Oct-99 30-Nov-99 29-Dec-99 31-Jan-00 28-Feb-00 30-Mar-00 28-Apr-00 31-May-00 29-Jun-00 31-Jul-00 31-Aug-00 28-Sep-00 31-Oct-00 30-Nov-00 29-Dec-00 31-Jan-01 28-Feb-01 29-Mar-01 30-Apr-01 31-May-01 28-Jun-01 31-Jul-01 30-Aug-01 27-Sep-01 31-Oct-01 29-Nov-01 31-Dec-01 31-Jan-02 28-Feb-02 29-Mar-02 30-Apr-02 29-May-02 28-Jun-02 31-Jul-02 30-Aug-02 30-Sep-02 30-Oct-02 $202.75 $206.71 $215.52 $220.01 $231.16 $243.42 $251.89 $251.04 $234.54 $251.08 $285.30 $292.26 $287.86 $315.76 $290.41 $295.49 $302.44 $310.21 $261.06 $213.22 $233.31 $234.72 $225.56 $229.14 $250.67 $241.70 $255.28 $270.52 $256.82 $255.21 $258.24 $250.98 $246.80 $243.39 $243.30 $259.52 $269.73 $264.73 $264.83 $273.06 $263.43 $240.64 $228.15 $220.08 $223.25 $1,469.94 $1,483.63 $1,485.78 $1,508.80 $1,444.45 $1,558.26 $1,530.86 $1,572.28 $1,564.95 $1,645.09 $1,655.25 $1,561.09 $1,442.34 $1,232.89 $1,255.07 $1,375.75 $1,304.75 $1,241.22 $1,098.87 $883.00 $1,030.64 $931.75 $994.14 $990.68 $1,121.38 $1,114.88 $1,111.80 $1,116.93 $1,032.79 $1,063.65 $999.18 $1,054.01 $1,085.61 $999.03 $989.44 $945.85 $893.67 $820.60 $770.88 $796.76 $713.58 $711.60 $809.81 $769.32 $754.17 7.536% 8.676% 8.472% 8.311% 8.197% 7.843% 7.688% 7.605% 7.689% 5.823% 5.853% 7.178% 7.301% 8.105% 7.517% 6.434% 7.310% 7.465% 7.391% 7.034% 6.597% 6.953% 5.447% 5.527% 5.375% 5.843% 5.416% 4.336% 5.150% 5.497% 5.293% 4.989% 4.703% 3.940% 3.717% 3.482% 3.726% 4.011% 3.238% 3.269% 3.513% 2.994% 3.025% 2.588% 2.547% 29-Nov-02 31-Dec-02 29-Jan-03 26-Feb-03 31-Mar-03 30-Apr-03 30-May-03 30-Jun-03 30-Jul-03 29-Aug-03 30-Sep-03 29-Oct-03 28-Nov-03 31-Dec-03 30-Jan-04 27-Feb-04 31-Mar-04 29-Apr-04 31-May-04 30-Jun-04 30-Jul-04 31-Aug-04 30-Sep-04 29-Oct-04 30-Nov-04 31-Dec-04 $229.60 $228.38 $234.24 $251.39 $252.95 $245.02 $244.71 $250.96 $267.10 $285.71 $281.87 $306.23 $304.73 $327.29 $318.82 $305.78 $309.12 $324.86 $303.28 $294.22 $307.85 $316.72 $292.54 $299.80 $295.52 $301.87 $781.43 $770.06 $766.56 $756.32 $839.53 $953.99 $946.04 $919.47 $967.47 $989.05 $951.11 $968.96 $813.13 $858.19 $973.03 $971.71 $917.16 $944.00 $994.34 $983.00 $961.93 $968.02 $932.31 $937.87 $908.58 $926.17 2.670% 2.761% 2.527% 2.730% 2.751% 2.740% 2.913% 2.781% 2.822% 2.863% 2.446% 2.761% 2.751% 2.822% 2.792% 2.903% 3.340% 3.370% 3.788% 3.452% 3.838% 4.235% 4.296% 4.337% 4.550% MOS 4312b - Derivatives Excel Spreadsheet Assignment 1: Value at Risk (VaR) Analysis for Forward Contracts Due Date: Wednesday, November 7th, 2015 in class This assignment is designed to teach four things: (1) (2) (3) (4) how to generate forward prices and returns how to measure VaR using the historical and normal methods how to calculate Risk-Adjusted Return on Capital or RAROC Effects of diversification on VaR and RAROC Instructions: Please download the Excel file from OWL onto your hard drive. Create the spreadsheet in a way where all entries are linked through formulae. Prepare a written summary of your work that includes detailed answers to all questions. You are encouraged to bring research from outside while discussing the results. Please don't forget to properly mention and refer the sources of help. Submit a hard copy of your written answers and an e-copy of your answers and the spreadsheet. The assignment should be submitted on time to receive full credit. 1. Column A gives you the last market date of the month for 200 recent months. Columns B and C give you prices for two unknown financial assets, X and Y. Column D gives you the annualized 1-month continuously compounded risk-free rate for each date. (Hint: refer to chapter 4 in the text book on continuously compounded rates if you need help with them). Assume that the forward contracts expire on the last trading day of each month. You may also assume that neither asset pays dividends. Calculate the forward prices for both X and Y. 2. Consider yourself a financial institution that underwrites forward contracts on these assets for your customers. Calculate your $ profits if you had taken a $1 million short position in 1-month forward contracts on asset X on each date. In the next column, calculate your $ profits for taking a $1 million long position in 1-month forwards on asset Y for each date. (Hint: as a start, determine the number of contracts on each date that is equivalent to a $1 million position.) At the bottom of each column, use the Excel statistical functions to calculate the means and standard deviations of these $ profits (for the standard deviation function, consider the data in each column to represent a population rather than a sample). What is your formula in these columns? What are the means and standard deviations of your positions? 3. Consider a \"normal\" market to be what happens in the middle 95% of outcomes. For example, for someone taking a short position, this makes a \"bad\" market the best 2.5% of outcomes for the asset. Use the normal method to calculate the VaR of the $1m short position in asset X. What is the VaR for X? What is the VaR for the long position in asset Y using the normal method? Which position seems more risky? Why? 4. Use Excel's sort function to sort the data on profit/loss on the forward positions. Using the same 95% normal market definition, find the VaR for each of the two forward positions using the historical method. What are the two respective VaRs? Are your VaR estimates higher for the normal method or the historical method? If yes, what is a likely explanation for the discrepancy? 5. Suppose that your financial institution holds these two forward positions as a portfolio. Calculate portfolio dollar profits and compute the mean and standard deviation of the portfolio profits. What numbers do you get? Explain. 6. Using the same definition of a normal market as in previous exercises, compute the VaR of the portfolio using the normal method and the historical method. What are your results? 7. What is the expected return on your long forward position? Explain the reason for the expected return on your long forward position. 8. The term RAROC stands for risk adjusted return on capital. Suppose that as a financial institution you are required to set aside capital equal to twice the VaR of your forward position before you can undertake it. Use your normal method VaR estimate and your average $ profit to calculate the expected RAROC of the $1m short position in forwards on asset X. What return do you get? What is the RAROC of the position in Y using your normal method calculations? Is one position more attractive than the other? Does either position appear inadvisable based on its individual RAROC? Explain. 9. Use the expected $ profit of the portfolio of the two positions and the VAR that you calculated using the normal method to compute the RAROC of the portfolio. What is it? Suppose you were able to charge your clients a nonstandard fee for underwriting forward contracts. Which client (the one who asks you to take a short position in X or the one who asks you to take a long position in Y) would you want to charge a higher fee? Why? If you could accept some forward contracts and refuse others, would you ever want to accept a contract that has a negative expected RAROC