Microsoft Word XXXXXXXXXXCFRM540 HW Project 1 Page 1 of 3 CFRM 540 HW Project 1 This project is to be worked on individually. You can discuss amongst yourselves the general approach, but code and...

This assignment needs to be coded in VBA


Microsoft Word - 2022 CFRM540 HW Project 1 Page 1 of 3 CFRM 540 HW Project 1 This project is to be worked on individually. You can discuss amongst yourselves the general approach, but code and spreadsheets should not be shared. Create an Excel tool in the form of a .xlsm file which can calculate VaR for a bond portfolio, by using the historical VaR approach. Since this is a bond portfolio which is driven by interest rates, a historical simulation approach must be used, using the historical interest rates, etc. (If it were an equity portfolio, the equity historical prices could simply be used. ) As a result, we must simulate the P&L for each day, based on the risk measures of the portfolio. We will use the simple day-over-day difference to measure the rate changes, rather than the percentage change each day. For the purposes of this project, we will assume the only components to the VaR are the KRDs (Key Rate Durations i.e Partial Durations) and Convexity. We will not consider other components such as volatility for simplicity. Data file: CFRM 540 HW Project 1_data.xlsx This file contains 1. Historical Rate data on the "Rate Data" tab 2. 4 bonds with position data and computed risk metrics on the "Position Data" tab Note: These data files contain more information than is necessary for this project. KRDs:  There are 10 KRD points computed for each bond, ranging from 3M - 30Years.  KRDs are given in units of KR DV01 at each tenor, which is the change in value for a 1bps DECREASE of that particular tenor point.  Since these are mortgage bonds, the relevant duration measure is the OA (option-adjusted) measures, which were computed using a monte carlo simulation. Satisfy for yourself that the sum of the KR DV01s is similar to the Total OA DV01.  Be careful on the units used - i.e. make sure to change the swap rates, which are in percent, appropriately. Convexity:  The relevant convexity measure is OA Cvx01. Note that this measure has not been scaled-up to be a "Total OA Cvx 01", so that will need to be done. It is also defined for a 1bps decrease in the particular tenor point. OA Cvx 01 is quoted per unit holding of the bond - sometimes for $1 or $100 holding amount. See what works here!  An additional complication is which swap tenor to shock for the convexity. We are choosing to determine the swap tenor by the Average Life of the bond, given in the AL field. Page 2 of 3 o The chosen swap tenor to be shocked, is the weighted average of the 2 swap points nearest the Average Life. o NOTE: The system should have all available swap rates to use that are in the "Rate Data" tab, not just the KRD points Inputs:  Your code should take as inputs on an Excel sheet: 1. Confidence-level in % - allow for computation of 4 different confidence-levels simultaneously 2. End Date: This is the most recent historical day used in the simulation 3. Interval: This is the number of days your VaR is. We will use actual days, instead of scaling using sqrt(time). Default and minimum value should be 1-day (i.e. 1-day VaR). For example, for 10-day VaR, the changes will be computed with 10-day overlapping changes 4. Number of Scenarios: This is the number of scenarios to use in the look-back period. It will not correspond to the number of days if the Interval is greater than 1.  For testing purposes, please show results for 2 sets of runs: 1. Run 1  Confidence levels: VaR0 = 95%, VaR1 = 99%, VaR2 = 99.5%, VaR3 = 99.9%  End Date: 6/28/2012  Interval: 1-day  # Scens: 252 (i.e. 1 year) 2. Run 2  Confidence levels: VaR0 = 95%, VaR1 = 99%, VaR2 = 99.5%, VaR3 = 99.9%  End Date: 6/28/2012  Interval: 5-day  # Scens: 300 Outputs: (create others if you find them useful)  An intermediate sheet called "Components" o This will show the P&L of each computed component for each Bond, end date, and variable. See the data file sheet "Components sample" for an example you can match to (I show only 1 bond and 1 day, but yours should show all bonds & days). The VaR field shows the total P&L for the day. Show each of the 4 bonds for a particular day, then move days. o Please report this using the same order and variable names I have used. o The components should be computed for each day, for every bond in the portfolio.  A sheet called "Totals" which rank orders each P&L from greatest to smallest, includes the end date corresponding to the P&L, and has a counter starting at 0 (largest P&L) running down to the smallest P&L result.  Final output is the 4 VaR values calculated for the 4 confidence levels specified in the Inputs Page 3 of 3 Grading:  60% Calculation accuracy o points will be deducted if your calculator fails for different intervals, end dates, # scens, etc  40% Documentation, Design & Robustness o How clear are the instructions on how to operate your spreadsheet o Is it designed logically, and easy to use? For example, it would be nice if all the inputs, controls, and final results were available on the same worksheet o How robust is the code or process, if data fields appeared in different columns? Is it easy to correct, or will the code do it automatically? o It is not necessary to do extensive error trapping, since we can assume a financial professional will be running your program. Rate Data DATEUST0.25YUST0.5YUST2YUST5YUST10YUST30YSWAP0.0833YSWAP0.25YSWAP0.5YSWAP1YSWAP2YSWAP3YSWAP4YSWAP5YSWAP6YSWAP7YSWAP10YSWAP15YSWAP20YSWAP30Y 201208010.09634260.1420450.2389280.6448751.53592.609590.2446660.44160.72490.4010.4310.5070.6530.8461.0471.2341.6652.0852.262.403 201207310.1064870.1420450.2191340.5953421.484872.570160.2456660.44260.72590.3970.420.4810.61050.7950.9891.1761.6052.0112.1862.349 201207300.1014130.1420420.2268510.6128161.500232.57590.2456670.44460.72640.390.4170.48050.6220.8040.9991.18351.6152.02382.2032.3435 201207270.1064850.1471190.24630.6590091.551432.637150.2456670.44660.72440.4130.4420.51620.6660.8551.0531.24051.67252.092.2592.4 201207260.1064860.1420440.2262940.5823771.42772.489090.2451670.44710.72440.4120.4360.4930.6180.7890.97251.1461.5611.9682.1362.274 201207250.1014150.1420450.2183660.5569121.405882.469610.2441670.44810.72640.4180.4380.48740.6140.7650.95881.1351.5451.9532.122.257 201207240.1014150.1420450.2175610.5522191.405972.470340.2441670.44810.72640.420.4390.4980.6180.7820.9631.1361.5461.9512.1222.258 201207230.09126960.1420420.213560.560321.434692.51160.2461680.45110.72740.4260.4460.5040.62130.79050.9761.1521.5681.982.1492.269 201207200.09126990.1369660.2095710.5796121.461782.551170.2467180.45210.72740.4230.4470.51050.640.81211.17851.5982.0132.1822.321 201207190.08619890.1318920.2137640.6118631.512812.614210.2467190.45310.72790.4180.4470.5180.6540.8331.0261.2111.6422.04832.2292.366 201207180.09127080.1369680.2218520.5975511.480672.583340.2467190.45510.72840.4320.4610.5270.6560.8271.0151.1961.6182.0362.1962.351 201207170.09634280.1420450.2419640.6184081.501062.59340.2467190.45510.72840.4380.4730.5450.6790.8561.0461.2241.6462.0582.22222.368 201207160.09126960.1369660.2259390.596121.467292.554880.2477190.45510.72840.4220.4440.5320.6620.8291.021.1961.60882.0212.1892.325 201207130.09634160.1471190.2540020.6313331.501192.586280.2477190.45510.72840.4450.4820.5570.6880.8591.0441.2191.6272.0362.19482.339 201207120.1014140.1471210.2619650.6251441.477712.560670.2487180.45510.72940.4410.4750.5420.6730.841.0191.1951.59751.99432.1712.3045 201207110.09634260.1471220.2659340.631591.504832.595690.2487180.45610.73440.4560.4970.5620.6870.8541.041.21751.632.0372.2122.349 201207100.09634280.1521990.2698910.6220921.504892.604340.2487190.45760.73640.4860.5230.57360.7020.8651.051.22651.6392.0442.2182.346 201207090.07605530.1420420.265890.6253461.515112.62310.2487190.45760.73640.4820.5030.5810.7060.87851.0651.24051.6552.0662.23112.377 201207060.07605550.1420430.2738060.6397451.542312.660870.245720.45760.73640.470.5180.5840.720.8991.0911.26511.69252.10482.2882.429 201207050.07605590.1471210.2895240.6733541.596912.716030.245720.45960.73640.4890.5330.59610.740.9241.1251.31251.74552.1672.3422.482 201207030.08619910.1471220.3052690.6972991.631122.744260.245720.46060.73440.4860.5390.60760.7520.9471.13791.32461.7662.18512.35852.4987 201207020.07605530.1521960.2972410.6687071.582.688810.2447190.46060.73440.5030.5450.6130.74950.9271.1191.30251.7272.1442.3162.451 201206290.08619830.1572730.3089790.7260651.65692.768220.2457190.46060.73440.4970.5450.62650.7780.9671.1671.3551.7822.2052.3762.515 201206280.08112730.1572750.3048210.6942921.581872.676420.2452190.46060.73440.5060.5550.6290.7690.9471.1351.31151.72052.132.2962.427 201206270.08619910.1623540.3125720.71721.619392.691840.2452190.46060.73440.4890.5450.630.7830.9681.161.33951.7492.1472.312.434 201206260.0912710.1572770.3110610.72851.636492.709540.2452190.46060.73440.4790.5410.6310.7870.9781.1741.3561.772.1712.3282.4515 201206250.08112660.1471190.2987720.7090021.609232.686010.2452180.46060.73390.4720.5250.6230.7780.96551.161.341.7492.1472.3052.427 201206220.08112680.1521960.3068270.7510831.670772.754860.2452180.46160.73440.4810.5380.640.8051.0041.2051.3881.8022.2042.3642.487 201206210.08112730.1471210.3025430.7233681.616182.685350.2452180.46760.73740.5070.55950.64850.80450.9951.1891.3661.7722.1672.3232.443 201206200.08619910.1521990.3024690.7394841.638382.719990.2452180.46760.73740.4910.5350.6270.7920.9941.1931.3731.7862.1862.3462.468 201206190.0912710.1471220.2902980.7038891.619662.729640.2437180.467850.73740.4920.5350.6160.76810.9711.171.35091.7752.1822.3462.47 201206180.08112660.1471190.2862150.6812821.573832.662020.2437180.467850.73690.5020.5360.6110.7610.9511.141.3191.7282.1212.2762.393 201206150.09634160.1471190.2781240.6780321.590832.699380.2427180.467850.73740.5080.5460.61330.76650.9531.1431.3271.7412.1372.3052.427 201206140.1014140.1521980.2980280.735891.636852.734170.2427170.467850.73790.5430.5930.6730.8291.0221.2121.3851.7952.1922.352.47 201206130.09127080.1471220.2939620.710071.601182.711990.2417180.467850.73790.5410.590.6670.8161.0011.1851.36051.7622.1642.3272.452 201206120.0912710.1471220.2939010.7470461.662492.7730.2407180.467850.73690.5380.5940.6780.8411.0381.23451.4171.83052.22652.3992.526 201206110.08619810.1420420.2739060.6890971.596172.719410.2407180.467850.73690.530.570.64190.8030.9931.1881.3681.7842.1962.3642.492 201206080.08112680.1369660.2698930.7115391.640382.766290.2407180.467850.73690.5410.5710.6520.8150.99651.2181.40351.832.2472.40362.541 201206070.08112730.1369680.2698110.7130051.654082.761850.2407170.467850.73690.5330.5720.6560.82151.0181.2221.4091.83452.2372.3952.517 201206060.08619910.1369680.2618690.7257871.654112.723940.2407170
Feb 05, 2022
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here