> - Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Volatility
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- GoalSeek
- You know that the same function is applied uniformly over the time horizon, and that one "rogue" formula is not different to its neighbours.
- Computationally it is more efficient because the input and output code to the function is only done once for the array of cells instead of once for each cell.
- Editing an array formula is a bit of a pain because you have to press Ctrl-Shift-Enter all the time to re-input the changes.
- The array formula concept is more complex to grasp. Business Functions arrays however are pretty simple, because its simply a function returning an array of outputs, one for each time in your timebase. However some uses of array formulae are mind-bogglingly complicated and you may have taken the understandable decision not to go near them.
- Inserting rows or columns into the array formula"s area requires the array formula are re-specified.
- Complete the function inputs as normal for the first period or time your cash flow.
- Instead of putting a single time or date in the first argument, put in the range of dates that correspond to your timebase.
- Enter the function as an array formula by pressing Ctrl-Shift-Enter.
- {=Con(E2:G2,12,2000,2100,1000)*2}
- {Con(E2:G2,12,2000,2100,Grow(E2:G2, 100, 2100, 5%))*2} confuses Excel (and us!) because the annual rate is specified as an array itself.
- =Con(E2:G2,12,2000,2100,1000)*2*Grow(E2:G2, 100, 2100, 5%)
- Only the first argument of a function is ever "arrayable", so it"s usually Time (becoming Times), or TheDate (becoming TheDates).
- For maximum efficiency, best practise and optimum structuring, use Array Functions.
- When you"re building a model, particularly a small one, use the conventional copy-across method - ultimately its more flexible.
- If you"re unsure about Array Functions, start in small way or avoid them for now.
Array Functions | |
| Many Business Functions are also Array Functions - here is how to use them | |
| Most of the functions in the projections categories can also be used as array functions. Array functions are particularly suited to the Business Functions approach of having a common formula applied across a timebase. The advantage of array functions over conventionally copying a formula across all the cells in your cash flow are: To use a Business Function as an array formula: For Example: Con(ListofTimes,Base,Start,Finish,AnnualRate) ListofTimes could be entered as B2:J2 for instance. You"ll notice that Excel puts curly brackets {} around the formula once entered like this: Where a Base variable exists, it is ignored - you can put whatever you like in for it. The Base for each time period is established from the gaps between the list of times itself, and the Base for the last period is the same as for the previous period. You can include other functions and operators, as part of a larger array formula and still return an array. For Example: But be careful. The following example won"t work: This following example does actually work, because it"s two one dimensional arrays multiplied together, but you need be confident with array formulas to do this: On the Utilities menu you"ll find some useful little tool designed to help you work with array functions. If you put an array function with a range of times into a single cell only, the first result from the output array will be returned. This is quite useful because you can test the formula on one cell before "arraying" it across the range. Business Functions has a simple rule with its array functions: Conclusion |