|
Array Functions- When your model is nearly complete and performance is the issue, consider using Business Functions as array functions.
- Array functions are functions that are specified for a range. In cashflow modelling terms this is an entire row or column
- Each Business Functions projection function is implemented as an array function too. The method is very straighforward: instead of inputting a Time and a Base, you put a range of dates in Time corresponding to the start of each timeperiod, and the Base is ignored.
- Array functions are 30-50% quicker because, although they do the same basic calculations as the normal function, the initialisation, error checking and return code is only executed once for the whole range. Additionally, functions such as Loan cache intermediate values resulting in greatly reduced calculation load.
- Array functions are slightly cumbersome in use, because Excel requires that all edits are done using Ctrl-Shift-Enter to commit the change. Business Functions provides a couple of tools, Expand and Collapse, available from the Utilities menu, that quickly and easy break an array function back to its first cell so that y ou can edit it, and then expand it back along the row or column required. A beneficial by-product of the cumbersom editing of array functions is that some protection is afforded against accidental changes or ill-advised tampering.
|
