Results 1 to 7 of 7

Stock Options calculations - How to calculate Delta after a

This is a discussion on Stock Options calculations - How to calculate Delta after a within the Excel Questions forums, part of the Question Forums category; I'm trying to calculate some things by hand (well, by computer) just so I have a thorough understanding of how ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    183

    Default

    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

  2. #2
    Board Regular
    Join Date
    Oct 2002
    Posts
    183

    Default

    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

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    WIll the following UDFs be of any help?

    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

    Bye,
    Jay

  4. #4
    Board Regular
    Join Date
    Oct 2002
    Posts
    183

    Default

    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+(Cal l_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

  5. #5
    New Member
    Join Date
    Nov 2010
    Posts
    1

    Default Re: Stock Options calculations - How to calculate Delta after a

    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


    Quote Originally Posted by vanclute View Post
    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

  6. #6
    New Member
    Join Date
    Nov 2011
    Posts
    1

    Default Re: Stock Options calculations - How to calculate Delta after a

    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

  7. #7
    New Member
    Join Date
    Jun 2011
    Posts
    19

    Default Re: Stock Options calculations - How to calculate Delta after a

    The spreadsheet at the bottom of this page will calculate the Greeks (Delta, Gamma, Theta) for vanilla and exotic options: http://investexcel.net/736/binomial-...pricing-excel/

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com