> -
- Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Volatility
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- GoalSeek
- 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.
- 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.
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: 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}. 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. |