Stock Options calculations - How to calculate Delta after a

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
I'm trying to calculate some things by hand (well, by computer) just so I have a thorough understanding of how they interact. I'd like to have a spreadsheet that tells me what an option price WOULD BE (not taking volatility into account which is a different can 'o worms) AFTER an expected move was made in the underlying stock. Here's where I'm at right now...

Current Delta is .5
Current Gamma is .02
Current Option price is 1.00
Expected Underlying Move is 2.5 over, say 15 days (# of days is irrelevant in this calculation though)
Underlying stock price is 30
Future Delta after a $1 underlying move would be .52 (current delta + gamma)
Future Option Price after a $1 underlying move would be 1.50 (current option price + current delta)
Future Delta after a $2 underlying move would be .54 (first future delta of .52 + gamma of .02)
Future Option Price after a $2 underlying move would be 2.02 (first future Option Price of 1.50 + first future delta of .52)
Current Delta after a $2.50 underlying move would be .55 (2nd future delta + (gamma/2) but the move is done anyway so I don't care)
Current Option Price after a $2.50 underlying move would be 2.56 (2nd future option price of $2.02 + 2nd future delta of .54)

So, fairly easy to calculate... what I can't figure out how to do is to get Excel to understand that the final option delta after the move is not in fact just (option delta + (gamma*EM)). I hope that makes sense. I need to calculate on some kind of series method where each $1 of the EM increases the price by the CURRENT delta, and the delta by the current gamma, and the next $1 of the EM raises the price by the NEW Delta, the delta again by the gamma, and so forth until the EM is completed.

OK, anyone still with me? LOL

Thanks anyone!

Jonathan
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
OK, here's a little further info and a (hopefully) more simplified explanation of what I'm stuck on. I really hope someone can help, I know I'm close but I'm just not quite there yet.

I need to calculate an end result, that is based on a gradually increasing set of numbers, depending on the numbers of times (days) I choose to run the claculation for.

Example:

On day 1, X=A+B
On Day 2, B=B+C and so now X=X (previously defined as A+B)+B
On Day 3, B (previously defined as B+C) now = (B+C)+C and so, X=X+B

and so forth... each time, the updated X value needs to have the previously updated B value added to itself, yielding an ever increasing series of numbers. One more example using actual numbers:

A=1.00
B=.50
C=.02

When T (my time variable) = 0,
X=1.00

T=1
X=1.50 (A+B)
B=.52 (B+C)

T=2
X=2.02 (X+B)
B=.54 (B+C)

And so forth...

How on earth can I calculate this in a single formula? I can do it already with a zillion different cels, one for each possible time increment, and it works fine. But this would ultimately leave me with THOUSANDS of individual cels being used and I want to avoid that.

I've played with arrays but haven't been able to get them to work. Also noticed that Excel doesn't allow much in the way of looping outside of if/then logic, unless you delve into VB which I am totally unfamiliar with.

Thanks in advance for any help... this has been a thorn in my side for several days now.

Jonathan
 
Upvote 0
Hi,

WIll the following UDFs be of any help?

<pre>Function BScdelta(S As Double, K As Double, sigma As Double, Time As Double, _
IntRate As Double) As Double
Dim indata As Variant

indata = BScgreeks(S, K, sigma, Time, IntRate)
BScdelta = indata(1, 2)
End Function


Function BScgreeks(S As Double, K As Double, sigma As Double, Time As Double, _
IntRate As Double) As Variant
Dim d1 As Double, d2 As Double, Nd1 As Double, Nd2 As Double
Dim N1d1 As Double
Dim Kr As Double, outdata(1, 6) As Double, srt As Double

srt = sigma * Sqr(Time)
Kr = K * Exp(-IntRate * Time)
d1 = Log(S / Kr) / srt + srt / 2
d2 = d1 - srt
Nd1 = Application.WorksheetFunction.NormSDist(d1)
Nd2 = Application.WorksheetFunction.NormSDist(d2)
N1d1 = 1 / Sqr(2 * Application.WorksheetFunction.Pi()) * Exp(-d1 * d1 / 2)

outdata(1, 1) = S * Nd1 - Kr * Nd2
outdata(1, 2) = Nd1
outdata(1, 3) = N1d1 / (S * srt)
outdata(1, 4) = -(S * N1d1 * srt / (2 * Time) + IntRate * Kr * Nd2)
outdata(1, 5) = S * Sqr(Time) * N1d1
outdata(1, 6) = Time * Kr * Nd2
BScgreeks = outdata
End Function</pre>
 
Upvote 0
WOW, Thanks Jay... unfortunately I have NO clue what any of that is, nor how to use it. I don't know the first thing about VBA, and honestly was hoping to keep this solution within the bounds of a "normal" excel function, mostly so I could understand it myself. While it would be great to just have someone else hand me the solution just to get my sheet working, I'd really like to understand *how* it's working too. And as I'm not a programmer, this code is way way beyond me.

If you'd care to explain what to do with it, I *would* be interested in seeing if it solves my problem... but as I said, if anyone has a simpler, non-VB based solution, that would be preferred just so I can wrap my head around it.

The formula I've come up with thus far, that *does* work but is very sloppy and tedious to figure out, and only works for up to 10 repetitions, is:

=CHOOSE(EM,call_Bid+Call_Delta,call_Bid+((Call_Delta*2)+call_Gamma),call_Bid+(Call_Delta*3)+(call_Gamma*3),call_Bid+(Call_Delta*4)+(call_Gamma*6),call_Bid+(Call_Delta*5)+(call_Gamma*10),call_Bid+(Call_Delta*6)+(call_Gamma*15),call_Bid+(Call_Delta*7)+(call_Gamma*21),call_Bid+(Call_Delta*8)+(call_Gamma*28),call_Bid+(Call_Delta*9)+(call_Gamma*36),call_Bid+(Call_Delta*10)+(call_Gamma*45))

Where EM is the number of times I expect to run the calculation. To be honest, I'm not even sure exactly WHY this formula works, I just kept playing with the numbers until as if by magic, they gave me the correct result in all cases I threw at them (up until 10 iterations anyway...)

So, thanks to anyone with more ideas!

Jonathan
 
Upvote 0
Dear Sir,

I wanted to ask why in the following step you divided the gamma by 2. Current Delta after a $2.50 underlying move would be .55 (2nd future delta + (gamma/2) but the move is done anyway so I don't care).

And thank you for the explanation.

Thanking You,
Manish Dedhia
+919833209982


I'm trying to calculate some things by hand (well, by computer) just so I have a thorough understanding of how they interact. I'd like to have a spreadsheet that tells me what an option price WOULD BE (not taking volatility into account which is a different can 'o worms) AFTER an expected move was made in the underlying stock. Here's where I'm at right now...

Current Delta is .5
Current Gamma is .02
Current Option price is 1.00
Expected Underlying Move is 2.5 over, say 15 days (# of days is irrelevant in this calculation though)
Underlying stock price is 30
Future Delta after a $1 underlying move would be .52 (current delta + gamma)
Future Option Price after a $1 underlying move would be 1.50 (current option price + current delta)
Future Delta after a $2 underlying move would be .54 (first future delta of .52 + gamma of .02)
Future Option Price after a $2 underlying move would be 2.02 (first future Option Price of 1.50 + first future delta of .52)
Current Delta after a $2.50 underlying move would be .55 (2nd future delta + (gamma/2) but the move is done anyway so I don't care)
Current Option Price after a $2.50 underlying move would be 2.56 (2nd future option price of $2.02 + 2nd future delta of .54)

So, fairly easy to calculate... what I can't figure out how to do is to get Excel to understand that the final option delta after the move is not in fact just (option delta + (gamma*EM)). I hope that makes sense. I need to calculate on some kind of series method where each $1 of the EM increases the price by the CURRENT delta, and the delta by the current gamma, and the next $1 of the EM raises the price by the NEW Delta, the delta again by the gamma, and so forth until the EM is completed.

OK, anyone still with me? LOL

Thanks anyone!

Jonathan
 
Upvote 0
Jonathan,

There's a closed-form mathematical solution for your problem.

Current option price: A, Current Delta: D and current gamma: G

Then for Time period, T = n, the values are:

Delta_n = D + n * G

and for option price at T=n:

X_n = A + n*D + n*(n-1)*G/2

Hope this helps,
Sahand

----
sahandi (at) hotmail (dot) com
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top