Black Scholes Model

[DaveJ]

I put this together to help me with deriving the option pricing and greeks for another spreadsheet I'm working on. I've built out formulas as well as created UDF's (User Defined Functions) for the option greeks. You can quickly see why the vba coding is more efficient than building lengthy equations.

Sharing with the community.

Dave

 

[SergB]

@DaveJ Thank you for sharing!
The biggest question for me is where to take volatility number, especially if I am to try to calculate options prices for the past date.
Do you know how to deal with it?

 

[status1]

I use the IV for the strike I am looking for that is on the trade tab

 

[status1]

Any reason why a cell reference does not work with UDF ?
I was trying to use IV using RTD but I am getting #VALUE! for the VBA UDF
It works with the excel formula though

 

[DaveJ]

I do same as status1, I take the IV from the strike as the volatility input.

 

[DaveJ]

Any reason why a cell reference does not work with UDF ?
I was trying to use IV using RTD but I am getting #VALUE! for the VBA UDF
It works with the excel formula though

In the spreadsheet I shared, cell references is exactly what I'm using for the UDF inputs. I do know the rtd feed for IV comes in not useable as a number. I use the VALUE formula around the RTD for IV to fix that.

ie.
=VALUE(RTD("tos.rtd",,"Impl_Vol","."&$K11))

No idea why IV has this oddity to it. The other rtd functions work fine without the additional value step.

 

[status1]

Thanks for the tip That did the trick
I looks like VBA needs a numerical value instead of text or something else just for this IMPL_VOL as you said the other inputs are working without it