Excel Solver on multiple cells (complete row) // Option payoff profile

Good morning y'all, i hope you are doing good today,

I've uploaded the attached spreadsheet. I've got the following case:

The first column shows the stock price at maturity S(T). For each of these stock prices we compute the payoffss for all the options we hold. Columns B-J contain the payoffs for each option at increasing strikes (Row 1) from 10 to 90. The cell formulas (Array B6:J14) multiple the payoffs per single option by the number of options held for each strike. The number of options held is stored in the row titled “Number” (Row 4). This row is where the Solver should choose the number of options of each strike to hold so as to make the payoff profile in the second last column (K) match the target profile (L) in the last column (therefor, the constraint should that column K=L). Row 2 (respectively Row 2) represents if the number of options in row for corresponds to a call or a put (call = 1 means, that its a call and vice versa). This should be picked up as an indicator variable by the solver.
My problem now is that I can't get the solver to solve a complete row at once.
This is where I'd love to get your assistance on, thanks in advance!

Cheers
Jules

Looking at you Solver model I'm puzzled. The "Target" cell i.e. J4 is set to a value of 0. This should be archived by Solver changing cells in range "B2:J2" but there is no formula in J4 that will change J4's value by changing cell value in "B2:J2".

To run Solver the "Target" cell value must change with a change in range "Variable" cells.

Alf

Hi Alf,

thanks for your swift reply.

Actually I don't know how to cure this issue. Do you have an idea how to model (using solver) my wanted numbers of options per strike and stock price?

Thanks in advance!

Cheers
Jules

Do you have an idea how to model (using solver) my wanted numbers of options per strike and stock price?

Well perhaps. I played a bit with different values in row 4 and found a combination that made it possible for solver to find a solution. If this solution
make sense to you I really don't know.

Alf

Thanks Alf! It was basically only the target cell, which I was to dump to come up with by myself.

Thanks a lot!

You are welcome and thanks for feedback.

which I was to dump to come up with by myself

Welcome to the club. There are times where I'm really surprised by my own stupidity!

Alf

Ps If this solves your problem don't forget to mark thread "Solved"

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Alf,

I was actually checking my spreadsheet again, and I encountered the following issue:
My goal was to set the "number of options" row 4 as for the solver to be solved. I think your mod spreadsheet set the stock prices to be variable, which is not what I wanted.

Now again, I can't make the solver solution to be feasible using the row 4 as variable, it'll bring me decimal numbers plus on top the set objective is not 0.

Sorry to come up with this again do you have any clue how to solve this one?

Thanks -

Cheers
Jules

it'll bring me decimal numbers plus on top the set objective is not 0

When solver can't find a solution to a problem it will quite often ignore integer constraints and show decimal values in a last attempt to try and gind
a solution for the object function.

I've tested a bit but as far as I can see your problem is not solvable i.e. by changing row 4 you can't meet the present constraints.

I've set up two solver models "Evolutionary" and "Simplex LP" both find a solution but only because I relax the original constraints.

So unless you have any new information I don't think I can help you any further as this problem as far as I see has no solution with the present constraints.

Alf

Thank you so much for your effort.

I see, this is probably a hard one. Anyone else, who might have a solution for my problem?

For the ease of reference, I attach the original exercise of the book INCLUDING the solution (which is - to be honest - not very satisfactory). But maybe it'll help you guys, to help me find out how to handle this?

Thanks in advance!

Cheers
Jules

attached - please see exercise 3)

Your formulas in range B6:J14 are wrong according to the pdf file. Exstending your matrix and adding the pdf values to row 4 the result do not correspond with the values shovn in the pdf file. See sheet "Your_formulas".

I have set up a model using the layout from the pdf fil and I get the same result as the shown.

Just clear the range B4:L4 on sheet "Simplex LP" and do a solver rerun with my setup.

Check the formulas on sheet "Simplex LP" range B6:L16 as well.

Alf

Hi Alf,

Thank you.

Unfortunately, I can't get your way of deriving the formulas in the payoff array:
Screen Shot 2016-11-09 at 09.38.50.png

Why have you set the second value in the substraction to 80, i.e. A9-E1. In my opinion the payoff should be Stock Price (i.e. A9=$80) - Strike Price (i.e. I1 = $120) * Number of options held (i.e. I1 = -4) = +$160.
What this means for me is, if the residual of Stock Price and the Strike is negative (which is the case in this specific example) and I short this option (i.e. have a negative number in row 4 "Number") I should end up with a positive payoff of $160.

So, why have you locked the formula cell of the Strike price to E1 = $80, so that the payoff is negative? This doesn't make sense to me.

I'd love to hear your feedback on this.

Thanks in advance!

Cheers
Jules

If you compare my setup to the solution given in the pdf file they are identical for every row and every column so this tells me that the formula I used is the same as the authors Sundarame & Das used in their example of "Derivates - Problems and solutions".

As to the why the formulas in the range B6:L16 are the way they are you better take that up with the authors or reread the book as economy is not my cup of tea.

Alf

de Quincey: "Confession of an English Opium-Eater"

In this state of imbecility, I had, for amusement, turned my attention to political economy