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
    Inclusive and Exclusive Dates
    How BF deals with the first and last days of a Time Period
    "Include the first, Exclude the last"
    The question frequently arises: "When specifying a rate, level or whatever from a certain date to another one, how do I know if it the time period is INCLUSIVE of both end dates or not?"

    The general rule the library follows is that of first day inclusive, last day exclusive, except where clearly stated. Examples of situations that follow the rule are:
    • Rates, such as used in the Constant Rate Projections category. For example, the function Con includes the first day and excludes the last. So to specify a one quarter time period, you may correctly specify "1/1/2002 to 1/4/2002". However, specifying "1/1/2002" to 31/3/2002" will leave you with one day"s too little amount calculated (it will omit March 31). Thats because the rate will run from and including any Start date until just excluding the Finish date.
    • Where FromDates are involved, the rate applies from and including the relevant FromDate and apply up to but excluding the next FromDate.
    • Where ToDates are involved, the rate applies from and including the last ToDate and applies until but excluding the next ToDate. So a function like TStep with a Start date of 1/1/2003 and ToDates of 1/1/2004 and 1/1/2005 is exactly equivalent to a function like FStep with a Finish date of 1/1/2005 and FromDates of 1/1/2003 and 1/1/2004.
    • Payments, such as used in the Discrete Amount Projections category and, for example used in the function MkPmts, apply on the date they are specified and are paid according to whether they fall in the time period specified by Time and Base. If they fall exactly on the day indicated by Time, they are paid. Base is an exact multiple of months and similar to the rules for rates above, if a payment falls exactly Base months after Time, it is not paid. In other words, the general rule applies that the time period includes the first day and excludes the last.
    • Levels, such as used in the Instantaneous Level Projections category and for example used in the function Level follow the general rule of running from and including any Start date up to but excluding the Finish date.

    Annual Date Sequences

    There is an instance where the "include the start, exclude the finish" rule gets called into question, BUT IT IS STILL ADHERED TO. It is when specifying "in arrears" Annual Date Sequences such as Periods, where it is very tempting to specify the period with reference to the last day in the period, rather than the first day of the next. For example, to split the year into two calendar halves its wrong to have a Periods of {-6.30,-12.31} but it"s right to have {-1.01,-7.01}.
    • Specifically, an annual in-advance date sequence {1.01,7.01} refers to the same 2 periods as the in-arrears sequence {-1.01,-7.01}, its just that the second sequence is PAID in arrears.
    • If a Date Sequence is specified in arrears, it is assumed that the cash payment is made on the DAY AFTER the last day of the accrual period. In loan agreements this is often phrased as "interest will accrue from the first day of an interest period up until (but not including) the next interest payment date". The key phrase is "not including".
    • Specifying "in arrears" date sequences makes sense, and is necessary for, functions in cash mode (e.g. in Con), but it is not required in accruals mode. The sequence {1.01,7.01} is identical in its effect to {-1.01,-7.01}, when the function is in accruals mode because they both refer to the same underlying time periods, regardless of the timing of payment.
      The facility to specify Periods in arrears (even when in accruals mode) is provided so that you could, if you want, refer to an already set up sequence that happened to be in arrears, or if you though it might make a difference but weren"t sure - it certainly is not necessary and for clarity, in accruals mode, functions should generally have a positive Periods. In cash mode, you obviously need to be specific about advance or arrears sequences.

    Special Case : ProjModes 2 and 3
    There are occasions when you want include the finish date. That"s when the projection you are modelling finishes on a certain date, and the cashflow, rate, level or whatever runs up to and includes that last day. In this instance you can substitute ProjMode 2 for ProjMode 0 and ProjMode 3 for ProjMode 1. For more details see ProjMode and Inclusive Dates.

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