Non-volatile UDFs recalculating unexpectedly--why?

[mrmattj]

Hello all,

First-time post, so please excuse my mistakes. I've hit an issue that I haven't managed to solve by searching through previous questions--so I am finally turning to the experts!

I have a spreadsheet that performs two functions: 1. calculating Black-Scholes option prices from a given volatility input; 2. backing-out implied volatility from the market option price. The first is performed entirely within Excel. The second--the volatility calculations--are performed using a simple Newton Raphson iterative UDF (below) in VBA.

The formula to call the UDF is:

=IFERROR(IVolCall(Spot Price, Strike Price, Risk-Free Rate, Dividend Yield, Days to Expiry, Market Option Price),"N/A").

All inputs are contained within the spreadsheet itself.

The issue I am having is that this formula recalculates on any change to the worksheet. I initially thought it was just hiding and unhiding rows, but a simple test script to insert a number into a blank cell also causes Excel to run through each UDF. To the best of my understanding, the UDFs shouldn't be volatile and so I am not sure why this is occurring.

Any help would be much appreciated!

Thanks,
Matt

IVolCall code (Dims excluded):

Function IVolCall(S, X, r, q, days, Price)

  'The initial implied volatility estimation is hard-coded for simplicity.
    
    Guess = 0.15
    
    'Tolerance for iteration
    
    Difference = 0.05
    
    '365 trading days are used
        
    t = days / 365


    ert = Exp(-r * t)
    eqt = Exp(-q * t)
    
    'Loop to 1000 or until the minimum difference is met.
            
    For I = 1 To 1000
         
        d1 = ((Log(S / X) + (r - q + 0.5 * Guess ^ 2) * t) / (Guess * Sqr(t))) 'Calculate d1 in B.S.
        d2 = d1 - Guess * Sqr(t) 'Calculate d2 in B.S.
        nd1 = Application.NormSDist(d1)
        nd2 = Application.NormSDist(d2)
                   
        Value = (S * eqt * nd1 - X * ert * nd2) 'B.S. call
        
        Vega = S * eqt * Sqr(t) * Application.NormDist(d1, 0, 1, False) 'B.S. vega
                           
        Guess = ((Price - Value + Vega * Guess) / (Vega)) 'Refined volatility estimate
          
            If Abs(Value - Price) < Difference Then Exit For 'Iteration criteria
        
    Next
   
IVolCall = Guess
    
End Function

 

[mole999]

> Volatile Excel Functions -Decision Models

 

[mrmattj]

Hi Mole - thanks for replying. I've read through that link, but am still having trouble understanding this issue. Would you mind helping a little more?

I have the same problem even in an entirely new worksheet with only a very simple function. The UDF recalculates the first (and only the first) time any cell is changed.

Function Half(x As Integer)
    
    Half = x / 2


End Function

Any sub that changes any cell in the worksheet first steps through the UDF. Example below.

Sub Test()


    Cells(2, "D") = "Test"


End Sub

The first time the sub runs, it steps through the UDF and recalculates it. If you then delete "Test" from D2 and run the sub again, it does NOT step through the UDF. If you then change the reference to a difference cell, the process starts again and the UDF recalculates.

I don't understand why this occurs. Is there any way to stop the UDF recalculating when a cell is changed for the first time?

Thanks!

 

[mole999]

Not sure about the UDF but any change on the worksheet that cause any formula to change, or everytime the CF recalculates, not sure of the order.

 

[mrmattj]

Hi Mole.

Hmm. Ok. I'm not changing any part of the formula though.

A1 contains a value of 5. The UDF is in B1 and references A1. If you change any random cell on the worksheet the UDF recalculates. If you empty that cell and then repeat the sub, the UDF doesn't recalculate.

I'm finding that no matter what I do, the UDF recalculates the first time ANY cell is changed.

Thanks.

 

[mikerickson]

Do you have other volatile functions (e.g. TODAY() ) elsewhere in the workbook?

 

[mrmattj]

Hi Mike,

Nope. The test worksheet I created just has the value, the UDF referencing that cell, and the sub which enters a value in a different, unrelated cell. I filmed a small video showing the issue (please let me know if this isn't allowed--it's uploaded on Tinypic). As you can see, the first time any cell is changed, the UDF is recalculated.

I really want to know if there is any way to prevent this (or if I am just doing something really silly)--it becomes a big problem on my actual project.

View My Video

Thanks!