how to speed up excel workbook

[cor]

hi all
i'm running a montecarlo simulation on excel 97. my workbook has 10
sheets with different annual strategies of portfolio insurance. in the
first sheet there is the random stock process, which is copied in the
other sheets (simply with the formula =sheet1!cell1 in every cells).
instead of normsinv i'm using a vba code for the inverse cumulative
function. the last sheet is the output sheet, where i collect three
results for every strategies (so for 1000 simulations i should get
30.000 data). this is done via vba code (calculate and copy the data
in every sheets in the output one).
using my celeron 700mhz notebook it takes 5 min. for 2 steps
simulation. for a 100 steps simulation it should take 4 hours, but the
computer crashes. when i save a change in a cell or a formula, it
takes minutes!

any suggestions for modifying the workbook? is there a way to speed up
the calculations? should i use newer excel (2000 or 2002)? or use a
pentium 3-4 computer?
thanks in advance

 

[KJTFS]

On thing you could do is in the vba code turn off autocalculate when you
are doing something then turn it back on when you are finished. I
would also turn it off in general for a workbook that has that much
linked data, that way you can make changes and the workbook won't spend
time calculating cells. When you need to have data updated hit F9 and
everything will calculate. That is one way to help. Unfortunatly with
monte carlo sims they are very very big so it is a resource heavy
program. I have developed some monte carlo sims in the past you can
check them out at www.kjtfs.com. I know some can bog down my 1Ghz
machine, but I also have a 3.6Ghz overclocked machine that handles them
in a snap. If your simulation is financial based and isn't
proprietary please email it to me so I can look at it. I always like
looking at new monte carlo methods.

Keith
www.kjtfs.com