Financial Engineering VBA Programming Help

Financial Engineering 

[35 marks:] 

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: 

[35 marks:] 

Part One: 

0) Starts by a brief introduction focusing in particular on any differences/divergences from the in- 

class approach; and then 1) Presents a decent illustration of the exposure profile(s) generated by the tool, and briefly 

comments on the intuition behind them. 2) 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; 3) Lists, and briefly explains, the main (say, three) approximations to (or simplifications of) 

reality, that you have resorted to; 4) Lists, and briefly explains, the main (say, three) opportunities for future work that remain. 

[30 marks:] 

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. 

a) 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? b) (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? c) 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: 

a B 50 6 t 5 6 8 8 0 

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.) 

d) 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. e) 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. f) (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 

Tired of the boring assignments to be submitted to colleges and universities? MyAssignmentMart.com gives you multiple reasons to trust it with your orders. We employ PhD experts handpicked from prestigious universities over the globe. We cater to quality assignment help, dissertation writing services and online essay help, covering more than 100 academic subjects. We pledge to complete your assignments on time and never compromise when it comes to deadline. Our prices for assignments are the most competitive in the market and make sure the cost suits your budget.