Skip to content

Instantly share code, notes, and snippets.

@CHatmaker

CHatmaker/BXL 5g Functions LAMBDA Financial Models 2025

Last active April 6, 2025 07:37
Show Gist options
  • Save CHatmaker/472f65269519a0937b03f741e9271334 to your computer and use it in GitHub Desktop.
Save CHatmaker/472f65269519a0937b03f741e9271334 to your computer and use it in GitHub Desktop.
Download ZIP
BXL 5g Functions LAMBDA Financial Models 2025
Raw
BXL 5g Functions LAMBDA Financial Models 2025
/* FUNCTION NAME: Aboutλ
DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/* REVISIONS: Date Developer Description
Jul 13 2023 Craig Hatmaker Original Development
May 13 2024 Craig Hatmaker Spring 2024 Version
May 18 2024 Craig Hatmaker Added RollingAvgλ, RollingMinλ and RollingMaxλ
May 28 2024 Craig Hatmaker See SumContainsλ
May 29 2024 Craig Hatmaker See LabelAmortizeλ
Jun 19 2024 Craig Hatmaker See RollingX functions
Jul 17 2024 Craig Hatmaker IntOnIntλ fix
Aug 01 2024 Craig Hatmaker GrowthProjectionsλ
Jan 05 2025 Craig Hatmaker See Corkscrew
Feb 21 2025 Craig Hatmaker Add BXL: to VERSION:
Mar 13 2025 Craig Hatmaker Removed data validation routines
*/
Aboutλ = TRIM(TEXTSPLIT(
"ABOUT: →BXL's Financial Models module. Suggested module name: BXF¶" &
"VERSION: →BXL: Feb 21 2025¶" &
"GIST URL: →https://gist.github.com/CHatmaker/2a613bd6053b4010d12f7bf908c62b04 ¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
"→¶" &
"FUNCTION →DESCRIPTION¶" &
" Aboutλ →Produces this table¶" &
"→¶" &
" Cumulativeλ →Creates a row or column of cumulative totals from a total row or column¶" &
" GrowthProjectionsλ→Project growth or decline factors that can be applied to values to increase or decrease them. ¶" &
" IntOnIntλ →Calculate Interest on Interest. Use to determine amount needed to cover debt plus interest on debt¶" &
" IRRλ →Calculates IRR, correcting for when the first investment is not in the first period¶" &
" Movementλ →Create a row of differences from column to column¶" &
" Reversalλ →Create a row that reverses input values in the next period.¶" &
"CORKSCREW SUITE→¶" &
" Corkscrewλ →Creates a simple corkscrew where the closing balance is the sum of independent flows plus opening balance¶" &
" CorkscrewRxOλ →Corkscrew-Rate-Times-Open-Balance multiplies the opening balance by a rate and adds results to one or more flows.¶" &
" CorkScrewReversalλ→Create a corkscrew where the opening balance is reversed in the next period based on a timing row¶" &
"ROLLING SUITE→¶" &
" Rollingλ →Creates a rolling window over a row of values and sends window to Function¶" &
" RollingAvgλ →Finds the Average value in a window of values relative to the current column¶" &
" RollingMaxλ →Finds the Maximum value in a window of values relative to the current column¶" &
" RollingMinλ →Finds the Minimum value in a window of values relative to the current column¶" &
" RollingSumλ →Totals values in a window of values relative to the current column¶" &
"FILTER SUITE→¶" &
" FilterContainsλ →Filter an array by another array that contains specifice text¶" &
" BYCOLContainsλ →Applies a function to each column of a Values array filterd by a Labels array containing specified text¶" &
" SumContainsλ →Creates a row of totals for each row in an array where its labels contain a unique letter, word, or phrase.¶" &
" SumPeriodsλ →Groups and totals all columns in a Values array by period resulting in one column for each period.¶" &
"→¶" &
"AMORTIZATION SUITE→¶" &
" Amortizeλ →Creates a corkscrew amortization schedule.¶" &
" LabelAmortizeλ →Create row labels for Amortizeλ result¶" &
" SumAmortizeλ →Create row totals for payments, interest, and principal portion in Amortizeλ results¶" &
"→¶" &
"DEPRECIATION SUITE→¶" &
" Depreciateλ →Create a block of CAPEX, Opening Balance, Depreciation Values, and Book Value for each asset¶" &
" LabelDepreciateλ →Create row labels for Depreciateλ result¶" &
" SumDepreciateλ →Create row totals for CAPEX, Depreciation, Book Value, Salvage Value, and Disposal costs in Depreciateλ results¶" &
" Allocateλ →Divide one or more amounts equally equally across smaller periods¶" &
" SLNλ →Straight Line depreciation method for one asset or asset class.¶" &
" SYDλ →Sum-of-years' digits depreciation method for one asset or asset class.¶" &
" DBλ →Fixed declining balance depreciation method for one asset or asset class.¶" &
" DDBλ →Double-declining balance depreciation method for one asset or asset class.¶" &
" VDBλ →Variable declining balance depreciation method for one asset or asset class.¶" &
" DepreciateSLNλ →Create single straight line depreciation row calculation.¶" &
"→¶" &
"SUBROUTINES →Functions used by other functions or general purpose functions.¶" &
" DeriveTimelineλ →Create internal start and end timelines based on the model's timeline.¶" &
" FitInTimelineλ →Positions and adjusts an array to fit in a model's timeline.¶" &
" RangeToDAλ →Create a dynamic array from a static range¶" &
" TimelineOffsetλ →Determines how many columns a date is offset from a timeline's first date¶" &
" TimelinePositionλ →Places an array or value appropriately within a model's timeline.¶" &
" PeriodStartλ →Determine when a period containing a date of interest starts.¶" &
" PeriodDiffλ →Determine the number of periods between two dates.",
"→","¶"
)
);
/* FUNCTION NAME: Corkscrewλ
DESCRIPTION:*//**Create a corkscrew section that sums the opening balance and up to 4 independent flows.*/
/* REVISIONS: Date Developer Description
Mar 06 2023 Craig Hatmaker Original Development
Jun 13 2023 Craig Hatmaker Added Help
May 01 2924 Craig Hatmaker Modified Help
Jan 05 2025 Craig Hatmaker Simplified Calcs
Feb 21 2025 Craig Hatmaker Added PositiveOnly
*/
Corkscrewλ = LAMBDA(
// Parameter Declarations
[Opening],
[Flow1],
[Flow2],
[Flow3],
[Flow4],
[PositiveOnly],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Corkscrewλ( Opening, FLow1, [Flow2], [Flow3], [Flow4], [PositiveOnly])¶" &
"DESCRIPTION: →Create a corkscrew section that sums the opening balance and up to 4 independent flows¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Feb 21 2025¶" &
"PARAMETERS:→¶" &
" Opening →(Required) First periods opening balance.¶" &
" Flow1 →(Required) First row (or array of rows) of numbers to include in period totals.¶" &
" Flow2 →(Optional) Second row (or array of rows) of numbers to include in period totals.¶" &
" Flow3 →(Optional) Third row (or array of rows) of numbers to include in period totals.¶" &
" Flow4 →(Optional) Fourth row (or array of rows) of numbers to include in period totals.¶" &
" PositiveOnly →(Optional) TRUE = display flows as positive values. Default is FALSE.¶" &
"NOTE! →All flows are added. To subtract a flow make it negative. If it is desired that all¶" &
"→values should be displayed as positive values set PositiveOnly to TRUE.¶" &
"→¶" &
"EXAMPLES: →NOTE! BXF is assumed to be the module's name¶" &
" Formula: →=BXF.Corkscrewλ(0, Sequence(1,5))¶" &
" Result: →00, 01, 03, 06, 10¶" &
"→01, 02, 03, 04, 05¶" &
"→01, 03, 06, 10, 15",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Opening),
ISOMITTED( Flow1)
),
// Processing
Cols, Columns(Flow1),
Rows, IF(ISOMITTED(Flow2), 1,
IF(ISOMITTED(Flow3), 2,
IF(ISOMITTED(Flow4), 3, 4)
)
),
FlowArray, SWITCH(Rows,
1, Flow1,
2, VSTACK(Flow1, Flow2),
3, VSTACK(Flow1, Flow2, Flow3),
4, VSTACK(Flow1, Flow2, Flow3, Flow4)
),
PeriodArray, IF( PositiveOnly, ABS( FlowArray), FlowArray),
PeriodTotal, Flow1 + Flow2 + Flow3 + Flow4,
ClosingTotal, SCAN(Opening, PeriodTotal, LAMBDA(Opn, Prd, Opn + Prd)),
OpeningTotal, ClosingTotal - PeriodTotal,
Result, VSTACK(OpeningTotal, PeriodArray, ClosingTotal),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: CorkscrewRxOλ
DESCRIPTION:*//**Corkscrew-Rate-Times-Open-Balance multiplies the opening balance by a rate and adds results to one or more flows.*/
/* REVISIONS: Date Developer Description
Feb 21 2025 Craig Hatmaker Original Development
*/
CorkscrewRxOλ = LAMBDA(
// Parameter Declarations
[Opening],
[Rate],
[Flows],
[PositiveOnly],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →CorkscrewRxOλ( Opening, Rate, Flows, [PositiveOnly])¶" &
"DESCRIPTION: →Corkscrew-Rate-Times-Open-Balance multiplies the opening balance by a rate¶" &
"→and adds results to one or more flows. If more than one flow is desired, ¶" &
"→VSTACK them into Flows.¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Feb 21 2025¶" &
"PARAMETERS:→¶" &
" Opening →(Required) First periods opening balance.¶" &
" Rate →(Required) A percent or rate to multiple the opening balance by.¶" &
"→ If the result is to be subtracted from the opening balance make the rate negative.¶" &
" Flows →(Required) A row, or VSTACKed rows of values to add to opening and applied rate result.¶" &
"→ If a row in Flows is to be subtracted make the row negative.¶" &
" PositiveOnly →(Optional) TRUE = display flows as positive values. Default is FALSE.¶" &
"→¶" &
"EXAMPLES:→¶" &
" Formula: →CorkscrewPOBλ(0, -5%, CAPEX, TRUE)¶" &
"→",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Opening),
ISOMITTED( Rate),
ISOMITTED( Flows)
),
// Processing
Counter, SEQUENCE( COLUMNS( FLows)),
Result, REDUCE( Opening, Counter,
LAMBDA( Acc, n,
LET(
Open, TAKE( Acc, -1, -1),
Rated, Open * Rate,
Flow, CHOOSECOLS( Flows, n),
CLose, SUM( Open, Rated, Flow),
FlowRows, IF( PositiveOnly, ABS( Flow), Flow),
RatedRow, IF( PositiveOnly, ABS( Rated), Rated),
Stack, VSTACK(
Open,
RatedRow,
FlowRows,
Close),
Result, IF( n = 1, Stack, HSTACK( acc, Stack)),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: CorkScrewReversalλ
DESCRIPTION:*//**Create a corkscrew where the opening balance is reversed in the next period based on a timing parameter.*/
/* REVISIONS: Date Developer Description
Apr 19 2024 Craig Hatmaker Copyright - Thank you Finomatics for the suggestion
*/
CorkScrewReversalλ=LAMBDA(
// Parameter Declarations
[Opening],
[ReversalFlags],
[Flow1],
[Flow2],
[Flow3],
[Flow4],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Corkscrewλ( Opening, FLow1, [Flow2], [Flow3], [Flow4])¶" &
"DESCRIPTION: →Create a corkscrew where the opening balance is reversed in the next period based on a timing parameter¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Apr 19 2024¶" &
"PARAMETERS:→¶" &
" Opening →(Required) First periods opening balance.¶" &
" ReversalFlags →(Required) A row of ones and blanks (or zeros) indicating when to reverse period totals.¶" &
" Flow1 →(Required) First row (or array of rows) of numbers to include in period totals.¶" &
" Flow2 →(Optional) Second row (or array of rows) of numbers to include in period totals.¶" &
" Flow3 →(Optional) Third row (or array of rows) of numbers to include in period totals.¶" &
" Flow4 →(Optional) Fourth row (or array of rows) of numbers to include in period totals.¶" &
"NOTE! →All flows are added. To subtract a flow make it negative.",
"→", "¶")),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Opening),
ISOMITTED( ReversalFlags),
ISOMITTED( Flow1)),
// Set defaults and constants
Cols, COLUMNS(Flow1),
Rows, IF(ISOMITTED(Flow2), 1,
IF(ISOMITTED(Flow3), 2,
IF(ISOMITTED(Flow4), 3, 4)
)
),
// Procedure
PeriodArray, SWITCH(Rows,
1, Flow1,
2, VSTACK(Flow1, Flow2),
3, VSTACK(Flow1, Flow2, Flow3),
4, VSTACK(Flow1, Flow2, Flow3, Flow4)
),
PeriodTotal, BYCOL(PeriodArray, LAMBDA(Col, SUM(Col))),
Result, REDUCE( 0, SEQUENCE( COLUMNS( Flow1)),
LAMBDA( Acc, Col,
LET(
Opening, IF(Col=1, Opening, TAKE( Acc, -1, -1)),
Reversal, -INDEX( ReversalFlags, 1, Col) * Opening,
ArrayColumn, CHOOSECOLS( PeriodArray, Col),
Stack, VSTACK(
Opening,
REversal,
ArrayColumn,
SUM(Opening, Reversal, ArrayColumn)),
Result, IF(Col = 1, Stack, HSTACK( Acc, Stack)),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: GrowthProjectionsλ
DESCRIPTION:*//**Project growth or decline factors that can be applied to values to increase or decrease them.
Uses include applying inflation to amounts or to ramp up sales projections.
NOTE! GrowthPercents are not applied to Actuals. */
/* REVISIONS: Date Developer Description
Aug 01 2024 Craig Hatmaker Copyright
*/GrowthProjectionsλ = LAMBDA(
// Parameter Declarations
[Actuals],
[GrowthPercents],
[Timeline],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →GrowthProjectionsλ( Actuals, GrowthPercents, [Timeline])¶" &
"DESCRIPTION: →Projects percentage increases/(decreases) over the last Actual amount.¶" &
"→ Uses include applying inflation to amounts or to ramp up sales projections. ¶" &
"→ NOTE! GrowthPercents are not applied to Actuals.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Jul 25 2024¶" &
"PARAMETERS: →¶" &
"Actuals →(Required) A single number of an array of actuals placed under the timelne.¶" &
"→ NOTE! Increases or decreases are applied in the first projected period to the last Actual amount.¶" &
"GrowthPercents →(Required) A single number of an array of growth percentages placed under the timeline.¶" &
"→ NOTE! Positive percentages are increases, negative percentages are decreases.¶" &
"Timeline →(Optional) Include if either Actuals or GrowthPercents have fewer columns than the timeline¶" &
"EXAMPLES: →Formula (BXE is assumed to be the module's name)¶" &
" Formula: →=BXE.GrowthProjectionsλ( {100,200,300}, 5%, SEQUENCE(, 12))¶" &
" Results: → 100, 200, 300, 315, 330.75",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Actuals),
ISOMITTED( GrowthPercents)
),
// Set defaults
Cols, IF( ISOMITTED( Timeline), MAX( COLUMNS( Actuals), COLUMNS( GrowthPercents)), COLUMNS( Timeline)),
Actuals, IF( COLUMNS( Actuals) < Cols, EXPAND( Actuals, 1, Cols, 0), Actuals),
GrowthPercents, IF( COLUMNS( GrowthPercents) = 1, EXPAND( GrowthPercents, 1, Cols, GrowthPercents), GrowthPercents),
Result, Scan( 0, SEQUENCE( ,COLUMNS( GrowthPercents)),
LAMBDA( Acc, n,
LET(Actual, INDEX( Actuals, n),
Factor, INDEX( GrowthPercents, n),
Result, IF( Actual > 0 ,
Actual,
Acc * ( 1 + Factor)
),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: IntOnIntλ
DESCRIPTION:*/ /**Calculate Interest on Interest. Use to determine amount needed to cover debt plus interest on debt*/
/* NOTE: Math source from Diarmuid Early's post: https://www.youtube.com/watch?v=k5rG_MvIWWs&t=787s
REVISIONS: Date Developer Description
Dec 29 2023 Craig Hatmaker Copyright on LAMBDA
*/
IntOnIntλ = LAMBDA(
[Principal],
[Rate],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IntOnIntλ( Principal, Rate)¶" &
"DESCRIPTION: →Calculate Interest on Interest (Interest only). Use to determine amount ¶" &
"→ needed to cover borrowed interest¶" &
"WEBSITE: →<Coming soon>¶" &
"VERSION: →BXL: Dec 29 2023¶" &
"PARAMETERS: →¶" &
" Principal →(Required) Principal amount¶" &
" Rate →(Required) Interest percentage rate. For years, APR. For months, APR/12¶" &
"EXAMPLES: →¶" &
" Result →Formula (BXF is assumed to be the module's name)¶" &
" 52.28 →=BXF.IntOnIntλ( 1000, 5%)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Principal),
ISOMITTED( Rate)
),
// Procedure
EffectiveRate, Rate/(1-(Rate/2)),
Result, EffectiveRate * Principal ,
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: IRRλ
DESCRIPTION:*/ /**Calculates IRR, correcting for when the first investment is not in the first period */
/* REVISIONS: Date Developer Description
Oct 07 2023 Craig Hatmaker Initial development
*/
IRRλ = LAMBDA(
[Values],
[Dates],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IRRλ( Values, Dates)¶" &
"DESCRIPTION: →Calculates IRR, correcting for when the first investment is not in the first period¶" &
"WEBSITE: →<Coming soon>¶" &
"VERSION: →BXL: Dec 07 2023¶" &
"PARAMETERS: →¶" &
"Values →(Required) A row of investments (negative) and distributions (positive)¶" &
"→ The first entry, whether 0 or an amount, must be for the first period in the timeline¶" &
"→ and subsequent entries must correspond to periods in the timeline; however,¶" &
"→ the timeline may extend beyond the end of values.¶" &
"Dates →(Required) A timeline for when each investment is made¶" &
"→¶" &
"EXAMPLES: →¶" &
" Result →Formula (BXF is assumed to be the module's name)¶" &
" Dates →=EDATE(""1/1/23"", SEQUENCE(,24, 0))¶" &
" Values →={0,0,-150,0,-100,10,20,30,40,50,60,70}¶" &
"→¶" &
" Result →Formula¶" &
" 24% →=BXF.IRRλ( Values, Dates)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( Dates)
),
// Set Defaults
Values, EXPAND(VALUES, 1, COUNTA(Dates), 0),
// Procedure
FilterBy, Values <> 0,
FilteredValues, FILTER( Values, FilterBy),
FilteredDates, FILTER( Dates, FilterBy),
Result, XIRR( FilteredValues, FilteredDates),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Movementλ
DESCRIPTION:*//**Create a row of differences from column to column*/
/* REVISIONS: Date Developer Description
Jun 21 2023 Craig Hatmaker Copyright
Apr 16 2024 Craig Hatmaker Handles multiple rows
*/
Movementλ = LAMBDA(
// Parameter Declarations
[BeginningValues],
[Values],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Movementλ( [BeginningValue], Values)¶" &
"DESCRIPTION: →Create a row of differences from column to column¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Jun 21 2023¶" &
"PARAMETERS: →¶" &
"BeginningValues→(Optional) Defaults to 0¶" &
"Values →(Required) A row of values to compare column to column¶" &
"EXAMPLES: →¶" &
" Formula →=Movementλ(,{100,110,130,100}, 4)¶" &
" Result →100,10,20,-30",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Values),
// Set Defaults
BeginningValues, IF(OR(ISOMITTED(BeginningValues), BeginningValues=""), 0, BeginningValues),
// Procedure
Rows, ROWS( Values),
Cols, Columns( Values),
Seq, SEQUENCE(, Cols),
Result, REDUCE(0, SEQUENCE( Rows),
LAMBDA( Acc, R,
LET(
Begin, If( ROWS(BeginningValues) = 1, BeginningValues, INDEX( BeginningValues, R)),
Row, CHOOSEROWS( Values, R),
NewRow, SCAN( Begin, Seq,
LAMBDA( A, C,
INDEX( Row, C ) - IF( C = 1, A, INDEX( Row, C - 1))
)
),
Result, IF( R = 1, NewRow, VSTACK(Acc, NewRow)),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Reversalλ
DESCRIPTION:*//**Create a row that reverses input values in the next period.*/
/* REVISIONS: Date Developer Description
Mar 25 2024 Craig Hatmaker Copyright
*/
Reversalλ = LAMBDA(
// Parameter Declarations
[OpeningValue],
[RowToReverse],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Reversalλ( [OpeningValue], RowToReverse)¶" &
"DESCRIPTION: →Create a row that reverses input values in the next period.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Mar 25 2024¶" &
"PARAMETERS: →¶" &
" OpeningValue →(Optional) Defaults to 0¶" &
" RowToReverse →(Required) A row of values to reverse the following periods¶" &
"EXAMPLES: →¶" &
" Formula →=BXF.Reversalλ( , {100,110,130})¶" &
" Result →-100,-110,-130 ",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( RowToReverse),
// Set Defaults
BeginningValue, IF( ISOMITTED( OpeningValue), 0, OpeningValue),
// Procedure
Counter, SEQUENCE( , COLUMNS(RowToReverse)),
Result, MAP( Counter,
LAMBDA(Col, IF(Col = 1, -OpeningValue, -CHOOSECOLS( RowToReverse, Col-1)))
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Cumulativeλ
DESCRIPTION:*//**Creates a row or column of cumulative totals from a total row or column*/
/* REVISIONS: Date Developer Description
Jan 05 2024 Craig Hatmaker Original Development
*/
Cumulativeλ = LAMBDA(
// Parameter Declarations
[Totals],
[OpeningBalance],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Cumulativeλ( Totals, [OpeningBalance])¶" &
"DESCRIPTION: →Creates a row or column of cumulative totals from a total row or column¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Jan 05 2023¶" &
"PARAMETERS: →¶" &
" Totals →(Required) A row or column of totals from which to run cumulative totals¶" &
"OpeningBalance →(Optional) A starting balance. This defaults to 0¶" &
"EXAMPLES: →¶" &
" Formula →=BXF.Cumulativeλ(Sequence(,3))¶" &
" Result →1,3,6",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Totals),
// Procedure
Result, SCAN( OpeningBalance, Totals,
LAMBDA( PreviousAmount, CurrentAmount,
PreviousAmount + CurrentAmount)),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Rollingλ
DESCRIPTION:*//**Creates a rolling window over a row of values and sends window to Function*/
/* REVISIONS: Date Developer Description
Dec 01 2024 Craig Hatmaker Initial development and Copyright
*/
Rollingλ=LAMBDA(
[Values],
[WindowStart],
[WindowSize],
[Function],
[AllowPartial],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Rollingλ( Values, WindowStart, WindowSize, [Function], [AllowPartial]) ¶" &
"DESCRIPTION: →Creates a rolling window over a row of values and sends window to Function¶" &
"→sends window to Function.¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Dec 01 2024¶" &
"PARAMETERS:→¶" &
" Values →(Required) A row of values to roll the window over¶" &
" WindowStart →(Required) Number of values (cells) to position the window¶" &
"→ Must be a whole number. Negative numbers are left of current cell.¶" &
"→ Positive numbers are right of current value.¶" &
" WindowSize →(Required) Number of values (cells) to include in window. ¶" &
"→ Must be a whole positive number.¶" &
" Function →(Optional) Function to apply to the values in the Window.¶" &
"→ SUM is the default. LAMBDAs are allowed where Window is the only argument¶" &
" AllowPartial →(Optional) Show results when only part of the window is over¶" &
"→ Values array. Default = FALSE.¶" &
"EXAMPLE:→¶" &
" Formula →Rollingλ(SEQUENCE(, 7), -2, 3)¶" &
" Result →0, 0, 6, 9, 12, 15, 18 ¶" &
"→",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( WindowStart),
ISOMITTED( WindowSize)
),
// Set optional argument defaults
Function, IF( ISOMITTED( Function), SUM, Function),
// Setup a counter for MAP() to process all values
Count, COUNTA( Values),
Counter, SEQUENCE( , Count),
// Use MAP() to step through all values
Result, MAP( Counter,
LAMBDA( n,
LET(
Right, n -1 + WindowStart + WindowSize,
Left, Right - WindowSize + 1,
Outside, OR( Right < 1, Left > Count),
Partial, OR( Left < 1, Right > Count),
Result, IF( OR( Outside, AND( NOT(AllowPartial), Partial)),
0,
LET(
// Get values left of, and including n
FirstTake, TAKE( Values, , Right),
// Get 'WindowSize' values from right
Window, TAKE( FirstTake, , -WindowSize),
// Process Window
Processed, Function( Window),
// Return LET Step Processed
Processed
)
),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: RollingAvgλ
DESCRIPTION:*//**Creates averages for a window of columns that moves relative to the current column.*/
/* REVISIONS: Date Developer Description
Jan 30 2024 Craig Hatmaker Copyright
Jun 19 2024 Craig Hatmaker Added ability to include values to the right of current column
Feb 22 2025 Craig Hatmaker Changed to leverage Rollingλ
*/
RollingAvgλ = LAMBDA(
// Parameter Declarations
[Values],
[WindowStart],
[WindowSize],
[AllowPartial],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →RollingAvgλ( Values, WindowStart, WindowSize, [AllowPartial])¶" &
"DESCRIPTION: →Finds the average value in a window rolling over values.¶" &
"→NOTE! Same as Rollingλ(Values, WindowStart, WindowSize, AVERAGE, [AllowPartial])¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Feb 22 2025¶" &
"PARAMETERS: →¶" &
" Values →(Required) A row of values to roll the window over¶" &
" WindowStart →(Required) Number of values (cells) to position the window¶" &
"→ Must be a whole number. Negative numbers are left of current cell.¶" &
"→ Positive numbers are right of current value.¶" &
" WindowSize →(Required) Number of values (cells) to include in window. ¶" &
"→ Must be a whole positive number.¶" &
" AllowPartial →(Optional) Show results when only part of the window is over¶" &
"→  Values array. Default = FALSE.¶" &
"EXAMPLES: →¶" &
" Formula →=RollingAvgλ(SEQUENCE(, 5), -2, 3, TRUE)¶" &
" Result →1,1.5,2,3,4",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( WindowStart),
ISOMITTED( WindowSize)
),
// Procedure - 'Roll' the window over the values and process
Result, Rollingλ( Values, WindowStart, WindowSize, AVERAGE, AllowPartial),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: RollingMaxλ
DESCRIPTION:*//**Creates maximums for a window of columns that moves relative to the current column.*/
/* REVISIONS: Date Developer Description
Jan 30 2024 Craig Hatmaker Copyright
Jun 19 2024 Craig Hatmaker Added ability to include values to the right of current column
Feb 22 2025 Craig Hatmaker Changed to leverage Rollingλ
*/
RollingMaxλ = LAMBDA(
// Parameter Declarations
[Values],
[WindowStart],
[WindowSize],
[AllowPartial],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →RollingMaxλ( Values, WindowStart, WindowSize, [AllowPartial])¶" &
"DESCRIPTION: →Finds the maximum value in a window rolling over values.¶" &
"→NOTE! Same as Rollingλ(Values, WindowStart, WindowSize, MAX, [AllowPartial])¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Feb 22 2025¶" &
"PARAMETERS: →¶" &
" Values →(Required) A row of values to roll the window over¶" &
" WindowStart →(Required) Number of values (cells) to position the window¶" &
"→ Must be a whole number. Negative numbers are left of current cell.¶" &
"→ Positive numbers are right of current value.¶" &
" WindowSize →(Required) Number of values (cells) to include in window. ¶" &
"→ Must be a whole positive number.¶" &
" AllowPartial →(Optional) Show results when only part of the window is over¶" &
"→  Values array. Default = FALSE.¶" &
"EXAMPLES: →¶" &
" Formula →=RollingMaxλ(SEQUENCE(, 5), -2, 3, TRUE)¶" &
" Result →1,2,3,4,5",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( WindowStart),
ISOMITTED( WindowSize)
),
// Procedure - 'Roll' the window over the values and process
Result, Rollingλ( Values, WindowStart, WindowSize, MAX, AllowPartial),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: RollingMinλ
DESCRIPTION:*//**Finds the minimum value in a window rolling over values.*/
/* REVISIONS: Date Developer Description
Jan 30 2024 Craig Hatmaker Copyright
Jun 19 2024 Craig Hatmaker Added ability to include values to the right of current column
Feb 22 2025 Craig Hatmaker Changed to leverage Rollingλ
*/
RollingMinλ = LAMBDA(
// Parameter Declarations
[Values],
[WindowStart],
[WindowSize],
[AllowPartial],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →RollingMinλ( Values, WindowStart, WindowSize, [AllowPartial])¶" &
"DESCRIPTION: →Finds the minimum value in a window rolling over values.¶" &
"→NOTE! Same as Rollingλ(Values, WindowStart, WindowSize, MIN, [AllowPartial])¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Feb 22 2025¶" &
"PARAMETERS: →¶" &
" Values →(Required) A row of values to roll the window over¶" &
" WindowStart →(Required) Number of values (cells) to position the window¶" &
"→ Must be a whole number. Negative numbers are left of current cell.¶" &
"→ Positive numbers are right of current value.¶" &
" WindowSize →(Required) Number of values (cells) to include in window. ¶" &
"→ Must be a whole positive number.¶" &
" AllowPartial →(Optional) Show results when only part of the window is over¶" &
"→  Values array. Default = FALSE.¶" &
"EXAMPLES: →¶" &
" Formula →=RollingMinλ(SEQUENCE(, 5), -2, 3, TRUE)¶" &
" Result →1,1,1,2,3",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( WindowStart),
ISOMITTED( WindowSize)
),
// Procedure - 'Roll' the window over the values and process
Result, Rollingλ( Values, WindowStart, WindowSize, MIN, AllowPartial),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: RollingSumλ
DESCRIPTION:*//**Creates totals for a window of columns that moves relative to the current column.*/
/* REVISIONS: Date Developer Description
Jan 30 2024 Craig Hatmaker Copyright
May 02 2024 Craig Hatmaker Changed COUNTA(Values) to COLUMNS( Values)
Jun 19 2024 Craig Hatmaker Added ability to include values to the right of current column
Feb 22 2025 Craig Hatmaker Changed to leverage Rollingλ
*/
RollingSumλ = LAMBDA(
// Parameter Declarations
[Values],
[WindowStart],
[WindowSize],
[AllowPartial],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →RollingSumλ( Values, WindowStart, WindowSize, [AllowPartial])¶" &
"DESCRIPTION: →Creates totals for a window of columns that moves relative to the current column.¶" &
"→NOTE! Same as Rollingλ(Values, WindowStart, WindowSize, SUM, [AllowPartial])¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Feb 22 2025¶" &
"PARAMETERS: →¶" &
" Values →(Required) A row of values to roll the window over¶" &
" WindowStart →(Required) Number of values (cells) to position the window¶" &
"→ Must be a whole number. Negative numbers are left of current cell.¶" &
"→ Positive numbers are right of current value.¶" &
" WindowSize →(Required) Number of values (cells) to include in window. ¶" &
"→ Must be a whole positive number.¶" &
" AllowPartial →(Optional) Show results when only part of the window is over¶" &
"→  Values array. Default = FALSE.¶" &
"EXAMPLES: →¶" &
" Formula →=RollingSumλ(SEQUENCE(, 5), -2, 3, TRUE)¶" &
" Result →1,3,6,9,12",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( WindowStart),
ISOMITTED( WindowSize)
),
// Procedure - 'Roll' the window over the values and process
Result, Rollingλ( Values, WindowStart, WindowSize, SUM, AllowPartial),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: →FilterContainsλ
DESCRIPTION:*//**→Filters a Values array by a Labels array containing specified text*/
/* REVISIONS: Date Developer Description
Feb 22 2024 Craig Hatmaker Initial development and copyright
*/
FilterContainsλ = LAMBDA(
// Parameter Declarations
[ValuesToFilter],
[ValueLabels],
[FilterByText],
[IgnoreCase?],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →FilterContainsλ( ValuesToFilter, ValueLabels, FilterByText, [IgnoreCase?])¶" &
"DESCRIPTION: →Filters a Values array filtered by a Labels array containing specified text¶" &
"WEBPAGE: →<Coming soon>¶" &
"VERSION: →BXL: Feb 22 2024¶" &
"PARAMETERS: →¶" &
" ValuesToFilter→(Required) An array of values to be filtered¶" &
" ValueLabels →(Required) An array containing the values' labels¶" &
" FilterByText →(Required) A unique letter, word or phrase to find contained¶" &
"→in ValueLabels¶" &
" IgnoreCase? →(Optional) A flag to determine if Text's case is important.¶" &
"→Default is TRUE¶" &
"EXAMPLES: →¶" &
" Formula →FilterContainsλ( SEQUENCE(5,6),{""A"";""B"";""A"";""C"";""A""},""A"")¶" &
" Result → 1, 2, 3, 4, 5, 6¶" &
"→13,14,15,16,17,18 ¶" &
"→25,26,27,28,29,30",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( ValuesToFilter),
ISOMITTED( ValueLabels),
ISOMITTED( FilterByText)),
// Check inputs - Set defaults
IgnoreCase?, IF( ISLOGICAL( IgnoreCase?), IgnoreCase?, TRUE),
Labels, CHOOSECOLS(ValueLabels, 1),
// Procedure
Matches, IF( IgnoreCase?,
SEARCH( FilterByText, Labels),
FIND( FilterByText, Labels)
),
Result, FILTER( ValuesToFilter, ISNUMBER( Matches)),
// Return Result or help
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: →BYCOLContainsλ
DESCRIPTION:*//**→Applies a function to each column of a Values array filterd by a Labels array containing specified text*/
/* REVISIONS: Date Developer Description
Feb 22 2024 Craig Hatmaker Initial development and copyright
*/
BYCOLContainsλ = LAMBDA(
// Parameter Declarations
[ValuesToFilter],
[ValueLabels],
[FilterByText],
[Function],
[IgnoreCase?],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →FilterContainsλ( ValuesToFilter, ValueLabels, FilterByText,¶" &
"→ [Function], [IgnoreCase?])¶" &
"DESCRIPTION: →Applies a function by column to a Values array filtered by a ¶" &
"→Labels array containing specified text¶" &
"WEBPAGE: →<Coming soon>¶" &
"VERSION: →BXL: Feb 22 2024¶" &
"PARAMETERS: →¶" &
" ValuesToFilter→(Required) An array of values to be filtered¶" &
" ValueLabels →(Required) An array containing the values' labels¶" &
" FilterByText →(Required) A unique letter, word or phrase to find contained¶" &
"→in ValueLabels¶" &
" Function →(Optiona) An Excel function or a LAMBDA function to process¶" &
"→a column of values. Default = SUM.¶" &
" IgnoreCase? →(Optional) A flag to determine if Text's case is important.¶" &
"→Default is TRUE¶" &
"EXAMPLES: →¶" &
" Formula →FilterContainsλ( SEQUENCE(5,6),{""A"";""B"";""A"";""C"";""A""},""A"")¶" &
" Result → 1, 2, 3, 4, 5, 6¶" &
"→13,14,15,16,17,18 ¶" &
"→25,26,27,28,29,30",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( ValuesToFilter),
ISOMITTED( ValueLabels),
ISOMITTED( FilterByText)),
// Check inputs - Set defaults
IgnoreCase?, IF( ISLOGICAL( IgnoreCase?), IgnoreCase?, TRUE),
Function, IF( ISOMITTED( Function), SUM, Function),
// Procedure
Filtered, FilterContainsλ( ValuesToFilter, ValueLabels, FilterByText, IgnoreCase?),
Result, BYCOL( Filtered, Function),
// Return Result or help
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: →SumContainsλ
DESCRIPTION:*//**→Creates a row of totals for each row in an array where
its labels contain a letter, word, or phrase*/
/* REVISIONS: Date Developer Description
Jan 19 2024 Craig Hatmaker Copyright
May 28 2024 Craig Hatmaker Added: Labels, CHOOSECOLS(ValueLabels, 1),
Feb 22 2025 Craig Hatmaker Changed to leverage BYCOLContainsλ
*/
SumContainsλ = LAMBDA(
// Parameter Declarations
[ValuesToFilter],
[ValueLabels],
[FilterByText],
[IgnoreCase?],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →SumContainsλ(ValuesToFilter, ValueLabels, FilterByText, [IgnoreCase?])¶" &
"DESCRIPTION: →Creates a row of column totals for each row in an array where¶" &
"→its labels contain a unique letter, word, or phrase.¶" &
"WEBPAGE: →<Coming soon>¶" &
"VERSION: →BXL: Feb 22 2025¶" &
"PARAMETERS: →¶" &
" ValuesToFilter→(Required) An array of values to be filtered¶" &
" ValueLabels →(Required) An array containing the values' labels¶" &
" FilterByText →(Required) A unique letter, word or phrase to find contained in ValueLabels¶" &
" IgnoreCase? →(Optional) A flag to determine if Text's case is important. Default is TRUE¶" &
"EXAMPLES: →¶" &
" Formula →SumContainsλ(SEQUENCE( 3, 4),{""A"";""B"";""A""},""A"")¶" &
" Result →10, 12, 14, 16",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( ValuesToFilter),
ISOMITTED( ValueLabels),
ISOMITTED( FilterByText)),
// Check inputs - Set defaults
IgnoreCase?, IF( ISLOGICAL( IgnoreCase?), IgnoreCase?, TRUE),
Labels, CHOOSECOLS(ValueLabels, 1),
// Procedure
Result, BYCOLContainsλ( ValuesToFilter, ValueLabels, FilterByText, SUM, IgnoreCase?),
// Return Result or help
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: SumPeriodsλ
DESCRIPTION:*//**Groups and totals all columns in a Values array by period resulting in one column for each period*/
/* REVISIONS: Date Developer Description
Mar 04 2022 Craig Hatmaker Original Development
Apr 10 2023 Craig Hatmaker Remove SUMIFS to be compatible with arrays. Added Help.
Feb 22 2025 Craig Hatmaker Leveraged DeriveTimelinesλ
*/
SumPeriodsλ = LAMBDA(
// Parameter Declarations
[Values],
[ValuesTimeline],
[ModelTimeline],
[EndDates?],
// Procedure
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →SumPeriodsλ( Values, ValuesTimeline, ModelTimeline, [EndDates?])¶" &
"DESCRIPTION: →Summarizes values where each value column is for one day ¶" &
"→into one column for each period. ¶" &
"VERSION: →BXL: Apr 10 2023¶" &
"WEBPAGE: →<coming soon>¶" &
"PARAMETERS:→¶" &
" Values →(Required) A two dimensional array/range containing values to be summed where" &
"each column is for a specific day¶" &
" ValuesTimeline→(Required) The Values' timeline¶" &
" ModelTimeline →(Required) The model's timeline" &
" EndDates? →(Optional) TRUE=The model's timeline displays period end dates. Default = TRUE." &
"EXAMPLES: →¶" &
" Formula →=BXF.SumPeriodsλ({1,2,3,4;4,3,2,1;10,20,30,40}, {1,2,3,4}, {1,3})¶" &
" Result →03, 07 →¶" &
"→07, 03¶" &
"→30, 70→",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( ValuesTimeline),
ISOMITTED( ModelTimeline)
),
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
// Procedure
Timelines, DeriveTimelinesλ( ModelTimeline, EndDates?),
Starts, CHOOSEROWS( Timelines, 1),
Ends, CHOOSEROWS( Timelines, 2),
MatchStarts, IFNA( XMATCH( ValuesTimeline, Starts, -1 ), 0),
MatchEnds, IFNA( XMATCH( ValuesTimeline, Ends, 1, -1 ), 0),
InRange, N( NOT( BYCOL( VSTACK( MatchStarts, MatchEnds), XOR))),
PeriodNumber, InRange * MatchStarts,
Result, MAKEARRAY(
ROWS( Values),
COLUMNS( ModelTimeline),
LAMBDA( Row, Col,
SUM( INDEX( Values, Row, ) * ( Col = PeriodNumber))
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
// Amortization Suite
/* FUNCTION NAME: Amortizeλ
DESCRIPTION:*//**Creates a corkscrew amortization schedule*/
/* REVISIONS: Date Developer Description
Jul 13 2023 Craig Hatmaker Original Development
Dec 20 2023 Craig Hatmaker Payments are assumed to be paid monthly
Jan 18 2024 Craig Hatmaker Added Debt Issuance
May 02 2024 Craig Hatmaker Converted to anonymous recursion
May 11 2024 Craig Hatmaker Corrected issue with month to year aggregation
*/
Amortizeλ = LAMBDA(
// Parameter Declarations
[Principals],
[APRs],
[Terms],
[StartDates],
[Timeline],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Amortizeλ( Principals, APRs, Terms, StartDates, [Timeline])¶" &
"DESCRIPTION: →Creates an amortization schedule array for one or more loans.¶" &
"→It assumes all payments are made monthly.¶" &
"→Use LableAmortizeλ() to apply labels to this function's results:¶" &
"→Use SumAmortizeλ() to apply totals to this function's results:¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-amortize%CE%BB¶" &
"VERSION: →BXL: May 11 2024¶" &
"PARAMETERS:→¶" &
"Principals →(Required) Loan/debt amounts.¶" &
"APRs →(Required) Annual Percentage Rates for interest.¶" &
"Terms →(Required) Number of months for loans/debts.¶" &
"StartDates →(Required) Dates debts are issued.¶" &
"Timeline →(Optional) An array of dates. If omitted, a row of months starting on start date will be added¶" &
"EXAMPLES:→¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.Amortizeλ(10000, 5%, 4 * 12, ""2023-01-01"")",
"→", "¶" )
),
// Constants
MpY, 12, //Months Per Year
ADpM, 30.42, //Average Days Per Month
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Principals),
ISOMITTED( APRs),
ISOMITTED( Terms),
ISOMITTED( StartDates)
),
// Set defaults and apply convenience conversions
Timeline, IF( ISOMITTED( Timeline),
LET(Counter, ROWS( Principals),
MaxDate, MAX(MAP( Counter,
LAMBDA( n,
EDATE( INDEX( StartDates, n), INDEX( Terms, n) - 1)))),
MinDate, MIN( StartDates),
MaxTerm, DATEDIF(MinDate, MaxDate, "M") + 1,
Result, EDATE( MinDate, Sequence(1, MaxTerm, 0)),
Result),
Timeline
),
// Determine timeline's interval (length of periods) and orientation (horizontal or vertical)
FirstPeriod, INDEX( Timeline, 1),
SecondPeriod, INDEX( TImeline, 2),
MpP, @ROUND(( SecondPeriod - FirstPeriod) / ADpM, 0),
PpY, MpY / MpP,
// Recursive function (fn) definition
Recursion,
LAMBDA( fn, Principals, APRs, Terms, StartDates, Timeline, Asset,
LET(
// Create individual Values from array
Principal, INDEX( Principals, Asset),
APR, INDEX( APRs, Asset),
Term, INDEX( Terms, Asset),
StartDate, INDEX( StartDates, Asset),
// Adjust inputs to match timeline
Periods, ROUNDUP(Term / MpP, 0),
Rows, 1,
Cols, Periods,
// Calculate for each month
Rate, APR / MpY,
MonthlyPayment, PMT( Rate, Term, Principal),
MonthlyPrincipal, -FV(Rate, SEQUENCE( 1, Term, 0), MonthlyPayment, Principal),
MonthlyInterest, MonthlyPrincipal * Rate,
// Convert to Timeline Periods
DebtIssueArray, EXPAND(Principal, 1, Periods, 0),
PrincipalArray, IF( MpP = 1,
MonthlyPrincipal,
MAKEARRAY( 1, Periods,
LAMBDA(R, C,
LET(Idx, (C - 1) * MpP + 1,
INDEX( MonthlyPrincipal, Idx)
)
)
)
),
InterestArray, IF( MpP = 1,
MonthlyInterest,
LET(
MoInt, EXPAND( MonthlyInterest, 1, ROUNDUP( Term/ Mpp, 0) * Mpp, 0),
MAKEARRAY( 1, Periods,
LAMBDA(R, C,
LET(Idx, (C - 1) * MpP + 1,
SUM( CHOOSECOLS(MoInt, SEQUENCE(1, MpP, Idx)))
)
)
)
)
),
PaymentArray, LET(MoPmt, EXPAND( SEQUENCE(1, Term, MonthlyPayment, 0), 1, ROUNDUP( Term/ Mpp, 0) * Mpp, 0),
MAKEARRAY( 1, Periods,
LAMBDA(R, C,
LET(Idx, (C - 1) * MpP + 1,
SUM( CHOOSECOLS(MoPmt, SEQUENCE(1, MpP, Idx)))
)
)
)
),
ClosingArray, PrincipalArray + InterestArray + PaymentArray,
PortionArray, -PaymentArray - InterestArray,
AmortizationSched, VSTACK(
DebtIssueArray,
PrincipalArray,
InterestArray,
PaymentArray,
ClosingArray,
PortionArray),
// Position in timeline
Offset, TimelineOffsetλ(StartDate, Timeline),
NewBlock, TimelinePositionλ(AmortizationSched, Timeline, Offset),
Result, IF( Asset = ROWS( Principals),
NewBlock,
VSTACK(
NewBlock,
EXPAND({" "}, 1, COUNTA(Timeline), " "),
fn( fn, Principals, APRs, Terms, StartDates, Timeline, Asset + 1)
)
),
Result
)
),
Result, Recursion( Recursion, Principals, APRs, Terms, StartDates, Timeline, 1),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: LabelAmortizeλ
DESCRIPTION:*//**Create labels for Amortizeλ*/
/* REVISIONS: Date Developer Description
Dec 19 2023 Craig Hatmaker Original Development
Jan 18 2024 Craig Hatmaker Added Debt Issuance
May 29 2024 Craig Hatmaker Added optional parameters
*/
LabelAmortizeλ = LAMBDA(
// Parameter Declarations
[LoanNames],
[LoanAmounts],
[LoanAPRs],
[LoanTerms],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →LabelAmortizeλ([LoanNames])¶" &
"DESCRIPTION: →Create row labels for the amortization schedule array produced by Amortizeλ¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-amortize%CE%BB¶" &
"VERSION: →BXL: May 29 2024¶" &
"PARAMETERS: →¶" &
"LoanNames →(Required) Names or descriptions of each loan in the amortization schedule produced by Amortizeλ¶" &
"LoanAmounts →(Optional) Amount borrowed¶" &
"LoanAPR →(Optional) Annual Percentage Interest Rate¶" &
"LoanTerm →(Optional) Number of months to repay¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.LabelAmortizeλ(tblLoans[Loan Name])",
"→", "¶"
)
),
// Check required inputs - Omitted required arguments
Help?, ISOMITTED( LoanNames),
// Check optional inputs
Optionals, NOT( OR(
ISOMITTED( LoanAmounts),
ISOMITTED( LoanAPRs),
ISOMITTED( LoanTerms)
)),
// Procedure
Result, REDUCE( "", SEQUENCE( ROWS( LoanNames) * 7 - 1),
LAMBDA(Acc, Row,
LET(Idx, QUOTIENT( Row - 1, 7) + 1,
Line, MOD( Row - 1, 7) + 1,
LoanName, INDEX( LoanNames, Idx),
LoanAmount, IF(ISOMITTED( LoanAmounts), 0, INDEX( LoanAmounts, Idx)),
LoanAPR, IF(ISOMITTED( LoanAPRs), 0, INDEX( LoanAPRs, Idx)),
LoanTerm, IF(ISOMITTED( LoanTerms), 0, INDEX( LoanTerms, Idx)),
Description, SWITCH( Line,
1, HSTACK(LoanName & " Debt Issuance", LoanAmount, ""),
2, HSTACK(LoanName & " Debt Balance", "",""),
3, HSTACK(LoanName & " Interest", Text(LoanAPR, "0.00%"), "APR"),
4, HSTACK(LoanName & " Debt Services", LoanTerm, "Months"),
5, HSTACK(LoanName & " Closing Balance","",""),
6, HSTACK(LoanName & " Debt Repayment", "", ""),
7, HSTACK("","","")
),
Values, IF( Optionals, Description, TAKE( Description, , 1)),
Result, IF( Row = 1, Values, VSTACK(Acc, Values)),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: SumAmortizeλ
DESCRIPTION:*//**Create row totals for Amortizeλ*/
/* REVISIONS: Date Developer Description
Dec 19 2023 Craig Hatmaker Original Development
Jan 18 2024 Craig Hatmaker Added Debt Issuance
*/
SumAmortizeλ = LAMBDA(
// Parameter Declarations
[AmoritizationSchedule],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →SumAmortizeλ(AmoritizationSchedule)¶" &
"DESCRIPTION: →Create row totals for Amortizeλ¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-amortize%CE%BB¶" &
"VERSION: →BXL: Dec 19 2023¶" &
"PARAMETERS: →¶" &
"AmoritizationSchedule→(Required) An array produced by Amoritizeλ¶" &
"EXAMPLES:→¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.SumAmoritizeλ(AmoritizationSchedule)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( AmoritizationSchedule),
// Procedure
Result, MAKEARRAY(ROWS(AmoritizationSchedule), 1,
LAMBDA( R, C,
SWITCH( MOD( R, 7),
3, SUM(CHOOSEROWS( AmoritizationSchedule, R)),
4, SUM(CHOOSEROWS( AmoritizationSchedule, R)),
6, SUM(CHOOSEROWS( AmoritizationSchedule, R)),
0
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
// Depreciation Suite
/* FUNCTION NAME: Depreciateλ
DESCRIPTION:*//**Create a block of CAPEX, Opening Balance, Depreciation Values, and Book Value for each asset*/
/* REVISIONS: Date Developer Description
Oct 24 2023 Craig Hatmaker Original Development
May 13 2024 Craig Hatmaker Spring Version
*/
Depreciateλ = LAMBDA(
// Parameter Declarations
[InitialValues],
[InServiceDates],
[LifeInYears],
[Timeline],
[SalvageValues],
[DisposalDates],
[DisposalCosts],
[Methods],
[Factors],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Depreciateλ(InitialValues, InServiceDates, LifeInYears, Timeline, ¶" &
" →[SalvageValues], [DisposalDates], [DisposalCosts], [Methods], [Factor])¶" &
"DESCRIPTION: →Creates an array of CAPEX, depreciation, and book values for a model's timeline¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-depreciate%CE%BB ¶" &
"VERSION: →BXL: Apr 10 2024¶" &
"NOTES: →The methods used here differs from Excel's functions in several ways. Please see webpage for details.¶" &
"PARAMETERS: →¶" &
"InitialValues →(Required) The purchase values of each asset to depreciate¶" &
"InServiceDates →(Required) The dates from which to start depreciating each asset¶" &
"LifeInYears →(Required) The number of years with which to depreciate each asset¶" &
"Timeline →(Required) The model's timeline or a row of period start dates. ¶" &
" →Timeline can be in Months, Quarters, or Years¶" &
"SalvageValues →(Optional: Default = 0) The book values of each asset after fully depreciated.¶" &
"DisposalDates →(Optional: Default = End of life) The dates on which the assets are removed from the books.¶" &
"DisposalCosts →(Optional: Default = 0) The book values of each asset after fully depreciated.¶" &
"Methods →(Optional: Default = SLN) The desired depreciation method. Must be one of these Excel function names: ¶" &
"→SLN=Straight Line Depreciation¶" &
"→SYD=Sum of Year's Digits¶" &
"→DB =Declining Balance¶" &
"→DDB=Double Declining Balance¶" &
"→VDB=Variable Declining Balance¶" &
"→MACRS=Modified Accelerated Cost Recovery System. NOTE: Salvage value ignored¶" &
"Factors →(Optional: Default = 2) Only applicable to DDB and VDB methods.",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( InitialValues),
ISOMITTED( LifeInYears),
ISOMITTED( InServiceDates),
ISOMITTED( Timeline)
),
// Set Constants
MethodArray, {"SLN","SYD","DB","DDB","VDB", "MACRS"},
Mpy, 12, //Months Per Year
// Set defaults and apply convenience conversions
SalvageValues, IF( ISOMITTED( SalvageValues),
MAP( InitialValues, LAMBDA( Ele, 0)),
MAP( SalvageValues, LAMBDA(v, IFERROR( VALUE( v), 0)))),
CvtSrvDates, IF( NOT( ISOMITTED( InServiceDates)),
IF( ISNUMBER( InServiceDates),
InServiceDates,
DATEVALUE( InServiceDates))),
DisposalDates, IF( ISOMITTED( DisposalDates),
EDATE( +CvtSrvDates, LifeinYears * 12),
MAKEARRAY( ROWS( DisposalDates), 1,
LAMBDA(R, C,
IF( INDEX( DisposalDates, R) = "",
EDATE( INDEX( CvtSrvDates, R), INDEX(LifeinYears, R) * 12),
INDEX( DisposalDates, R)
)
)
)
),
DisposalCosts, IF( ISOMITTED( DisposalCosts),
MAP( InitialValues, LAMBDA( Ele, 0)),
DisposalCosts),
Methods, IF( ISOMITTED( Methods),
MAP( InitialValues, LAMBDA( Ele, "SLN")),
MAP( Methods, LAMBDA(v, IF( TRIM( v) = "", "SLN", v)))),
Factors, IF( ISOMITTED( Factors),
MAP( InitialValues, LAMBDA( Ele, 2)),
MAP( Factors, LAMBDA(v, IF( TRIM( v) = "", 2, v)))),
// Create individual Values from timeline array
TimelineCols, COLUMNS( TImeline),
FirstPeriod, INDEX( Timeline, 1),
SecondPeriod, INDEX( TImeline, 2),
MpP, @ROUND(( SecondPeriod - FirstPeriod) / 30.5, 0), //Months Per Period
Interval, SWITCH( MpP, 1, "M", 3, "Q", 12, "Y"),
PpY, SWITCH( MpP, 1, 12, 3, 4, 12, 1), //Periods Per Year
EndDates, HSTACK( MAP( SEQUENCE(, TimelineCols - 1), LAMBDA( n, INDEX( Timeline, n + 1) - 1)), EDATE( MAX( Timeline), MpP) -1 ),
// Process each asset
Result, REDUCE( 0, SEQUENCE( ROWS( InitialValues)),
LAMBDA(Block, Asset,
LET(
// Create individual values from arguments
Method, @INDEX( Methods, Asset),
Years, @INDEX( LifeInYears, Asset) + N(Method = "MACRS"),
InitialValue, @INDEX( InitialValues, Asset),
SalvageValue, IF( Method = "MACRS", 0, @INDEX( SalvageValues, Asset)),
InServiceDate, @INDEX( CvtSrvDates, Asset),
DisposalDate, IF( Method = "MACRS",
MAX(EDATE( InserviceDate, Years * MpY), @INDEX( DisposalDates, Asset)),
@INDEX( DisposalDates, Asset)),
DisposalCost, @INDEX( DisposalCosts, Asset),
Factor, @INDEX( Factors, Asset),
LifeInPeriods, Years * PpY,
LifeInMonths, PeriodDiffλ( InserviceDate, DisposalDate, "M"),
LastPeriod, PeriodDiffλ( InserviceDate, DisposalDate, Interval),
// Procedure
// Depreciation is calculated annually then allocated equally over the next 12 months
Depreciate, CHOOSE( MATCH( @Method, MethodArray, 0), //CHOOSE is more efficient than SWITCH
// 1. Straight line
SLNλ( InitialValue, SalvageValue, Years),
// 2. Sum-of-years' digits
SYDλ( InitialValue, SalvageValue, Years),
// 3. Declining balance
DBλ( InitialValue, SalvageValue, Years),
// 4. Double declining balance
DDBλ( InitialValue, SalvageValue, Years, Factor),
// 5. Variable declining balance
VDBλ( InitialValue, SalvageValue, Years, Factor),
// 6. Modified accelerated cost recovery system
MACRSλ( InitialValue, Years - 1),
),
// Place Disposal and Salvage in the asset's last month
Disposal, HSTACK( SEQUENCE( 1, LifeInMonths - 1, 0, 0) , DisposalCost),
Salvage, HSTACK( SEQUENCE( 1, LifeInMonths - 1, 0, 0) , SalvageValue),
// Allocating annual values to months
Allocate, HSTACK( EXPAND( Allocateλ( Depreciate, "Y", "M"), 1, LifeInMonths - 1, 0), SalvageValue),
Opening, DROP( HSTACK( InitialValue, SCAN( InitialValue, Allocate, LAMBDA( Acc, Val, Acc - Val))), 0, -1),
// Create a monthly timeline for the asset
AssetTimeline, EDATE( InServiceDate, SEQUENCE( , LifeInMonths, 0)),
// Aggregate monthly allocations by model's period intervals and position in timelne
Depreciation, MAP( SEQUENCE( , TimelineCols),
LAMBDA( n,
SUM((Allocate) * ( AssetTimeline <= INDEX(EndDates, n)) * ( AssetTimeline >= INDEX( Timeline, n))))),
CAPEX, InitialValue * (Timeline <= InServiceDate) * (EndDates >= InServiceDate),
OpeningAmount, MAP( SEQUENCE( , TimelineCols),
LAMBDA( n,
IFERROR(INDEX( Opening, MATCH(1,(AssetTimeline >= INDEX( Timeline, n))*(AssetTimeline<=INDEX( EndDates, n)),0)),0))),
BookValue, OpeningAmount - Depreciation,
SalesRevenue, SalvageValue * (Timeline <= DisposalDate) * (EndDates >= DisposalDate),
DisposalExp, DisposalCost * (Timeline <= DisposalDate) * (EndDates >= DisposalDate),
// Assemble this assets rows into a block
NewBlock, VSTACK( CAPEX, OpeningAmount, Depreciation, BookValue, SalesRevenue, DisposalExp),
// Add this block to prior blocks (if any)
Result, IF( Asset = 1,
NewBlock,
VSTACK(
Block,
EXPAND("", 1, TimelineCols, ""),
NewBlock
)
),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: LabelDepreciateλ
DESCRIPTION:*//**Create labels for Depreciateλ*/
/* REVISIONS: Date Developer Description
Oct 24 2023 Craig Hatmaker Original Development
*/
LabelDepreciateλ = LAMBDA(
// Parameter Declarations
[AssetNames],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →LabelDepreciateλ( AssetNames)¶" &
"DESCRIPTION: →Create row labels for the depreciation schedule array produced by Depreciateλ¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-depreciate%CE%BB¶" &
"VERSION: →BXL: Oct 24 2023¶" &
"PARAMETERS: →¶" &
"AssetNames →(Required) The names or descriptions of each asset in teh depreciation schedule produced by Depreciateλ¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.LabelDepreciateλ(tblAssets[Asset Name])",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( AssetNames),
// Procedure
Result, MAKEARRAY( ROWS(AssetNames) * 7 - 1, 1,
LAMBDA(R, C,
LET(AssetName, INDEX( AssetNames, QUOTIENT(R-1, 7) + 1),
SWITCH( MOD(R-1, 7) + 1,
1, AssetName & " CAPEX",
2, AssetName & " Opening Value",
3, AssetName & " Depreciation",
4, AssetName & " Book Value",
5, AssetName & " Revenue from Sale",
6, AssetName & " Disposal Costs",
7, ""
)
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: SumDepreciateλ
DESCRIPTION:*//**Create row totals for Depreciateλ*/
/* REVISIONS: Date Developer Description
Dec 21 2023 Craig Hatmaker Original Development
*/
SumDepreciateλ = LAMBDA(
// Parameter Declarations
[DepreciationSchedule],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →SumDepreciateλ(DepreciationSchedule)¶" &
"DESCRIPTION: →Create row totals for Depreciateλ¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-depreciate%CE%BB¶" &
"VERSION: →BXL: Dec 21 2023¶" &
"PARAMETERS: →¶" &
"DepreciationSchedule→(Required) An array produced by Depreciateλ¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.SumDepreciateλ(DepreciationSchedule)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( DepreciationSchedule),
// Procedure
Result, MAKEARRAY(ROWS(DepreciationSchedule), 1,
LAMBDA( R, C,
SWITCH( MOD( R, 7),
1, SUM(CHOOSEROWS( DepreciationSchedule, R)),
3, SUM(CHOOSEROWS( DepreciationSchedule, R)),
5, SUM(CHOOSEROWS( DepreciationSchedule, R)),
6, SUM(CHOOSEROWS( DepreciationSchedule, R)),
0
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Allocateλ
DESCRIPTION:*//**→Divide one or more amounts equally equally across smaller periods*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
*/
Allocateλ = LAMBDA(
// Parameter Declaration
[Amounts],
[FromInterval],
[ToInterval],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Allocateλ(Amounts, [FromInterval], [ToInterval])¶" &
"DESCRIPTION: →Divide one or more amounts equally equally across smaller periods¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 17 2024¶" &
"PARAMETERS: →¶" &
"Amounts →(Required) One amount or a row of amounts to allocate.¶" &
"FromInterval →(Optional Default=Y) The larger interval in Q=Quarters or Y=Years¶" &
"ToInterval →(Optional Default=M) The smaller interval in W=Weeks, M=Months, or Q=Quarters¶" &
"NOTE! →¶" &
"→When a From Period's amount does not divide evenly to the 1/100 for all To Periods',¶" &
"→the last To Period contains the necessary adjustment.¶" &
"EXAMPLES: →¶" &
"→Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.Allocateλ({999.99, 2000}, ""Y"", ""Q"")¶" &
"→Result¶" &
"→250,250,250,249.99,500,500,500,500",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Amounts),
// Set defaults for optional values
FromInterval, IF( ISOMITTED( FromInterval), "Y", FromInterval),
ToInterval, IF( ISOMITTED( ToInterval), "M", ToInterval),
// Procedure
From, SWITCH(FromInterval, "Y", 1, "Q", 4),
To, SWITCH(ToInterval, "Q", 4, "M", 12, "W", 52),
FromCount, COUNT( Amounts),
ToCount, To / From,
Result, REDUCE( 0, SEQUENCE( , FromCount),
LAMBDA( Acc, n,
LET(
FromAmount, INDEX( Amounts, n),
ToAmount, ROUND( FromAmount * From / To, 2),
BaseArray, EXPAND( ToAmount, 1, ToCount, ToAmount),
ToArray, IF( ToCount = 1,
BaseArray,
HSTACK(
TAKE(BaseArray, 1, ToCount - 1),
FromAmount - SUM( TAKE(BaseArray, 1, ToCount - 1))
)
),
Result, IF( n = 1, ToArray, HSTACK( Acc, ToArray )),
Result
)
)
),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: PeriodDiffλ
DESCRIPTION:*//**Determine the number of periods between two dates.*/
/* REVISIONS: Date Developer Description
Apr 15 2024 Craig Hatmaker Copyright
*/
PeriodDiffλ = LAMBDA(
// Parameter Declaration
[StartDate],
[EndDate],
[Interval],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: PeriodDiffλ(StartDate, EndDate, Interval)¶" &
"DESCRIPTION: →Determine the number of periods between two dates.¶" &
"NOTES! →This improves on, and corrects problems w/Excel's deprecated DATEDIF() function.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 15 2024¶" &
"PARAMETERS: →¶" &
"StartDate →(Required) Start Date.¶" &
"EndDate →(Required) End Date¶" &
"Interval →(Required) W=Week, M=Month, Q=Quarter, Y=Year.¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"1 →=BXF.PeriodDiffλ(""2024-01-01"", ""2024-04-15"", ""Q"")",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( StartDate),
ISOMITTED( EndDate),
ISOMITTED( Interval)
),
// Constants
MpP, SWITCH( Interval, "M", 1, "Q", 3, "Y", 12, 0),
// Procedure
Months, DATEDIF( StartDate, EndDate, "M") + 1,
Periods, INT( Months / MpP),
PartialPeriod, N( MOD( Months, MpP) > 0),
EOMCorrection, N( AND( EndDate = EOMONTH( EndDate, 0), DAY( StartDate) > Day( EndDate))),
Result, IF( Interval = "W",
INT((EndDate - StartDate + 1) / 7),
Periods + OR(PartialPeriod, EOMCorrection)),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: SLNλ
DESCRIPTION:*//**Straight Line depreciation method for one asset or asset class.*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
*/
SLNλ = LAMBDA(
// Parameter Declaration
[Cost],
[Salvage],
[Life],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →SLNλ(Cost, Salvage, Life)¶" &
"DESCRIPTION: →Straight Line Depreciation Method for one asset or asset class.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 17 2024¶" &
"PARAMETERS: →¶" &
"Cost →(Required) Asset's initial cost.¶" &
"Salvage →(Required) Asset's value and end of life¶" &
"Life →(Required) Asset's useful life in years.¶" &
"EXAMPLES: →¶" &
"→Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.SLNλ(1000, 100, 5)¶" &
"→Result¶" &
"→180,180,180,180,180",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Cost),
ISOMITTED( Salvage),
ISOMITTED( Life)
),
// Procedure
Depn, SLN(Cost, Salvage, Life),
Result, EXPAND(Depn, 1, Life, Depn),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: SYDλ
DESCRIPTION:*//**Sum-of-years' digits depreciation method for one asset or asset class.*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
*/
SYDλ = LAMBDA(
// Parameter Declaration
[Cost],
[Salvage],
[Life],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →SYDλ(Cost, Salvage, Life)¶" &
"DESCRIPTION: →Sum-of-years' digits depreciation method for one asset or asset class.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 17 2024¶" &
"PARAMETERS: →¶" &
"Cost →(Required) Asset's initial cost.¶" &
"Salvage →(Required) Asset's value and end of life¶" &
"Life →(Required) Asset's useful life in years.¶" &
"EXAMPLES: →¶" &
"→Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.SYDλ(1000, 100, 5)¶" &
"→Result¶" &
"→300,240,180,120,60",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Cost),
ISOMITTED( Salvage),
ISOMITTED( Life)
),
// Procedure
Result, SYD(Cost, Salvage, Life, SEQUENCE( , Life)),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: DBλ
DESCRIPTION:*//**Fixed declining balance depreciation method for one asset or asset class.*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
*/
DBλ = LAMBDA(
// Parameter Declaration
[Cost],
[Salvage],
[Life],
[Months],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →DBλ(Cost, Salvage, Life, [Month])¶" &
"DESCRIPTION: →Fixed declining balance depreciation method for one asset or asset class¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 17 2024¶" &
"PARAMETERS: →¶" &
"Cost →(Required) Asset's initial cost.¶" &
"Salvage →(Required) Asset's value and end of life. Salvage cannot be 0.¶" &
"Life →(Required) Asset's useful life in years.¶" &
"Months →(Optional) Number of months in the first year to depreciate.¶" &
"EXAMPLES: →¶" &
"→Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.DBλ(1000, 100, 5)¶" &
"→Result¶" &
"→369.00,232.84,146.92,92.71,58.53",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Cost),
ISOMITTED( Salvage),
ISOMITTED( Life)
),
// Set defaults for optional values
Months, IF( ISOMITTED( Months), 12, Months),
// Procedure
Depn, DB( Cost, Salvage, Life, SEQUENCE( , Life - 1 ), Months),
Result, HSTACK( Depn, Cost - Sum(Depn) - Salvage),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: DDBλ
DESCRIPTION:*//**Double-declining balance depreciation method for one asset or asset class.*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
*/
DDBλ = LAMBDA(
// Parameter Declaration
[Cost],
[Salvage],
[Life],
[Factor],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →DBλ(Cost, Salvage, Life, [Factor])¶" &
"DESCRIPTION: →Double-declining balance depreciation method for one asset or asset class.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 17 2024¶" &
"PARAMETERS: →¶" &
"Cost →(Required) Asset's initial cost.¶" &
"Salvage →(Required) Asset's value and end of life. Salvage cannot be 0.¶" &
"Life →(Required) Asset's useful life in years.¶" &
"Factor →(Optional Default=2) Rate at which the balance declines.¶" &
"EXAMPLES: →¶" &
"→Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.DDBλ(1000, 100, 5)¶" &
"→Result¶" &
"→400.00,240.00,144.00,86.40,29.60",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Cost),
ISOMITTED( Salvage),
ISOMITTED( Life)
),
// Set defaults for optional values
Factor, IF( ISOMITTED( Factor), 2, Factor),
// Procedure
Depn, DDB( Cost, Salvage, Life, SEQUENCE( , Life -1), Factor),
Result, HSTACK( Depn, Cost - Sum(Depn) - Salvage),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: VDBλ
DESCRIPTION:*//**Variable declining balance depreciation method for one asset or asset class.*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
*/
VDBλ = LAMBDA(
// Parameter Declaration
[Cost],
[Salvage],
[Life],
[Factor],
[No_Switch],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →VDBλ(Cost, Salvage, Life, [Factor], [No_Switch])¶" &
"DESCRIPTION: →Variable declining balance depreciation method for one asset or asset class.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 17 2024¶" &
"PARAMETERS: →¶" &
"Cost →(Required) Asset's initial cost.¶" &
"Salvage →(Required) Asset's value and end of life. Salvage cannot be 0.¶" &
"Life →(Required) Asset's useful life in years.¶" &
"Factor →(Optional Default=2) Rate at which the balance declines.¶" &
"No_Switch →(Optional Default=FALSE) Switch to straight-line depreciation when¶" &
"→ depreciation is greater than the declining balance calculation.¶" &
"EXAMPLES: →¶" &
"→Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.VDBλ(1000, 100, 5, 1.5, TRUE)¶" &
"→Result¶" &
"→300.00,210.00,147.00,121.50,121.50",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Cost),
ISOMITTED( Salvage),
ISOMITTED( Life)
),
// Set defaults for optional values
Factor, IF( ISOMITTED( Factor), 2, Factor),
No_Switch, IF( ISOMITTED( No_Switch), FALSE, No_Switch),
// Procedure
Result, VDB( Cost, Salvage, Life, SEQUENCE( , Life, 0), SEQUENCE( , Life), Factor),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: MACRSλ
DESCRIPTION:*//**Modified Accelerated Cost Recovery System depreciation method for one asset or asset class.*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
*/
MACRSλ = LAMBDA(
// Parameter Declaration
[Cost],
[Life],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →MACRSλ(Cost, Life)¶" &
"DESCRIPTION: →Modified Accelerated Cost Recovery System depreciation method for one asset or asset class.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Apr 17 2024¶" &
"PARAMETERS: →¶" &
"Cost →(Required) Asset's initial cost.¶" &
"Life →(Required) Asset's useful life in years.¶" &
"EXAMPLES: →¶" &
"→Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.MACRSλ(1000, 5)" &
"→Result¶" &
"→300.00,210.00,147.00,121.50,121.50",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Cost),
ISOMITTED( Life)
),
// Set defaults for optional values
Percentage, SWITCH(
Life,
03, {33.33,44.45,14.81,7.41},
05, {20.00,32.00,19.20,11.52,11.52,5.76},
07, {14.29,24.49,17.49,12.49,8.93,8.92,8.93,4.46},
10, {10.00,18.00,14.40,11.52,9.22,7.37,6.555,6.55,6.56,6.55,3.25},
15, {5.00,9.50,8.55,7.70,6.93,6.23,5.90,5.90,5.91,5.90,5.91,5.90,5.91,5.90,5.91,2.95},
20, {3.75,7.22,6.68,6.18,5.71,5.29,4.89,4.52,4.46,4.46,4.46,4.46,4.46,4.46,4.46,4.46,4.46,2.23}
)/100,
// Procedure
Result, Cost * INDEX(Percentage, SEQUENCE( , Life + 1)),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: DepreciateSLNλ
DESCRIPTION:*//**Create single straight line depreciation row calculation.*/
/* REVISIONS: Date Developer Description
Jun 21 2023 Craig Hatmaker Original Development & Copyright
Jun 19 2024 Craig Hatmaker Used Rollingλ()
*/
DepreciateSLNλ = LAMBDA(
// Parameter Declaration
[CAPEX],
[Life],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →DepreciateSLNλ( CAPEX, Life)¶" &
"DESCRIPTION: →Create single straight line depreciation row calculation.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →BXL: Jun 21 2023¶" &
"PARAMETERS: →¶" &
" CAPEX →(Required) A row of capital expenditures per period to depreciate.¶" &
" Life →(Required) Asset's useful life in years.¶" &
"EXAMPLES: →¶" &
" Formula →=DepreciateSLNλ({0,0,0,1000,2000,5000}, 4)¶" &
" Result →{0, 0, 0, 200, 750, 2000,}→",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( CAPEX),
ISOMITTED( Life)
),
// Procedure
WindowSize, Life,
WindowStart, - Life + 1,
Result, Rollingλ( CAPEX, WindowStart, WindowSize, SUM, TRUE) / Life,
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
// SubRoutines
/* FUNCTION NAME: TimelineOffsetλ
DESCRIPTION:*//**Determines how many periods one array is offset from the timeline*/
/* DISCUSSION: The possibility exists requiring calculations before a model's timeline. An example is
depreciation of assets in an existing company wanting to forecast some activity.
In such cases, it can be advantageous to perform the calculations within their own timeline
and then place the results properly within the model's timeline.
This routine determine where such calculation results fit within the model's timeline and
crops those results that do not belong. */
/* REVISIONS: Date Developer Description
Dec 12 2023 Craig Hatmaker Original Development
Dec 20 2023 Craig Hatmaker Made sure date parameter is numeric, not text.
Dec 21 2023 Craig Hatmaker Added @ to Date
*/
TimelineOffsetλ = LAMBDA(
// Parameter Declarations
[Date],
[Timeline],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →TimelineOffsetλ(ArrayStart, Timeline)¶" &
"DESCRIPTION: →Determines in which period, relative to the timeline's start and interval,¶" &
"→a date falls within.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 12 2023¶" &
"PARAMETERS: →¶" &
"Date →(Required) A starting date¶" &
"Timeline →(Required) A model's timline (Row of period start dates)¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.TimelineOffsetλ(""2/15/2022"", EDATE(""1/1/2023"", SEQUENCE( , 12, 0)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Date),
ISOMITTED( Timeline)
),
// Check inputs - If date is in text, convert to value
Date, @IF( ISTEXT( Date), DATEVALUE( Date), Date),
/* Premise: Create a short timeline that is in sync with the model's timeline that spans
from the timeline start to the date in question plus one period on either side.
No we can use MATCH() to find which timeline period */
// Determine timeline's interval (length of periods)
Period1, INDEX( Timeline, 1),
Period2, INDEX( Timeline, 2),
MpP, ROUND( (Period2 - Period1)/30.5, 0), //Months Per Period
// Determine if Prior to timeline or after
Direction, IF(Date < Period1, -1, 1),
// Determine the number of months between timeline's start and the date's period start
MonthDiff, @DATEDIF( MIN( Period1, Date), MAX( Period1, Date), "M"),
// Convert to number of periods
PeriodDiff, QUOTIENT( MonthDiff, MpP),
PeriodSeq, SEQUENCE( 1, PeriodDiff + 2, IF( Direction = 1, 0, -( PeriodDiff + 1))),
SearchTimeline, EDATE( Period1, PeriodSeq * MpP),
Result, MATCH( Date, SearchTimeline, 1) - IF( Direction = 1, 1, PeriodDiff + 2),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: TimelinePositionλ
DESCRIPTION:*//**Crops an array based on a model's timeline*/
/* REVISIONS: Date Developer Description
Dec 12 2023 Craig Hatmaker Original Development
Dec 21 2023 Craig Hatmaker Added @ to Offset
*/
TimelinePositionλ = LAMBDA(
[Array],
[Timeline],
[Offset],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →TimelinePositionλ(Array, Timeline, Offset)¶" &
"DESCRIPTION: →Position an array relative to the model's timeline¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Dec 12 2023¶" &
"PARAMETERS: →¶" &
"Array →(Required) The array to be positioned relative to a model's timeline¶" &
"Timeline →(Required) A model's timline¶" &
"Offset →(Required) The number of rows or columns to start placing the array within the timeline¶" &
"NOTES! →¶" &
"→Timelines can be vertical or horizontal. Whichever way the timeline goes, the array must match.¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"→=BXF.TimelinePositionλ(SEQUENCE(3,3), EDATE(""1/1/2023"", SEQUENCE( , 12, 0)), 12)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Array),
ISOMITTED( Timeline),
ISOMITTED( Offset)
),
// Check inputs - Make sure scalars are not arrays
Offset, @Offset,
// Precedure
Rows, ROWS(Array),
Cols, COLUMNS(Array),
Result, IF( COLUMNS(TIMELINE) > 1,
// Horizontal Timeline
MAKEARRAY( Rows, COLUMNS( Timeline),
LAMBDA(R, C,
IF( ((C - Offset) >= 1) * (( C - Offset) <= Cols) = 1,
INDEX( Array, R, C - Offset),
0)
)
),
// Vertical Timeline
MAKEARRAY( ROWS( Timeline), Cols,
LAMBDA(R, C,
IF( ((R - Offset) >= 1) * (( R - Offset) <= Rows) = 1,
INDEX( Array, R - Offset, C),
0)
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: RangeToDAλ
DESCRIPTION:*//**Create a dynamic array from a static range*/
/* REVISIONS: Date Developer Description
Jan 15 2024 Craig Hatmaker Original Development
*/
RangeToDAλ = LAMBDA(
// Parameter Declarations
[Cell],
[Rows],
[Columns],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →RangeToDAλ( Cell, [Rows], [Columns])¶" &
"DESCRIPTION: →Create a dynamic array from a static range¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Jan 15 2024¶" &
"PARAMETERS: →¶" &
"Cell →(Required) The upper left cell of a range to convert¶" &
"Rows →(Optional) The number of rows for dynamic array. Defaults to 1.¶" &
"Columns →(Optional) The number of columns for dynamic array. Defaults to 1.¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
" →=BXF.RangeToDAλ( A1, , 12)",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Cell),
// Set Defaults
Rows, IF(ISOMITTED(Rows), 1, IF(Rows = 0, 1, Rows)),
Columns, IF(ISOMITTED(Columns), 1, IF(Columns = 0, 1, Columns)),
// Procedure
Result, OFFSET(Cell, 0, 0, Rows, Columns),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: PeriodStartλ
DESCRIPTION:*//**Determine when the period containing a date of interest starts*/
/* REVISIONS: Date Developer Description
Apr 13 2024 Craig Hatmaker Copyright
*/
PeriodStartλ = LAMBDA(
// Parameter Declaration
[AnyPeriodStart],
[MonthsPerPeriod],
[DateOfInterest],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →PeriodStartλ(AnyPeriodStart, MonthsPerPeriod, DateOfInterest)¶" &
"DESCRIPTION: →Determine when the period containing a date of interest starts.¶" &
"WEBPAGE: →<coming soon>¶" &
"VERSION: →Apr 13 2024¶" &
"PARAMETERS: →¶" &
"AnyPeriodStart →(Required) Any start date within a timeline.¶" &
"MonthsPerPeriod→(Required) Number of months in each period¶" &
"DateOfInterest →(Required) A date for which we want to know the start of the period containing it.¶" &
"EXAMPLES: →¶" &
"Result →Formula (BXF is assumed to be the module's name)¶" &
"2024-04-01 →=BXF.PeriodStartλ(""2024-01-01"", 3, ""2024-04-15"")",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( AnyPeriodStart),
ISOMITTED( MonthsPerPeriod),
ISOMITTED( DateOfInterest)
),
// Constants
MpY, 12, //Months Per Year
MpP, MonthsPerPeriod,
// Procedure
DateDay, DAY(DateOfInterest),
PeriodDay, Day(AnyPeriodStart),
DateMonth, MONTH(DateOfInterest),
PeriodMonth, MONTH(AnyPeriodStart),
MonthModDifference, MOD(DateMonth - PeriodMonth, MpP),
NeedPriorPeriod?, AND( MonthModDifference = 0, DateDay < PeriodDay) * - MpP,
NewPeriodMonth, MOD(DateMonth - MonthModDifference + NeedPriorPeriod? -1, MpY ) + 1,
YearCorrection, -((DateMonth * 100 + DateDay) < (NewPeriodMonth * 100 + PeriodDay )),
PeriodDate, DATE(YEAR(DateOfInterest) + YearCorrection, NewPeriodMonth, PeriodDay),
LastDayOfMonthAdj, PeriodDate - (MONTH(PeriodDate) <> NewPeriodMonth),
Result, LastDayOfMonthAdj,
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: FitInTimelineλ
DESCRIPTION:*//**Positions and adjusts an array to fit in a model's timeline*/
/* REVISIONS: Date Developer Description
Feb 17 2025 Craig Hatmaker Original Development
*/
FitInTimelineλ = LAMBDA(
// Parameter Declarations
[Values],
[ValuesRowTypes],
[ValuesTimeline],
[ModelTimeline],
[EndDates?],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →FitInTimelineλ( Values, ValuesRowTypes, ValuesTimeline, ModelTimeline, [EndDates?])¶" &
"DESCRIPTION: →Positions and adjusts an array to fit in a model's timeline.¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Feb 17 2025¶" &
"PARAMETERS:→¶" &
" Values →(Required) 2 dimensional array/range containing values to be placed in the¶" &
"→model's timeline.¶" &
" ValuesRowTypes→(Required) A column indicating if a row in Values contains: ¶" &
"→ T=Transactions are SUM'd when several Values periods fit in one model period.¶" &
"→ O=Opening Balances are pulled from first Values period fitting in a model period.¶" &
"→ C=Closing Balances are pulled from last Values period fitting in a model period.¶" &
"→ B=Blank lines.¶" &
" ValuesTimeline→(Required) A row of dates providing the start date for each column in Values¶" &
" ModelTimeline →(Required) The model's timeline¶" &
" EndDates? →(Optional) TRUE (default) = The model's timeline displays period end dates¶" &
"EXAMPLE:→¶" &
" Usage →=FitInTimelineλ(¶" &
"→ InstallmentDebtsλ!E15#, ¶" &
"→ RepeatRowTypesλ( InstallmentDebtsλ!E15#, ""BBOTTCBOTTTCB""), ¶" &
"→ InstallmentDebtsλ!E5#, ¶" &
"→ E5#)¶" &
"→",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( ValuesRowTypes),
ISOMITTED( ValuesTimeline),
ISOMITTED( ModelTimeline)
),
// Understand the model's timeline
// Set Constants
MpY, 12, //Months Per Year
DpM, 365/MpY, //Days Per month
QpY, 4, //Quarters Per Year
// Determine how many months are in each of the model's periods
FirstPeriod, INDEX( ModelTimeline, 1),
SecondPeriod, INDEX( ModelTimeline, 2),
MpP, @ROUND(( SecondPeriod - FirstPeriod) / DpM, 0), //Months Per Period
// Set timeline dependent defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Interval, SWITCH( MpP, 1, "M", 3, "Q", 12, "Y"),
PpY, SWITCH( MpP, 1, MpY, 3, QpY, 12, 1), //Periods Per Year
Starts, IF( EndDates?, EDATE( +ModelTimeline + 1, - MpP), ModelTimeline),
Ends, IF( EndDates?, ModelTimeline, EDATE( +ModelTimeline, MpP) - 1),
// Pre-calcs
Rows, ROWS( Values),
Cols, COLUMNS( Starts),
Window, ( ValuesTimeline >= MIN( Starts)) * ( ValuesTimeline <= MAX( Ends)),
MWindow, (Ends >= MIN( ValuesTimeline)) * (Starts <= MAX( ValuesTimeline)),
PrdNum, IFERROR( MATCH( ValuesTimeline, Starts, 1), 0) * Window,
// Define functions that handle processing each row type.
Functions, FIND(ValuesRowTypes, "OTCB"),
Openings, LAMBDA( r, c, INDEX( INDEX( Values, r, ), MATCH(c, PrdNum, 0))),
Transactions, LAMBDA( r, c, SUM( CHOOSEROWS( Values, r) * (c = PrdNum))),
Closings, LAMBDA( r, c, INDEX( INDEX( Values, r, ), MATCH(c, PrdNum, 1))*INDEX( MWindow, c)),
Blanks, LAMBDA( r, c, ""),
// Procedure
Result, MAKEARRAY( Rows, Cols,
LAMBDA( r, c,
CHOOSE( INDEX( Functions, r),
IFNA( Openings( r, c), 0),
Transactions( r, c),
IFNA( Closings( r, c), 0),
Blanks( r, c)
)
)
),
// Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: DeriveTimelinesλ
DESCRIPTION:*//**Create internal start and end timelines based on the model's timeline*/
/* REVISIONS: Date Developer Description
Feb 22 2025 Craig Hatmaker Original Development and copyright
*/
DeriveTimelinesλ = LAMBDA(
// Parameter Declarations
[ModelTimeline],
[EndDates?],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →DeriveTimelinesλ( ModelTimeline, [EndDates?])¶" &
"DESCRIPTION: →Create internal start and end timelines based on the model's timeline¶" &
"NOTES: →For use by functions that require start and end dates¶" &
"→without requiring models to have both start and end dates¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Feb 22 2025¶" &
"PARAMETERS:→¶" &
" ModelTimeline →(Required) Either the model's start or end timeline¶" &
" EndDates? →(Optional) TRUE (default) = The model's timeline displays period end dates¶" &
"EXAMPLE:→¶" &
" Usage →=DeriveTimelinesλ( Timeline)",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( ModelTimeline),
// Understand the model's timeline
// Set Constants
MpY, 12, //Months Per Year
DpM, 365/MpY, //Days Per month
QpY, 4, //Quarters Per Year
// Determine how many months are in each of the model's periods
FirstPeriod, INDEX( ModelTimeline, 1),
SecondPeriod, INDEX( ModelTimeline, 2),
MpP, @ROUND(( SecondPeriod - FirstPeriod) / DpM, 0), //Months Per Period
// Set timeline dependent defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Interval, SWITCH( MpP, 1, "M", 3, "Q", 12, "Y"),
PpY, SWITCH( MpP, 1, MpY, 3, QpY, 12, 1), //Periods Per Year
Starts, IF( EndDates?, EDATE( +ModelTimeline + 1, - MpP), ModelTimeline),
Ends, IF( EndDates?, ModelTimeline, EDATE( +ModelTimeline, MpP) - 1),
// Procedure
Result, VSTACK(Starts, Ends),
// Result
CHOOSE(Help? + 1, Result, Help)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment