Well, here is a partial translation from the linked source to Excel. You will
need the Analysis toolpack atpvba.xla to be loaded/linked: it is used for
the complex arithmetics needed (very slow, for better performance write
your own) in VBA. The integrands are oscillating, but if the strike is not
to far from spot, they are 'reasonable' enough for a Gauss-Laguerre approx
and using 18 points (pre-computed, they are not in Abramowitz-Stegun) seems
to gives a good mixture of exactness ( ~ 5 digits) and speed within Excel.
One might compare to 9 pts or mid-point-summation by looking in the code.
Remembering the above bottle neck one should look also at pb273's solution.
Feel free to post comments: improvements/errors/hints ...

Edited: Sun Sep 28, 03 at 10:12 PM by AVt

Heston93_atpvba.zip  (21 KB)

My above translation is not very satisfactory: slow and unprecise.

I took it as an exercise: what can be done in Excel? Find the answer uploaded.

For this the function evaluation has to be speed up first. After massaging
the code the result seems nice, there is a routine tstRunTimeHestonIntegrands
to check it. Next is integration. And Gauss-Laguerre is not the right one.
One thing would be FFT (but Excel has not a reasonable one and it would be
a mess with exactness). So i thought about adaptive quadrature but after some
hack around i decided to equal spaced slicing in subintervalls and to apply a
fast and simple Gauss-Legendre method. Due to Excel's restricted excactness of
~ 15 digits a choose of degree=16 works (the higher degree above is nonsense,
it introduces error). Playing with exacter tools a cut off for integration at
128 gives good results.

That is the default used in the sheet. To change it set the global variables gLength
and gLengthSubInterval (module mHestonIntegrals) appropriate by opening
the VBA project. For the concurrent values use tstRunTimeHestonValue to check
speed: if one uses 64 and 16 it is much faster with 4 - 5 digits of exactness
for realistic parameters (for very short times one has to obey the oscillation,
i did that in a rude way, dont be careless - anyway Heston is not for short
experies ...).

The sheet contains some reference values to check excatness (esp if one wants
to integrate coarser). The values have been computed with the NAG library using
Maple for cutoff=128 and cutoff=180. It is a very reliable lib (it even checks
round off errors), and limited to machine precision it gives ~ 14 - 15 digits.

Which is enough to cross check Excel :-) For the default parameters against the
reference values the exactness of the implemented Excel solution is ~ 10^(-9)
(with a speed of 45 option values per second [on a 900 Mhz PC], increasing
almost linear to 180 values per sec for the coarser grid - the speed to
evaluate the real parts of the 2 integrands is 11500 pairs per second).

Additional there are 2 simple grafics to get a feeling what happens with the
integrands, they display on data entered in the sheet 'HestonPrice'.

Of course one could do it properly with C (i dont do), but it was fun with
Excel and i learned a lot through it. Enjoy.

Edited: Sun Nov 02, 03 at 01:42 PM by AVt

Heston93_opt.zip  (53 KB)