Business Functions Library for Excel

##### 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.
 0 Accruals (normal ie include start date, exclude finish date) 1 Cash (normal ie include start date, exclude finish date) 2 Accruals (include finish date) 3 Accruals (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 Ltd, London, UK Website Design: Webpure