In this post we are considering trading option position on USD/INR currency pair. The strike price here is in terms of rupee against the dollar. We are holding different option positions (call and put options) on this underlying (USD/INR pair). The excel workbook shows the payoff of holding different positions simultaneously on the same underlying. It also provides with single option position for a given combination across the spot price range.
It is highly useful for people involved in the option products structuring in bank/corporate treasuries as well as in fund houses for their clients. This workbook can also be used for other currency pairs (like EUR/USD) as well as other asset class options (like Nifty or SPY options) by changing the initial and the incremental value on the Sheet1 below the final spot (cell A9 and A10 onwards).
The excel sheet consists of 4 sheets. We shall explore all the sheets one at a time.
The snapshot of the table data at the beginning of the sheet is provided here.
Row 2: type of position i.e. call or put denoted by C or P respectively,
Row 3: Whether the respective option is being bought or sold denoted by B or S respectively,
Row 4: strike price of the respective option,
Row 5: notional amount in USD (millions), and
Row 6: the net position. Net position calculation is explained below:
Net position is the effective notional amount for the respective option if we were to replicate it with a future contract. So if we are buying a call option or selling a put option, net notional for that option would be considered positive. Similarly if we are buying a put option or selling a call option, net notional for that option would be considered negative.
Column A from row 8 onwards represents increasing “Final Spot Price” i.e. the exchange rate of dollar against rupee on expiry. The model considers the increment in price at the price interval of 1 paisa (i.e. 0.01 Rupee) with thousand such increments resulting in a range of Rs 10 in total. If you want to cover a different range, you can put the starting price of the range that you want to consider in cell A9.
Column B (similarly Column C, D, etc) from row 8 onwards represent the profit/loss for the respective option in that column at the final spot price as mentioned in the Column A from row 8 onwards. Let us explore how the Pay-off is calculated for each option position.
Column B formula
The profit/loss for cell B9 (and hence for the entire column B thereafter) is calculated in the following manner:
Cell number B2 (where we have ‘P’ for put as the value) and B3 (where we have ‘B’ for buy as the value) suggest that we have bought a Put option. In a long Put, lower is the final spot price from the strike price, higher is the profit. So, in cell B9 we have the highest Pay-off value as it has the lowest value of the final spot price. Please notice that the final spot price is increasing from B9 on wards hence the payoff is decreasing. When the final spot price is higher than the strike price the long Put expires worthless. Hence the profit is zero (as we’ve bought the put option and we have the right to sell but no obligation to sell). This is evident from the cell B335 onwards.
The same formula is extended for other options mentioned in Column C, D, etc. For example in Column C9, we’ve the following formula:
The pay-off for cell C9 (and hence for the entire column C thereafter) is calculated in the following manner:
C2 and C3 suggest that we have sold a Call option. In a short Call, we do not have any positive pay-off (as we only receive premium which we are not considering here). Since we have ignored any premium payment or transaction costs, the maximum pay-off is zero. When the final spot price starts increasing above the strike price, the short call incurs loss. This is evident from the cell C335 on wards.
Column AG: The Total Payoff
Column AG denotes the total profit/loss from the three option positions combined at that final spot price.
The total payoff sheet displays the net payoff from all the option positions on the left.
Column AI: The status
Column AI denotes the status of change in the total payoff difference. The formula is as follows:-
If the difference between two payoffs is zero then the entry in column AI is null.
The next part can be broken into two steps.
- The ratio of difference between total payoff to the difference of final spot is calculated.
- The absolute value of the differences of the ratio calculated above is computed.
If the absolute value is greater than 0.000001 then the entry is “Change” else if the absolute value is less than 0.000001 then the entry is “Ok”.
Column AJ: The signal
Column AI denotes the status of buy or sell signal.
If the absolute value of the difference between successive total payoffs is less than 0.000001 then the entry is “Fixed”. If the total payoff is greater than 0.000001 then the signal is “Buy” else if the total payoff is less than 0.000001 then the signal is “Sell”.
Column AK: The option type
Column AK denotes the option position that is held.
If the corresponding entry in column AJ is “Fixed”, then the entry in column AK is “Fixed Payout”. Else the following conditions are tested:-
- If the ratio of difference of the payoffs and the difference of final spot is greater than 0.000001 and if the corresponding entry in AJ is “Sell” then it is “Put” else it is “Call”.
- If the ratio of difference of the payoffs and the difference of final spot is less than 0.000001 and if the corresponding entry in AJ is “Sell” then it is “Call” else it is “Put”.
Column AL: Strike price
Column AL calculates the strike price as follows:-
If the corresponding entry in column AK is “Fixed Payout”, then the entry in the column AL is “Fixed”.
Else the strike price is calculated as follows:-
The ratio of difference in the total payoffs to the difference in final spot is calculated. This entity is used to divide the total final payoff from the AG column. The resulting value is subtracted from the final spot price to obtain the strike price.
Column AM: Net position
Column AM denotes the net position and is calculated as follows:-
We will see in a moment what the contents of the sheet3 are, as of now the notional amount is the sum of the product of corresponding cell values from sheet1 and sheet3 for the ranges $B$6:$AE$6 and B9:AE9 respectively.
The values from the sheet1 for the range $B$6:$AE$6 is shown in the screen shot below
Only the first three values will be considered since the rest of the values are zeros.
The values from the sheet3 for the range B9:AE9 is shown in the screen shot below.
Now the sum of products is calculated as follows:-
(-3*1) +(-6*0) + (3*0)=-3
Now we will turn our attention to sheet3.
Sheet 3 is essentially the binary representation of sheet 1 for the payoff columns. For example column B from B8 onwards shows the payoff for the “Put” and “Buy” combination. The payoff is positive from the cell B9 to B334. So the entry in the column B of sheet 3 from B9 onwards is 1 if the corresponding absolute value in sheet 1 is at least 0.000001. The screen shot below shows the formula.
Similar logic is applied for all other columns of sheet 3.
Let us explore sheet 2.
The macro written to fill the cells of sheet 2 is shown below.
With the initial parameters of i=11 and j=11, the Do While loop fills the cells of sheet 2 with the contents of sheet 1 as follows:-
All this means is the value of cell defined by row 13 and column 36 (Sheet1!AJ13) of sheet 1 is placed in the cell defined by row 3 and column 1(Sheet2!A2) of sheet 2.
Similarly for the cells B2, C2 and D2.
Cell E5 of sheet 2 is filled on the following logic:-
If the cell AJ13 of sheet 1 has the entry as “Fixed”, then the entry in E5 of sheet 2 should be of AG13 of sheet 1. Otherwise it should be “-“.
Cell F5 of the sheet 2 is the sum of products of range of cell values from sheet 1 and sheet 3 described in the formula below.
Sheet 4 (named Total Payoff) shows the plot for the payoff. The X axis shows the rupee exchange against dollar (the final spot). Y axis shows the final payoff (column AG of sheet 10). As you can see, for this combination of options, payoff increases as the final spot decreases and payoff decreases as the final spot increases. In other words, payoff increases as the rupee strengthens and decreases as rupee weakens against the greenback.
Download Working Model