Calculation speed

From Financial Modelling Wiki
Revision as of 13:49, 5 February 2022 by Levi (talk | contribs) (Created page with "[https://mvp.microsoft.com/en-us/PublicProfile/4025136?fullName=Charles%20Williams Charles Williams] generously shares information on improving Excel calculation speed (as well as VBA processing time) and his [http://www.decisionmodels.com/ Decision Models] and [https://fastexcel.wordpress.com/ Fast Excel] websites are considered by a lot of experienced modellers to be the best reference point on the topic. It is worthwhile for anyone who spends a lot of time modelling i...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Charles Williams generously shares information on improving Excel calculation speed (as well as VBA processing time) and his Decision Models and Fast Excel websites are considered by a lot of experienced modellers to be the best reference point on the topic. It is worthwhile for anyone who spends a lot of time modelling in Excel to go through those sites. A good starting point is to read the following pages in full:

The information that Charles shares is for a general technical audience - it is not solely dedicated to financial modelling. Financial models often have calculation characteristics that are outside broader norms for Excel workbooks, including:

  • Multithreaded calculation: In most Excel workbooks generally, multithreaded calculation is usually faster than single threaded calculation. In financial models, the reverse is often true. It can go one way or the other (often by wide margins) and, if calculation speed is a consideration in your model, it is definitely worth testing.
  • Smart recalculation vs forcing full calculations: In most excel workbooks generally, Excel's smart recalculation engine is plainly helpful because creating and maintaining the dependency tree is very quick and it improves calculation speed. In financial models, maintaining the dependency tree often takes longer and the smart recalculation engine often only provides a small improvement in calculation speed (sometimes it even makes the calculation speed worse), so there is commonly a stronger argument for forcing full calculations in financial models. That said, common financial modelling workflows are also particularly sensitive to other downsides of forcing full calculations (i.e. trace dependents becoming unreliable and charts generally not updating).