Reference no: EM132478592

Financial Engineering

Please build a spreadsheet, equipped with a VBA programme, that performs and illustrates the calculation of the CVA arising from a portfolio (a netting set) consisting of: (A) a forward contract that is long the underlying, and (B) a forward contract that is short the underlying. The two underlyings are different, but the two forwards have the same maturity. The notional of (A) should be greater than the notional of (B). As many of the calculations as possible should be placed in the VBA module(s). In addition to providing central estimates, your tool should facilitate a prudential-valuation analysis.

Please write a report (and please make it as concise and to-the-point as possible, and please make it use itemising/enumerating whenever possible) that:

Part One:
0) Starts by a brief introduction focusing in particular on any differences/divergences from the in- class approach; and then
i) Presents a decent illustration of the exposure profile(s) generated by the tool, and briefly comments on the intuition behind them.
ii) Lists, and briefly explains, the (top three) value-adding elements (eg in providing extra sophistication/accuracy) of your tool, compared to the in-class spreadsheet;
iii) Lists, and briefly explains, the main (say, three) approximations to (or simplifications of) reality, that you have resorted to;
iv) Lists, and briefly explains, the main (say, three) opportunities for future work that remain.

Part Two:
Then please answer the following questions. They touch on the broad area you have worked on above. Please give succinct (rather than lengthy) answers.

i) Assume that the discount rate is exactly 0 (and the following monetary values are in $million). Picking a time horizon, T, you are told that (as calculated at time zero), for a particular portfolio, the EPE is 3 and the ENE is 9 (both corresponding to T). Is this enough to deduce the expected exposure / valuation (again measured at time 0) of that portfolio, corresponding to time horizon T? If so, what is it?
ii) (Without loss of generality assume that the expected/maximal exposures mentioned here correspond to a single time point in future, T.) Bank A uses for economic capital (EC) a measure equal to: 1.4 * EPE. Bank B, instead, uses the weighted average: 80% * EPE + 20%
* ME. How small does the ME (maximal exposure) need to be, relative to the EPE, for Bank A
to end up putting aside (for this specific EC aspect) more EC than Bank B?
iii) Write a piece of VBA code, a macro called readAndPrint, that reads the contents of cells A1 to (horizontally) J1 of sheet "readFrom", and then prints to cell A1 of sheet "printTo" the individual cell contents (that have been read) separated by commas, with that whole result enclosed in brackets.

For example, if the cell content of A1 to J1 of sheet "readFrom" is:

the macro then prints into A1 of sheet "printTo" the following string: (a,b,50,6,t,5,6,8,8,0)
NB: Please make sure you use an iteration loop when iterating through the input cells (rather than doing the iterating manually).
For the remaining questions, please indicate whether True or False. (Importantly, please include a convincing and targeted explanation of why.)
iv) If the presumed recovery on a reference entity goes up (and assuming all other market variables of relevance are unchanged), then the implied default probability goes down.
v) Take two counterparties with identical exposure profiles, but they differ in their credit qualities: A has a low credit spread, and B has a high credit spread. It then follows that a stress test that bumps credit spreads up by 100bp will result in a higher CVA increase for A than for B.
vi) (Assume the discount rate to be zero in this question). For a stock, that is now worth $100, and that makes between now and 1 year time two (discrete) dividend payments: $5 (in 1 month time) and $10 (in 9 months time), it then follows that the (continuous-compounding quoted) dividend yield (up to 1 year) is exactly equal to 15%.

NB:

- Please submit both the spreadsheet and the report (the latter should preferably be in Word format). You should also include the code from your module(s) as an appendix (preserving the default VBA Editor colours) to your report.
- In your report's answers, please use the same numbering used in the questions above.
- Please make sure that any (academic or other) sources are properly referenced.
- [To be confirmed:] Please note that the Keats submission facility is likely to permit you to submit only a maximum of three files.
- And finally: Please submit well ahead of the deadline. (Problems that arose for late submitters in the past include, among many other incidents, discovering that their login/password had expired. It only took them a few minutes to revive them, but because they left it so close to the deadline, they ended up missing the cut-off time. There are plenty of other incidents reported by later submitters - an unexpected loss of internet connection, etc, etc - so please do not treat the deadline as a target. Rather, it is a cliff edge to stay well clear of.)

Attachment:- Financial Engineering.rar