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
    Using Daycount
    Detailed discussion of DayCount and Periods
    See also: Examples of DayCount, Calculating Interest

    Daycount comes up a lot when using the library and, as many financial modellers have found, it is substantially more complex than meets the eye. For example:
    • Fractions of a Year: If rent is paid quarterly, is one quarter"s rent equal to a quarter of a year"s rent or the number of days in the quarter divided by 365?
    • Number of Days: Similarly for interest, how you decide how many days to calculate interest for?
    • Stub Periods: If there are stub periods (partial quarters at the beginning or end) is interest or rent equal to the number of days in the stub period over 365, or is it the number of days divided by the number of days in the quarter times one quarters interest?
    • Business Days: How do you take into account the fact that interest is rarely paid on a holiday?
    This section explains how use the library to correctly describe scenarios such as those just described. At the same time it explains how the defaults (Daycount variables are usually optional) arrive at sensible answers. If you just want examples on usage, see Examples of DayCount

    Introduction

    Daycount is about how to describe the length of time between two dates in terms of a number of years. The length of time is usually measured in terms of a number of years because most rates (interest, rent) are specified as annual rates.

    There are two types of variable that describe daycount in the library:
    • DayCount specifies the method used to calculate the daycount, or exact number of a periods, between two dates. Methods of DayCount such as ACT/365, 30/360 etc are described widely in books and the financial literature.
    • Periods specifies either the length of time between payments or the length of time of accruals periods (usually both at the same time).

    Why two variables?
    We use the DayCount variable as well as the Periods variable in most of the projections functions because:
    • Although daycount is widely considered to be a single choice over the method to be used (Excel"s functions only use one variable), in practise you may need to specify what happens to partial periods and whole periods separately. To do this you need to have knowledge of the period dates themselves.
    • There is a very common method of daycount, known as "ACT/ACT in period" (Actual Days/Actual Days in Period) which demands that you state how frequently and when payments are made. "ACT/ACT in period" applies to situations such as interest payments that are not on exact calendar days and rental payments that fall on odd days (such as English property leases). Most bonds use ACT/ACT (in period) daycount, with Periods at 6 month intervals.
    • When in cash mode, you clearly need to know how long the time is between cash payments - that"s the whole purpose of cash. So Periods tells you when, and DayCount, in combination with Periods and AnnualRates, tell you how much.

    We reckon that the great majority of daycount situations, where you are deciding how much of a year has elapsed, can be described by DayCount and Periods, which is reassuring to know.

    There are two ways Daycount is used in the Library: DayCount when a function is accruals mode, and DayCount in cash mode. (See Accruals and Cash).

    1. Daycount in Accruals mode

    A great many of the projections functions work in two distinct modes according the variable ProjMode - Accruals mode and Cash mode. For example, Con and FStep can work in accruals mode as functions that are simply concerned with how much of an AnnualRate accrues in a time period, or between two dates. On the other hand, they can work in cash mode to project the actual cash payments resulting from the application of an AnnualRate to an annual sequence of payment dates.

    DayCount and Periods are used to describe the daycount scenario. Periods is necessary to be able to perform one additional but important kind of daycount, ACT/ACT (in period), as described earlier. ACT/ACT (in period) is fact very common when preparing projections because you want a to project a fractional number of rental, interest payments or whatever - bonds, leases and loans often implicitly use this form of DayCount.

    The options for DayCount are the same as Excel for options 0-4:
    • 0 denotes 30/360 (US/PSA).
    • 1 denotes ACT/ACT (365 or 366).
    • 2 denotes ACT/360.
    • 3 denotes ACT/365.
    • 4 denotes 30E/360, also known as European 30/360.
    • 5 or omitted denotes Decimal Month (ACTM/12). This is the default and represents the number of months between two dates divided by 12. Dates are converted into a decimal month based on the day number and the actual days in the month. This method of daycount does not correspond to any official "standard", but is a great compromise for giving sensible results in a variety of situations, which is why we adopted it is the default.
    • 6 denotes ACT/ACT (in period). With this method the number of days between two dates is divided by the number of days in the specified period to give the exact number of periods, including fractions. Then the amount paid is this fraction multiplied by the annual rate and divided by the number of periods in the year.
    • 7 is the same as 0, 30/360.
    • 8 denotes ACT (Non Leap)/365: Same as ACT/365 but ignoring Feb 29.
    • 9 30/365: (same as 30/360 but with 365 denominator)
    • 10 30/360 (ISDA)
    • 11 30/360 (ISDA + Feb Adj)
    • 12 30/360 (ISDA + EOM)
    • 13ACT/252
    For further detail look up the DayCount variable .
    Periods can either be a sequence of dates or a preset option as follows:
    • 1 denotes calendar years i.e. 1st Jan etc
    • 2 denotes calendar half-years i.e. 1st Jan, 1st July.
    • 4 is the default and denotes calendar quarters i.e. 1st Jan, 1st April, 1st July and 1st Oct.
    • 12 denotes calendar months i.e. 1st Jan etc
    • 13 denotes English Real Estate Quarter Days i.e., the 25th March, 24th June, 29th September and 25th December each year.
    ACT/ACT (in period) Daycount (type 6) requires a value for Periods- if one is not supplied Periods assumes calendar quarterly, which is not always what you wanted. If you are actually in doubt what the function is using, make use of Trace or use functions such as DescribeDayCount and DescribePeriods, using the same value(s) of the arguments as you are using in your function, to explain in plain English the assumptions used in the function.

    Input of either or both of DayCount and Periods is optional:.
    • If both are omitted, the function will use Decimal Month daycount, giving pretty sensible results over a broad spectrum of application. It does not need or require a value for Periods.
    • If Periods is omitted the function will use any of the DayCount options in the usual way. If option 6 (ACT/ACT in period) is specified but Periods isn"t, quarterly Periods will be assumed. Again, a sensible result.
    • If DayCount is omitted but Periods is specified, it depends how this has been done. IF DayCount has in effect been specified as "missing" by just putting a comma with no entry in a function (e.g. myfunction(AnnualRate, ,12) then DayCount will just default to Decimal Month (value of 5) and the specified Periods will be used. If it is missed out altogether as if it wasn"t there (e.g. myfunction (AnnualRate,12), then an error will result because you have accidentally tried to set DayCount instead of Periods. However, if you specify myfunction(AnnualRate,2), thinking you will get biannual Periods, you will have inadvertently set ACT/365 DayCount with default quarterly periods.

    Specifying Periods directly (instead of using one of the presets) is possible by inputting a range of your own annual sequence numbers in the format mm.dd, for example {1.01,4.01,7.01,10.01}. The preset date sequences in fact can each be expressed this way:
    • 2 is equivalent to {1.01,7.01}
    • 4 or omitted denotes calendar quarters i.e. 1st Jan, 1st April, 1st July and 1st Oct and is equivalent to {1.01,4,01,7.01,12.01}
    • 12 is equivalent to {1.01,2.01,3.01,4.01,5.01,6.01,7.01,8.01,9.01,10.01,11.01,12.01}
    • 13 is equivalent to {3.25,6.24,9.29,12.25}
    Incidentally, the curly brackets {} are not for presentation only. Any time you want to specify a range in an Excel formula by directly typing in a list of values rather than referencing a location somewhere else on the spreadsheet, you can use curly brackets to describe a range.

    Differing Daycount for whole and stub periods is a common requirement. When you require that all quarterly payments should be equal but that partial quarters should be calculated on an ACT/365 basis, you are basically saying that you want ACT/ACT (in period) for entire periods and ACT/365 for the stub periods. The library caters for this in the following way:
    • For the payment stream as a whole, you specify an DayCount as a number from 1 to 12, usually 6 for something like rental projections and 2 for interest calculations.
    • To specify a different Daycount for the stub periods, you use a format ww.ss, where ww is the daycount for the whole periods, and ss is the daycount for the stub periods as follows:
    • Before the decimal point: The Daycount method for Whole Periods.
    • After the decimal point: The Daycount method for Stub Periods.
    Examples of composite Daycount options would be:
    • 6.03: ACT/ACT (in period) for whole periods, ACT/365 for stub periods (eg property rents).
    • 5.03: Decimal Year (ACTM/12) for whole periods, ACT/365 for stub periods.
    In versions of BF prior to 1.21 there was an old "2 digit" system that is still valid for use, but can only cope with daycount types 0 to 9 (see Old Composite DayCount Format).

    Note that you sometimes can"t specify daycount type 0 directly. For example 6.00 signifies type 6, not type 6 for whole periods and type 0 for stub periods, In this case used type 7, which is the same as type 0 ie 67. However, you can go 6.0006 and the program will recognise types 6, 0 and 6.

    Slipping to the next Business Day is quite often required, and you can do this by implanting a Business Day Switch into the date sequence described by Periods. Slipping to the next Business Day is also know as the Following Business Day Convention. BF does not currently do Preceding or Modified Following Business Day conventions.

    The Business Day Switches, as described by the variable BusDaySwitch, have values like 10,20, 100, 200 etc.
    • Example 1: for a 4 period date sequence that slips according to UK Business Days, you could specify: {1.01,4.01,7.01,10.01,100}.
    • Example 2: whereabouts the BusDaySwitch goes in the range does not matter - you could equally specify: (100,1.01,4.01,7.01,10.01}.

    2. Daycount in Cash mode

    Functions in cash mode (ProjMode=1 or 3) again use two variables to describe daycount. They use DayCount (just as in accruals mode), and use Periods to describe when and how cash payments are actually made. Periods is much more important in cash mode because you probably won"t want to use the default of calendar quarters payable in advance.

    DayCount has the exact same options as in accruals mode:
    • 0 denotes 30/360 US (PSA)
    • 1 denotes ACT/ACT
    • 2 denotes ACT/360
    • 3 denotes ACT/365
    • 4 denotes 30E/360
    • 5 or omitted denotes Decimal Month (ACTM/ACTM). This is the default.
    • 6 denotes ACT/ACT (in period).
    • 7 is the same as 0, 30/360. This setting is just there for when you are using composite (2 and 3 digit) daycount, and using zero just isn"t practical.
    • 8 denotes ACT (Non Leap)/365: Same as ACT/365 but ignoring Feb 29.
    • 9 30/365: (same as 30/360 but with 365 denominator)
    • 10 30/360 (ISDA)
    • 11 30/360 (ISDA + Feb Adj)
    • 12 30/360 (ISDA + EOM)
    • 13ACT/252
    For further detail look up the DayCount variable .

    Periods can either be a sequence of dates or a preset option. Additionally, there are options that describe payment in arrears or advance as follows:
    • -12 denotes monthly in arrear on calendar months i.e. payment on 1 Jan for the accrual period Dec 1-Dec 31 etc
    • -2 denotes bi-annually in arrear on calendar months i.e. 1 Jan for the accrual period July 1-Dec 31, etc
    • -4 denotes quarterly in arrear on calendar quarters i.e. 1 April for the accrual period Jan 1-Mar 31, etc
    • -1 denotes annually in arrear on calendar years i.e. 1 Jan for the accrual period Jan 1-Dec 31 the previous year
    • 1 denotes annually in advance on calendar years i.e. 1 Jan etc
    • 2 denotes bi-annually in advance on calendar quarters i.e. 1 Jan, 1 July.
    • 4 is the default and denotes quarterly in advance on calendar quarters i.e. 1 Jan, 1 April, 1 July and 1 Oct.
    • 12 denotes monthly in advance on calendar months i.e. 1st Jan etc
    • 13 denotes English Real Estate Quarter Days i.e., the 25 March, 24 June, 29 September and 25 December each year, paid in advance.

    As described previously, you need to be slightly careful with periods in arrear, because the payment date is the day AFTER the day of the period. For example {-1.01,-7.01} refers to accruals periods 1 Jan-30 Jun inclusive and 1 Jul to 31 Dec inclusive, but payment is made the day after the accrual period ends, on Jul 1 and Jan 1.

    Note again that Periods is always used by the function, even if it is defaulted to quarterly in advance.

    Input of either or both of DayCount and Periods is optional:.
    • If both are omitted, the function will use quarterly in advance payments with decimal month daycount, giving pretty sensible results over a broad spectrum of application, but notable not for interest calculations, where Periods always needs to be in arrears.
    • If Periods is omitted the function will use any of the DayCount options in the usual way. Whether or not option 6 (ACT/ACT in period) is specified, if Periods isn"t specified, a Periods of quarterly in advance will be assumed. Again, a sensible result.
    • If DayCount is omitted but Periods is specified, it depends how this has been done. IF DayCount has in effect been specified as "missing" by just putting a comma with no entry in a function (e.g. myfunction(AnnualRate, ,12) then DayCount will just default to Decimal Month (value of zero) and the specified Periods will be used. If it is missed out altogether as if it wasn"t there (e.g. myfunction (AnnualRate,12), then an error will result because you have accidentally tried to set DayCount instead of Periods. However, if you specify myfunction(AnnualRate,2), thinking you will get biannually in advance Periods, you will have inadvertently set ACT/365 DayCount with default quarterly in advance Periods.

    Specifying Periods directly (instead of by the presets) is possible by inputting a range of your own annual sequence numbers in the format mm.dd (for payments in advance) or -mm.dd for payments in arrear. The preset date sequences are in fact the equivalent of:
    • -12 is equivalent to {-1.01,-2.01,-3.01-4.01,-5.01,-6.01,-7.01,-8.01,-9.01,-10.01,-11.01,-12.01}, which means periods that accrue for each calendar month and payment is made on the first day of the following month.
    • -2 is equivalent to {-1.01,-7.01}, which means periods that accrue between 1 Jan and 30 Jun inclusive and between 1 Jul and 31 Dec inclusive, payment being made on the first day of the following month (1 Jul, 1 Jan).
    • -4 is equivalent to {-4.01,-7.01,-10.01,-12.01}
    • -1 is equivalent to {-1.01}
    • 1 is equivalent to {1.01}
    • 4, zero or omitted is equivalent to {1.01,4,01,7.01,12.01}
    • 2 is equivalent to {1.01,7.01}
    • 12 is equivalent to {1.01,2.01,3.01,4.01,5.01,6.01,7.01,8.01,9.01,10.01,11.01,12.01}
    • 13 is equivalent to {3.25,6.24,9.29,12.25}

    A note about February: To specify the last day in February in arrears, the setting is -3.01, meaning accruals will continue until the end of the 28th or 29th Feb, depending on leap years, and payment will be made 1st March. If you specify (rather unusually) -2.29, this means, in a leap year, accruals to 28th Feb paying out on the 29th Feb and in a normal year accruals to 27th Feb paying out on the 28th Feb. The reason for this is that you have to make a distinction in your input between the last day in February {-2.29} and the 28th of February {-2.28}, which are not the same thing.

    Differing Daycount for whole and stub periods is dealt with the same as in the Accruals mode above.

    Slipping to the next Business Day is again similar to Accruals mode above.

    A couple of points on Advance and Arrears in Accruals Mode
    As described, -ve values for Periods imply payments made in arrear. There are couple of points of potential confusion to clear up however:
    • Even when in accruals mode, can you specify payments in arrear for Periods? Yes you can, but Business Functions will give the same result as if you had specified them neutrally. That is because payments in arrear don"t really make sense in an accruals situation - all that matters is the time period you are talking about. You can equally specify {1.01,7.01} or {-1.01,-7.01} - it"s the same thing.
    • When you"re specifying payments in arrear, it"s the same as sticking a minus sign in front of payments in advance. That"s because the accrual period is the same. Note however that the payment date for payments in arrear will be the day after the accrual period ends e.g. July 1 for Jan 1 - Jun 30.

    More Information on Daycount

Business Functions Ltd, London, UK Website Design: Webpure