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
    Time Periods
    How BF defines Time Periods
    The projections categories of the library employ a method of specifying a time period using a Time and a Base.

    • Time is the start of the time period. It can either be a normal date (displayed by Excel as, say 1/1/2003, but actually a number such as 36824), or a "decimal year", such as "2003", "2003.25" or "2003.1666". This specifies the exact day that the time period starts. See Dates in Excel and Business Functions.

    • Base specifies the length of the time period. It is in months, and BF will only accept a whole, or integer number of months, so a time period of 2.5 months is not allowed (you will get a NUM! Error reported, which if investigated by Trace will tell you that the Base was illegal.) The reason for the restriction, incidentally, is to save confusion and errors. You rarely ever need a time period of a fraction of a month, but see the notes below for a workaround should you need it.

    You can also specify the end of the time period by inputting a date or decimal year as a Base. You can input any date, including dates midway through a month, so this is a way round the restriction of integer base months should you need to use it. Note if you specify the end date of a timeperiod, the timeperiod runs up to but excludes this date, in accordance with BF"s rules on Inclusive and Exclusive Dates (incidentally: this is not affected by use of extended ěProjModesěs).

    The best way of investigating time periods is to look at a simple projections function like Con or the functions of the Time Period family.

    Dates as Text
    Don"t be tempted to use an inconsistently applied option in Excel to input dates as text, ie in quotes or as text in a cell. Some of Excels functions do actually accept dates specified as text, but even the Excel help writers but a big health warning on them. No, dates are numbers, even if Excel represents them to you, the viewer, as dates. Sometimes Excel has to be bullied to accept dates properly, for example when it persists in calculating 1/1/2000 as a division sum. Excel tries to second guess what you mean when you input dates in this way, and sometimes it gets it wrong. Be aware.

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