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
    Annual Date Sequences
    Description and Use of the AnnualDateSequence data type
    Business Functions uses a data type called an Annual Date Sequence. This is primarily used:
    • To specify Periods and with DayCount to describe the ACT/ACT (in period) Daycount method when a function is an accruals mode.
    • To specify Periods and with DayCount again to describe how cash payments are made when a function is in cash mode.
    • To specify a series of annually recurring dates in date sequence calculations such as NextDateSeqBD.
    What an Annual Date Sequence does is describe a set of dates that recur each and every year. It consists of a range of elements in the form mm.dd. For example:
    • {1.01,4.01,7.01,10.01} describes calendar quarters
    • {1.01,7.01} describes biannual periods.

    Date Sequences describing periods paid in arrear

    The above date sequence descriptions are fine when you are just describing accruals periods which start on various dates, and you either want them in advance for a cash function, or you don"t really care about advance or arrears, such as a function in accruals mode or sequencing function.

    An annual date sequence can also how describe payments are made in cash mode (ProjMode=1), whether advance or arrears. Care must be taken here, as will be explained, but in principle:
    • {-1.01,-4.01,-7.01,-10.01} describes calendar quarters in arrears.
    • {-1.01,-7.01} describes biannual periods in arrears.
    Note that in payments in arrear the payment is made the day AFTER the accrual period. In the above example, payment occurs on 1st July for the period of accrual 1st January to 30th June.
    In general the library adheres to the overall rule of "include the start date, exclude the finishing date", and it does this with arrears periods, too. There is a method of including finish dates in certain circumstances however, see ProjMode and Inclusive Dates.

    You can embed a Business Day Switch in an annual date sequence which can specify the Business Day and Date Rolling Convention.

Business Functions Ltd, London, UK Website Design: Webpure