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.
|