Calculate Black-Scholes Options Fair Value in Excel

CallPremium Function: Returns the Fair Value of a European Call option according to the Black-Scholes model.

=CallPremium(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

PutPremium Function: Returns the Fair Value of a European Put option according to the Black-Scholes model.

=PutPremium(UnadjustedPrice, StrikePrice, Years, Volatility, RiskfreeRate, DividendYield)

Premium Function: Returns the Fair Value of a European Call or Put option according to the Black-Scholes model. This is the same function as above except it works for a Call or a Put. This is sometimes easier to work with in Excel.

=Premium(OptionType, UnadjustedPrice, StrikePrice, StartDate, EndDate, Volatility, RiskfreeRate, DividendYield, WeekdaysOnlyMode)

Description of parameters

For each Excel Function that calculates an Option Greek or other Options statistic, there are certain parameters required as shown in the formula(s) above. Not all functions use all parameters. Here is a description of each parameter:

Notes:

WeekdaysOnlyMode: Whethere to include or not include weekends and holidays in your Implied Volatility calculation is a personal preference. Generally, markets are closed on weekends and holidays and some say for this reason, WeekdaysOnlyMode should be set to "ON" thereby calculating an Implied Volatility based on roughly a 252 Day Trading year. Personally, I prefer if off because it's a more accurate representation of a true year and because those that are short options over a weekend still get paid which means the market is really never closed!

StartDate and EndDate: These are not affected based on how WeekDaysOnly Mode is set. Using actual Calendar dates and times when entering the StartDate and EndDate values is the most accurate way to go.

European vs American call options have the same theoretical value. However American Puts are more difficult to value than European Puts. Most professionals agree that the Black-Scholes model provides a reasonably accurate estimation of value.