Hedging Mark to Market Calculation Help

I’m trying to put together a quick mark to market summary for some natural gas and crude oil hedges. To make it easier I’m using a fixed strike price and will not be including optionality. Basically I just want to see what the mark to market gain/loss would look like if I used the August 1 closing prices. I’m stuck with how to create a giant formula that will capture all of my potential calculations. For example I could have multiple combinations of: Commodity = Nat Gas or Crude Oil Transaction Type = FFSWAP or Option Buy or Sell Call or Put (If Transaction Type = Option) Exercise Price Strike Price Quantity Start date and End Date Should I just attempt to put together one huge IF(AND(OR type nested excel formula or is there an easier/quicker way? Any help would be appreciated, thanks.

uh, you could write a program…

Way too easy. Calling all Excel guru’s here!