Question

How do I compute call and put deltas (Δcall and Δput) for a range of stock prices in excel?

is there a formula for computing call and put delta for multiple stock prices instead of just one stock price?

Answer 1

The delta of a call option is given by N(d1) and the delta of a put option is given by N(-d1) = N(d1)-1

We enter the formula in the cells to get the call and put deltas for a range of stock prices.

We fix all the variables except the stock price, so that when we drag down, we get the delta for all the stock prices

You can directly copy and paste these formulas in excel.

Strike price	100
Risk-free rate	0.05
Volatility	0.3
Time to maturity	1
Stock price	Call delta	Put delta
80	=(LN(A7/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B7-1
81	=(LN(A8/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B8-1
82	=(LN(A9/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B9-1
83	=(LN(A10/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B10-1
84	=(LN(A11/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B11-1
85	=(LN(A12/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B12-1
86	=(LN(A13/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B13-1
87	=(LN(A14/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B14-1
88	=(LN(A15/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B15-1
89	=(LN(A16/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B16-1
90	=(LN(A17/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B17-1
91	=(LN(A18/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B18-1
92	=(LN(A19/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B19-1
93	=(LN(A20/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B20-1
94	=(LN(A21/$B$1) + ($B$2+$B$3*$B$3/2)*$B$4)/($B$3*($B$4)^0.5)	=B21-1