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
    ProjMode and Inclusive Dates
    Using ProjModes 3 and 4 for a subtle change in defining the finish date
    Certain values of ProjMode allow for including the finish date in calculating the amount in a projections function.

    You may be aware that Business Functions usual method of operating is to "include the start date, exclude the finish date". This is done to be literally correct (a date difference is one date less another, non-inclusive), to be intuitively correct, to be consistent with the way interest and other calculations work, and to be consistent with Excel"s date methodology where the date number represents the first instant of that day. It does, however, sometimes lead to unexpected results: a property lease that expires on 30th June only includes rent up to the 29th June, for example, not the result you would want.

    There are two additional values of ProjMode, 3 and 4 that allow for this situation.
    0Accruals (normal ie include start date, exclude finish date)
    1Cash (normal ie include start date, exclude finish date)
    2Accruals (include finish date)
    3Accruals (include finish date)

    These two settings are therefore recommended where you have a specific reason to include the last day in the projection, for example if it specifies this in a business contract.

    As a general rule, we recommend sticking to ProjModes 0 and 1, which are more intrinsically consistent and logical. Note also that Loans would nearly always operate with an "exclude the finish date" philosophy.

    How ProjModes 2 and 3 Work
    This is very simple. There are certain variables that are classified as "finish" variables - you"ll see them listed under each function definition inasmuch as they pertain to that function. In the input screening section of BF, if a ProjMode 2 or 3 is encountered, the finish variable(s) for that function is incremented one day. Then ProjMode is reset to 0 and 1 and execution continues.

    Note that it is just "finish" variables that are dealt with this way. A variable like Finish will be adjusted in the manner described, but not a variable like Start or MidPoint.

    Where the "finish" variable is part of a range, like ToDates, it is the last date in the range that is assumed to be the finish variable and is adjusted, the other dates remain unchanged. Therefore it is only the true end date of the projection that is shifted, the dates of any transition in rates or levels in the function are unchanged.

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