CM2B April 2019 Q3 (vi)

I can't see how the examiner has worked out that holding -1000 Call Options and 2296 Put Options is the portfolio that maximises the probability of repaying the loan. I can see from the spreadsheet that -1000 and 2296 give a bigger probability of repayment than in Part (v), but I'm not sure how we know this is the maximum, or how we are meant to arrive at -1000 and 2296.

Is there a Goal-seek type function that can be used to maximise Cell I6 (the probability of repayment) by changing cells D3 and E3 (subject to them summing to 10,076) ? Or are we meant to use a Lagrangian technique to maximise the probability?

At the minute, the only way I can see to get to the answer is some elaborate Trial and Error. Any help would be really appreciated!

I've attached the question paper and Q3 solution spreadsheet

Thanks

 

Yes we have got a function that minimise/maximise a certain cell in excel.
That function is known as (solver). But remember that in order to solve any optimisation problem you need constrains that are subject to the problem you are trying to minimise or maximise. This is usually found in the question. Please note that you have to see this for your self the examiners won't tell you. In the case of the April 2019 examination we want to maximise the probability cell such that
(1000*So)+(a*Co)+(b*Po)=10076(constrain which you will need when you use solver in excel).
So- initial share price
Co-value of the call option at time 0
Po-value of the put option at time zero.
Please use solver in excel and let me know if you are happy