-
- 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
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.
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. |