May 12th, 2005, 12:04 pm
since i'm feeling in a generous mood, here's one of my VBA functions - you'll need to replace VACBD with something like WorksheetFunction.BINOMDIST from Excel (though it blows up after 1030 points)Function VAEuroOptionQBinLR#(icp%, S#, K#, r#, q#, tyr#, sigma#, nstepOdd%)' Returns Quick Bin LR Euro Option Value' Uses VACBD fn Dim sigt#, d2#, c1#, pu#, pd#, M1#, u#, d#, bind2#, bind1#, ve# Dim ia% sigt = sigma * Sqrt(tyr) d2 = (Log(S / K) + (r - q - 0.5 * sigma * sigma) * tyr) / sigt c1 = d2 / (nstepOdd + 1 / 3 + 0.1 / (nstepOdd + 1)) pu = 0.5 * (1 + Sign(d2) * Sqrt(1 - Exp(-c1 * c1 * (nstepOdd + 1 / 6)))) c1 = c1 * (1 + sigt / d2) pd = 0.5 * (1 + Sign(d2 + sigt) * Sqrt(1 - Exp(-c1 * c1 * (nstepOdd + 1 / 6)))) M1 = Exp((r - q) * tyr / nstepOdd) u = M1 * pd / pu d = M1 * (1 - pd) / (1 - pu) ia = Max(0, 1 + Int((Log(K) - Log(S) - nstepOdd * Log(d)) / (Log(u) - Log(d)))) bind2 = 1 - VACBD(ia - 1, nstepOdd, pu) bind1 = 1 - VACBD(ia - 1, nstepOdd, pu * u / Exp((r - q) * tyr / nstepOdd)) If ia <= nstepOdd Then ve = S * Exp(-q * tyr) * bind1 - K * Exp(-r * tyr) * bind2 Else ve = 0 End If If icp = -1 Then ve = ve - S * Exp(-q * tyr) + K * Exp(-r * tyr) VAEuroOptionQBinLR = veEnd Function