Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    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:
    • 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.
    Array formulae are not everyone"s cup of tea and you can still use the conventional copy-across approach if the following factors are important to you:
    • 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.

    To use a Business Function as an array formula:
    • 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.

    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:
    • {=Con(E2:G2,12,2000,2100,1000)*2}

    But be careful. The following example won"t work:
    • {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.

    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:
    • =Con(E2:G2,12,2000,2100,1000)*2*Grow(E2:G2, 100, 2100, 5%)

    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:
    • Only the first argument of a function is ever "arrayable", so it"s usually Time (becoming Times), or TheDate (becoming TheDates).

    Conclusion

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

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure