Create a simplified CSP and CC scanner in excel using Black-Scholz Model

A simpler version of a CSP and CC scanner can be created using Black-Scholz model in Excel and the formula is as follows with certain limitations:

Known limitations:

• Assumes constant volatility (doesn't model the IV skew)

• Assumes continuous trading (no gaps)

• Assumes log-normal returns (real distributions have fatter tails)

• Doesn't handle dividends in the basic form (modify by subtracting dividend yield from r)

• Assumes constant interest rates

**The Black-Scholes PDE**

∂V/∂t + ½σ²S²(∂²V/∂S²) + rS(∂V/∂S) − rV = 0

V = option value, S = stock price, t = time, σ = volatility, r = risk-free rate, K = Strike Price

**Pricing formulas**

**Step 1** ; calculate d1 and d2 .

d1 = \[ln(S/K) + (r + σ²/2)·T\] / (σ·√T)

d2 = d1 − σ·√T

**Step 2:** Calculate Premiums

Est. Call Premium =S·N(d1) − K·e\^(−rT)·N(d2)

Est, Put Premium = K·e\^(−rT)·N(−d2) − S·N(−d1)

where N() is the cumulative standard normal distribution.

**Step 3:** Greeks (computed from the same inputs)

Call Delta = N(d1)

Put Delta = N(d1) − 1

Gamma = N'(d1) / (S·σ·√T)

Vega = S·N'(d1)·√T / 100

**Excel implementation:**

In Excel, N(x) is implemented as NORM.S.DIST(x, TRUE).

You can recreate your entire screener with these formulas if you want to.

This is an estimated premium and you will have to check the Bid-Ask Spread, Earnings date, and OI to see which ones are advisable.

Once you have placed the trades, you need to know how to manage these , traps to avoid and how Greeks interact with each other near expiry. Here are my cheat-sheets, posted to my wall.

[https://imgur.com/a/cash-secured-put-wSbTsX9](https://imgur.com/a/cash-secured-put-wSbTsX9)

[https://imgur.com/a/covered-call-ksnN117](https://imgur.com/a/covered-call-ksnN117)

For my personal use, I have a more an strike-IV ans Open Interest and Bid-Ask Spread Aware filter .

**EDUCATIONAL PURPOSES ONLY. NO FINANCE OR TRADE ADVICE.**