HELP my function is acting weird

[chenyu768]

HELP! This function doesn't always work. sometimes it'll calculate sometimes it won't. I checked auto calculate and i pressed F9. It seems to work sometimes when i retype the formula but considering i have 24months x 2000 clients it can get a little much.

Can someone please help audit this for me!

Function Delta_Call(pipe, date_val, nymex_vol, usage, exp_date, asof_date, call_prc, k_type, Fixed)

If k_type = "PARTNER" Then
call_prc = Fixed
Else
call_prc = call_prc
End If

index_val = WorksheetFunction.Index(Range("monthly_index"), WorksheetFunction.Match(pipe, Range("indexes_names"), 0), WorksheetFunction.Match(date_val, Range("Index_date"), 0))

tT = (exp_date - asof_date + 1) / 365
p1 = (Log(index_val / call_prc)) + (nymex_vol ^ 2 / 2 * (tT))
p2 = (nymex_vol * tT ^ (1 / 2))
d1 = p1 / p2


CallDelta = (WorksheetFunction.NormSDist(d1) * Exp(-0.025 * tT))
Delta_Call = CallDelta * usage


End Function

 

[VoG]

Try this

Function Delta_Call(pipe, date_val, nymex_vol, usage, exp_date, asof_date, call_prc, k_type, Fixed)
Application.Volatile
If k_type = "PARTNER" Then

 

[chenyu768]

Holy mole, You sir are a gentleman and a scholar. This totally worked. Thank you so much, you just saved my life!