A lambda to encapsulate the calculation of the Financial Quarter-end Date for a given date.

Development Rationale

The purpose in developing this lambda is to simplify the derivation of the financial quarter-end date for a given date in a model, such as a transaction date or other critical action date .  This not an overly complicated thing, but it is useful to have it "tidied away" in a lambda to make it easy to implement in workbooks where that is required.

About the Lambda Calculations

The process of calculating the financial quarter-end for a given date is fairly straight-forward.  You need two pieces of information, the date for which the calculation is to be rendered and the month in which the financial year ends.  The latter is perhaps most simply provided as a number, although an implementation could be done that took the month name, or a month abbreviation.  But working like that with text would then add some issues if different languages were involved.

Since we have some extra steps to traverse, we would be wise to use a LET function to streamline things, and make the layout easier and to avoid lots of deep nesting and redundancy.

Here, I will layout the workings using line wrapping, so each step of the LET function can be read and analysed:

1  =LAMBDA(as_at_date,fy_end_month,
2  LET(fyEnd,λFinancialYearEnd(as_at_date,fy_end_month),
3  monthDiff,MONTH(fyEnd)-MONTH(as_at_date),
4  monthFwd,MOD(IF(monthDiff<0,monthDiff+12,monthDiff),3),
5  EOMONTH(1*as_at_date,monthFwd)))

In line 1 above, the lambda is defined, with two arguments: as_at_date and fy_end_month.  fy_end_month is expected to be a number in the range 1..12,

In line 2, the LET function in the third argument of the LAMBDA encases the step-by-step calculation of the required result.  Initially, a local value called fyResult is derived by calling an associated lambda function from this library - λFinancialYearEnd, which calculates the year end date.

In line 3, the local value monthDiff calculates the difference in months between the year-end month and the month of as_at_date, calculated using a MONTH function for each.  The difference will be a value between -11 and +11

In line 4, the local value monthFwd adjusts monthDiff, adding twelve to negative values where the year-end month is lower.  This value is passed to a MOD function, which returns the remainder of division, in this case by 3.  We now have the number of months forward to the quarter-end month.  This is now a value between 0 and 2.

Finally, in line 5, the result returned by the LET function is calculated, which is the result of the LAMBDA: it calls the EOMONTH function to calculate the end-of-month date for the month monthFwd into the future..  Due to the way that EOMONTH handles horizontal arrays of dates for its start_date argument, 1*as_at_date cures the issue with horizontal arrays that would lead to a #VALUE! error.  The alternative handling to correct for EOMONTH's expectations is significantly more complex, so we have settled on this simple fix.  The fix is only required if a horizontal array is passed to as_at_date.  Single values or vertical arrays do not have the issue.

Defined Name Settings

To create the lambda, simply use the define name¹ feature in Excel to define this named lambda in the workbook where it will be used.  The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.

Syntax of λFinancialQuarterEnd

=λFinancialQuarterEnd(as_at_date,fy_end_month)

where:
   as_at_date is a reference to a range, a nested expression that returns an array or reference to a range, or a constant value with a date or dates whose financial year-ends is/are to be derived

   fy_end_month is a reference to a range, a nested expression that returns an array or reference to a range, or a constant value 1 through 12, representing the financial year ending in months January through December respectively

NB: This lambda function requires the λFinancialYearEnd function to be available in the workbook, since it depends upon it to calculate the financial year-end date!

Sample File

An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.

Version History

¹  The required settings are shown below.  Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.

² You may change the name of the lambda and drop the prefix.  The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source.  The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.