Collar and butterfly trading stragey

[Padraic]

Hi all,

I have been given the following assignment in work placement and with only basic prior excel experience I am not sure how to go about setting this following question up.

"Using excel, map out the pay-offs on a "collar" and "butterfly" trading strategies. Use VBA to calculate premia."

I would really appreciate any help as the deadline is Wednesday

kind regards
P

 

[Shakone]

Hi all,

I have been given the following assignment in work placement and with only basic prior excel experience I am not sure how to go about setting this following question up.

"Using excel, map out the pay-offs on a "collar" and "butterfly" trading strategies. Use VBA to calculate premia."

I would really appreciate any help as the deadline is Wednesday

kind regards
P

Not a lot to go on, and without knowing any values or the expected background, below is what I would do:

For the butterfly, I would start with a column labelled Asset_Price_at_Maturity (or Spot if you're lazy like me ), next to a column labelled Payoff. A few columns to the right of these, inputs of 'Spot_increment', 'Mid_Strike', 'Strike_diff' or some better names that you prefer. Just single cells for these. So for a butterfly with strikes 80,100,120, you would have 100 in the cell to the right of the words Mid_Strike, and 20 in the cell to the right of Strike_diff.

Then put a 0 in the first cell in the spot column, perhaps an increment of 5 in the increment cell (or 1 if you like), and then drag down the spot column, so that it goes 0,5,10,15,...

Then you can calculate the payoff for each spot, referring to the strike cells. Drag this payoff column down, and plot it with Spot along the x-axis, payoff along the y-axis. You will know you have done it correctly (or incorrectly) because you can search online for the payoff diagram of a butterfly and compare to what it should look like. This chart is lacking the initial premium paid, so it is all above 0.

Let me know if any of the above isn't straightforward. It might look something like the pic attached, but I'm confident you can make it look much better and name things in a better way. My Excel version is ancient.

Next you need to do the premia in VBA. Since a butterfly is just a combination of long and short of call options, you will need extra cells with inputs for the call option (or a button - see below), like 'interest', 'volatility', 'expiry'. I don't know what your experience with VBA is like, so you'll have to give feedback before I say any more on that, i.e. "I've never used it before", "I've programmed some simple stuff in it". If you're an expert in VBA you already have enough info to complete the task I'd guess.

Once you can get the premium for one individual choice of parameters, spot, interest, vol, maturity and strike, then you can create another column and plot the premia for all the spots in column A and maybe plot that. Again, I don't really know what is expected from that one line.

Since we're using VBA, you can put a button which when clicked gives a pop-up box that asks for the various parameters required to price the option. They may want this.

 

[Padraic]

Not a lot to go on, and without knowing any values or the expected background, below is what I would do:

For the butterfly, I would start with a column labelled Asset_Price_at_Maturity (or Spot if you're lazy like me ), next to a column labelled Payoff. A few columns to the right of these, inputs of 'Spot_increment', 'Mid_Strike', 'Strike_diff' or some better names that you prefer. Just single cells for these. So for a butterfly with strikes 80,100,120, you would have 100 in the cell to the right of the words Mid_Strike, and 20 in the cell to the right of Strike_diff.

Then put a 0 in the first cell in the spot column, perhaps an increment of 5 in the increment cell (or 1 if you like), and then drag down the spot column, so that it goes 0,5,10,15,...

Then you can calculate the payoff for each spot, referring to the strike cells. Drag this payoff column down, and plot it with Spot along the x-axis, payoff along the y-axis. You will know you have done it correctly (or incorrectly) because you can search online for the payoff diagram of a butterfly and compare to what it should look like. This chart is lacking the initial premium paid, so it is all above 0.

Let me know if any of the above isn't straightforward. It might look something like the pic attached, but I'm confident you can make it look much better and name things in a better way. My Excel version is ancient.

Next you need to do the premia in VBA. Since a butterfly is just a combination of long and short of call options, you will need extra cells with inputs for the call option (or a button - see below), like 'interest', 'volatility', 'expiry'. I don't know what your experience with VBA is like, so you'll have to give feedback before I say any more on that, i.e. "I've never used it before", "I've programmed some simple stuff in it". If you're an expert in VBA you already have enough info to complete the task I'd guess.

Once you can get the premium for one individual choice of parameters, spot, interest, vol, maturity and strike, then you can create another column and plot the premia for all the spots in column A and maybe plot that. Again, I don't really know what is expected from that one line.

Since we're using VBA, you can put a button which when clicked gives a pop-up box that asks for the various parameters required to price the option. They may want this.

Great thanks for your help. Really appreciate it. I have just been forwarded on more requirements from my manager with regard to this assignment. He now also wants me to do the following:

Describe and explain each trading strategy in a word doc. How might these types of strategies be relevant to traders? and also For the butterfly investigate the effect of increasing volatility.

Is there any books or anything on the web that may help?

Thanks again. Really appreciate your help.

p

 

[random12345]

Great thanks for your help. Really appreciate it. I have just been forwarded on more requirements from my manager with regard to this assignment. He now also wants me to do the following:

Describe and explain each trading strategy in a word doc. How might these types of strategies be relevant to traders? and also For the butterfly investigate the effect of increasing volatility.

Is there any books or anything on the web that may help?

Thanks again. Really appreciate your help.

p

What kind of work placement is this?! Surely it's an internship to be doing all this stuff rather than a placement...?

I had a work placement at Reuters many years ago and I just copied out the AP newsfeed onto their intranet website before leaving at 2pm. Sounds like you've got a pretty rough deal to me... good luck.

http://www.investopedia.com/slide-show/options-strategies/ - this gives an intro to each common strat and points out its basic pros & cons. If he wants more than that, tell him to go jump and learn to be a brickie instead.

 

[Shakone]

Great thanks for your help. Really appreciate it. I have just been forwarded on more requirements from my manager with regard to this assignment. He now also wants me to do the following:

Describe and explain each trading strategy in a word doc. How might these types of strategies be relevant to traders? and also For the butterfly investigate the effect of increasing volatility.

Is there any books or anything on the web that may help?

Thanks again. Really appreciate your help.

p

The Bible for options related question is Hull - Options, Futures & Other Derivatives. For less technical discussion you can look at Natenberg's book. For this assignment you probably don't need any of that though. Wikipedia will probably do.

Wikipedia is always worth checking out as it gives you a place to start, and can definitely descibe things like the butterfly which will help your word doc, and Random's link above is helpful too. More generally though, use some common sense. Once you know the payoff diagram for any option strategy, you know when it profits and when it doesn't, in other words you know what that strategy is trying to capitalise on. A butterfly profits when the asset price at maturity is within the range of the two outer strikes (or within some distance from the middle strike). If I strongly believed an asset wasn't going to move far from some area, but didn't know which direction it was going to go, I might be interested in buying that option. Make sense? Now as volatility increases, what does your instinct tell you will be the effect?

If you don't know, doesn't matter. Investigating the effect of volatility on the butterfly will come about when you've finished the VBA, as you'll be able to plot the premia for different spot prices, but also for different volatilities.

You didn't say how you got on with the excel sheet and the payoff chart, or whether you can do VBA, but the word doc is probably the easier part.