Skip to content

Instantly share code, notes, and snippets.

@CHatmaker

CHatmaker/BXL 5g Functions LAMBDA Financial Models 2025

Last active December 1, 2025 20:23
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save CHatmaker/472f65269519a0937b03f741e9271334 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
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
// BXL 5g Functions: Financial Library 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
Aug 07 2025 Craig Hatmaker Version 2
Sep 27 2025 Craig Hatmaker See Growthλ
Oct 06 2025 Craig Hatmaker Added CorkscrewAλ
Oct 08 2025 Craig Hatmaker Prep for loading to gist
Nov 15 2025 Craig Hatmaker Added Inflationλ
Nov 24 2025 Craig Hatmaker See Depreciateλ
Dec 01 2025 Craig Hatmaker See Corkscrewλ
*/
Aboutλ = TRIM(TEXTSPLIT(
"ABOUT: →BXL's Financial Models module. Suggested module name: BXF¶" &
"VERSION: →BXL: Nov 24 2025¶" &
"GIST URL: →https://gist.github.com/CHatmaker/472f65269519a0937b03f741e9271334 ¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-financial-library ¶" &
"→¶" &
"FUNCTION →DESCRIPTION¶" &
" Aboutλ →Produces this table¶" &
"→¶" &
" Allocateλ →Divide values by smaller periods and distribute evenly.¶" &
" AvgBalIntλ →Calculate interest on average balance.¶" &
" Cumulativeλ →Creates a row or column of cumulative totals from a total row or column¶" &
" Growthλ →Project growth or decline factors that can be applied to values to increase or decrease them. ¶" &
" Inflationλ →Create period factors for adjusting amounts by inflation.¶" &
" 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¶" &
" PMTAλ →Determine monthly payment amount for Actual/360, Actual/365, or Actual/Actual¶" &
" Reversalλ →Create a row that reverses input values in the next period.¶" &
" 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.¶" &
" AmortizeLabelsλ →Create row labels for Amortizeλ result¶" &
" AmortizeSumλ →Create row totals for payments, interest, and principal portion in Amortizeλ results¶" &
"→¶" &
"CORKSCREW SUITE→¶" &
" Corkscrewλ →Creates a corkscrew section from inflows (additions) and outflows (subtractions).¶" &
" CorkscrewAλ →Creates a simple corkscrew that derives opening balances from actual periods.¶" &
" 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¶" &
" CorkScrewAvgBalIntλ→Create a corkscrew that applies a rate to the average of opening and closing balances¶" &
"→¶" &
"DEPRECIATION SUITE→¶" &
" Depreciateλ →Create a block of CAPEX, Opening Balance, Depreciation Values, and Book Value for each asset¶" &
" DepreciateLabelsλ →Create row labels for Depreciateλ result¶" &
" DepreciateSumsλ →Create row totals for CAPEX, Depreciation, Book Value, Salvage Value, and Disposal costs in Depreciateλ results¶" &
" SUB FUNCTIONS →Functions used by Depreciateλ. Not intended for standalone use.¶" &
"  fnAllocateλ →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.¶" &
"→¶" &
"SUB FUNCTIONS →Functions used by other functions or general purpose functions.¶" &
" FitInTimelineλ →Positions and adjusts an array to fit in a model's timeline.¶" &
" 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.¶" &
" PeriodDiffλ →Determine the number of periods between two dates.",
"→","¶"
)
);
/* FUNCTION NAME: Allocateλ
DESCRIPTION:*//**→Divide one or more amounts equally equally across smaller periods*/
/* REVISIONS: Date Developer Description
Apr 17 2024 Craig Hatmaker Copyright
Jun 25 2025 Craig Hatmaker Added ability to handle multiple amounts in our out of a timeline
*/
Allocateλ = LAMBDA(
// Parameter Declaration
[Amounts],
[Timeline],
[FromInterval],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Allocateλ( Amounts, Timeline, [FromInterval])¶" &
"DESCRIPTION: →Divide one or more amounts equally equally across smaller periods¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-allocate%CE%BB¶" &
"VERSION: →BXL: Jun 25, 2025¶" &
"PARAMETERS: →¶" &
" Amounts →(Required) One or more amounts to allocate in a row. The amount's position¶" &
"→ under the model's timeline indicates when the amount is incurred.¶" &
" Timeline →(Required) The model's timeline.¶" &
" FromInterval →(Optional Default=Y) Y=amounts are annual, Q=quarterly.",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Amounts),
ISOMITTED( Timeline)),
// Set defaults for optional values
FromInterval, IF( ISOMITTED( FromInterval), "Y", FromInterval),
// Get Starts and Ends
Timelines, DeriveTimelinesλ( Timeline, 1),
ToInterval, INDEX( Timelines, 3, 1),
// Pre Calcs
From, SWITCH(FromInterval, "A", 1, "Y", 1, "Q", 4, "M", 12, "W", 52),
To, SWITCH(ToInterval, "A", 1, "Y", 1, "Q", 4, "M", 12, "W", 52),
AmountCount, COLUMNS( Amounts),
Counter, SEQUENCE( AmountCount),
// Allocate Function
Allocate, LAMBDA( Amount, From, To, AmountIndex,
LET(
Allocation, Amount * From / To,
Array, EXPAND( Allocation, 1, To/From, Allocation),
Result, IF( AmountIndex > 1,
HSTACK( EXPAND( 0, 1, AmountIndex - 1 , 0), Array),
HSTACK( Array)
),
Result
)
),
// Procedure
Block, REDUCE( Timeline, Counter,
LAMBDA( Acc, n,
LET(
Amount, INDEX( Amounts, n),
IF( Amount = 0,
Acc,
VSTACK( Acc, Allocate( Amount, From, to, n))
)
)
)
),
Result, IFNA( TAKE( DROP( Block, 1), , COLUMNS( Timeline)), 0),
// Return Result or Help
CHOOSE( Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: AvgBalIntλ
DESCRIPTION:*//**Calculates interest on the average of opening and closing balances*/
/* REVISIONS: Date Developer Description
Jun 31 2025 Craig Hatmaker Copyright
*/
AvgBalIntλ = LAMBDA(
[InterestRate],
[OpeningBalance],
[Additions],
Let(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →AvgBalIntλ( InterestRate, [OpeningBalance], Additions)¶" &
"DESCRIPTION: →Calculates interest on the average of opening and closing balances. ¶" &
"→Closing balance includes interest.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-avgbalint%CE%BB¶" &
"VERSION: →BXL: Jun 31 2025¶" &
"PARAMETERS: →¶" &
" InterestRate →(Required) The APR divided by the number of periods in a year.¶" &
" OpeningBalance →(Required) The opening balance.¶" &
" Additions, →(Optional) Total of amounts, other than interest, to add/sub to closing balance. Default = 0.",
"→", "¶" )
),
Help?, OR( ISOMITTED( InterestRate),
ISOMITTED( OpeningBalance)),
// Defaults
Additions, IF( ISOMITTED( Additions), 0, Additions),
// Recursive function (fnInterest) definition
fnInterest, LAMBDA(fn, InterestRate, Open, Additions, Close,
LET(
AvgBalance, Average(Open, Close),
Interest, AvgBalance * InterestRate,
Closing, Open + Additions + Interest,
Difference, AvgBalance - Average( Open, Closing),
Result, IF( ABS(Difference) < .0001,
Interest,
fn( fn, InterestRate, Open, Additions, Closing)
),
Result
)
),
// Procedure
Result, fnInterest( fnInterest, InterestRate, OpeningBalance, Additions, 0),
// 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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-cumulative%CE%BB¶" &
"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.",
"→", "¶" )
),
// 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: Growthλ
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
Jul 24 2025 Craig Hatmaker Copyright
Sep 27 2025 Craig Hatmaker Timeline made optional
*/
Growthλ = LAMBDA(
// Parameter Declarations
[Actuals],
[Percentages],
[Timeline],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Growthλ( Actuals, Percentages, [Timeline])¶" &
"DESCRIPTION: →Projects percentage increases/(decreases) over the last Actual amount.¶" &
"→ Uses include applying inflation to amounts or to ramp up sales projections. ¶" &
"→ NOTE! Percents are not applied to Actuals.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-growth%CE%BB¶" &
"VERSION: →BXL: Jul 24 2025¶" &
"PARAMETERS: →¶" &
" Actuals →(Required) A single number or an array of actuals placed under the timeline.¶" &
"→ NOTE! If a single number, it is the first period's actual (not forecast) value.¶" &
"→ If an array of actuals, select only actuals columns. Exclude forecast columns.¶" &
"→ Increases or decreases are not applied to actuals columns, only forecast.¶" &
" Percentages →(Required) A single number or an array of change percentages placed under the timeline.¶" &
"→ NOTE! Positive percentages are increases, negative percentages are decreases.¶" &
"→ NOTE! Use monthly % for monthly timelines and annual % for annual timelines.¶" &
" Timeline →(Optional) The model's timeline. If omitted, number of periods is derived from percentages.",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Actuals),
ISOMITTED( Percentages)
),
// Set defaults
ActCols, COLUMNS( Actuals),
PctCols, COLUMNS( Percentages),
Cols, IF( ISOMITTED( Timeline), PctCols, COLUMNS( Timeline)),
Percentages, IF( PctCols = 1,
EXPAND( Percentages, 1, Cols, Percentages),
EXPAND( Percentages, 1, Cols, 0)
),
Periods, SEQUENCE( , Cols),
Result, Scan( 0, Periods,
LAMBDA( Acc, Period,
LET(Factor, INDEX( Percentages, Period),
Result, IF( Period <= ActCols ,
INDEX( Actuals, Period),
Acc * ( 1 + Factor)
),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Inflationλ
DESCRIPTION:*/ /**Create inflation factors for adjusting amounts*/
/* REVISIONS: Date Developer Description
Nov 15 2025 Craig Hatmaker Copyright on LAMBDA
*/
Inflationλ = LAMBDA(
[PercentageRates],
[EffectiveDates],
[Timeline],
[EndDates?],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →IntOnIntλ( PercentageRates, EffectiveDates, Timeline)¶" &
"DESCRIPTION: →Create period factors for adjusting amounts to inflation¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-inflation%CE%BB¶" &
"VERSION: →BXL: Nov 15 2025¶" &
"PARAMETERS: →¶" &
" PercentageRates→(Required) Forecasted annual inflation percentage¶" &
"→ NOTE! This converts rates for monthly and quarterly timelines using:¶" &
"→ ( 1 + Annual % Rate) ^ (1 / number of periods in year) - 1¶" &
" EffectiveDates→(Required) Date when the percentage rate is applied¶" &
"→ NOTE! To prevent inflating amounts in the first period, set the first¶" &
"→ effective date to the second period start date.¶" &
" Timeline →(Required) Model's timeline¶" &
" EndDates? →(Optional) If TRUE, timeline displays period end dates, else, start dates.¶" &
"→ Default=TRUE.",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( PercentageRates),
ISOMITTED( EffectiveDates),
ISOMITTED( Timeline)
),
Result, IF( Help?,
Help,
LET(
// Set Defaults
EndDates, IF(ISOMITTED( EndDates?), TRUE, EndDates? ),
// Get Timeline attributes
Timelines, DeriveTimelinesλ( Timeline, EndDates?),
Ends, CHOOSEROWS( Timelines, 2),
Interval, TAKE( Timelines, -1, -1),
Periods, SWITCH( Interval, "M", 12, "Q", 4, "Y", 1),
// Create rates
AnnualRates,XLOOKUP( Ends, EffectiveDates, PercentageRates, 0,-1),
PeriodRates,( 1 + AnnualRates) ^ ( 1 / Periods) - 1,
Result, SCAN( 1, PeriodRates, LAMBDA( Acc, Val, Acc + Acc * Val)),
Result
)
),
Result
)
);
/* 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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-intonint%CE%BB¶" &
"VERSION: →BXL: Dec 29 2023¶" &
"PARAMETERS: →¶" &
" Principal →(Required) Principal amount¶" &
" Rate →(Required) Interest percentage rate. For years, APR. For months, APR/12",
"→", "¶"
)
),
// 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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-irr%CE%BB¶" &
"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",
"→", "¶"
)
),
// 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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-movement%CE%BB¶" &
"VERSION: →BXL: Jun 21 2023¶" &
"PARAMETERS: →¶" &
" BeginningValues→(Optional) Defaults to 0¶" &
" Values →(Required) A row of values to compare column to column",
"→", "¶" )
),
// 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)
)
);
PMTAλ = LAMBDA(
[APR],
[TermInMonths],
[DebtAmount],
[StartDate],
[AccrualMethod],
[Tolerance],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →PMTAλ( APR, TermInMonths, DebtAmount, StartDate, AccrualMethod, [Tolerance])¶" &
"DESCRIPTION: →Determine monthly payment amount for Actual/360, Actual/365, or Actual/Actual¶" &
"→ This does the same thing for commercial loans that PMT() does for consumer loans.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-pmta%CE%BB¶"&
"VERSION: →BXL: Jul 30 2025¶"&
"PARAMETERS:→¶"&
" APR →(Required) Annual interest rate¶"&
" TermInMonths →(Required) Count of months from first payment due to end of term (inclusive).¶"&
" DebtAmount →(Required) Debt amount when first payment is due.¶"&
" StartDate →(Required) Date when first payment is due¶"&
" AccrualMethod →(Required) Select: 1=30/360, 2=Actual/360, 3=Actual/365, or 4=Actual/Actual¶"&
" Tolerance →(Optional) Returns a result within this tolerance. Default = .001.",
"→","¶")
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( APR),
ISOMITTED( TermInMonths),
ISOMITTED( DebtAmount),
ISOMITTED( StartDate),
ISOMITTED( AccrualMethod)
),
// Defaults
Tolerance, IF( OR(ISOMITTED( Tolerance), Tolerance = 0), .001, Tolerance),
// Pre Calculations
Counter, SEQUENCE( TermInMonths),
DatesArray, EOMONTH( StartDate, Counter - 1),
MonthDays, IF( AccrualMethod = 1, IF( DatesArray, 30), DAY( DatesArray)),
LeapYears, MONTH( DATE( YEAR( DatesArray), 2, 29)) = 2,
YearDays, CHOOSE( AccrualMethod,
IF( DatesArray, 360),
IF( DatesArray, 360),
IF( DatesArray, 365),
IF( DatesArray, 365) + LeapYears
),
// Initial guesses for the bisection method to find the correct payment.
LoGuess, DebtAmount / TermInMonths,
HiGuess, DebtAmount * (1 + APR),
// Define function to apply payment to all months and determine remaining balance
fnGetBal, LAMBDA(
Payment,
REDUCE( DebtAmount, Counter,
LAMBDA( Acc, n,
LET(
MDays, INDEX( MonthDays, n, 1),
YDays, INDEX( YearDays, n, 1),
Acc + Acc * APR / YDays * MDays - Payment
)
)
)
),
// Define solver-like function to find equal monthly payments
fnPMT, LAMBDA(
fn, LoGuess, HiGuess,
LET(
MidPayment, AVERAGE(LoGuess, HiGuess),
Balance, fnGetBal( MidPayment),
IF( ABS( Balance) < Tolerance,
MidPayment,
IF( Balance > 0,
// If balance is positive, payment guess was too low. Increase lower bound
fn( fn, MidPayment, HiGuess),
// If balance is zero or negative, payment guess was too high or just right. Decrease upper bound
fn( fn, LoGuess, MidPayment)
)
)
)
),
// Run solver-like function to find equal monthly payments
Result, IF( NOT(Help?), fnPMT( fnPMT, LoGuess, HiGuess)),
// 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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5greversal%CE%BB¶" &
"VERSION: →BXL: Mar 25 2024¶" &
"PARAMETERS: →¶" &
" OpeningValue →(Optional) Defaults to 0¶" &
" RowToReverse →(Required) A row of values to reverse the following periods",
"→", "¶" )
),
// 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: →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)
*/
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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-sumcontains%CE%BB¶" &
"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.",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( ValuesToFilter),
ISOMITTED( ValueLabels),
ISOMITTED( FilterByText)),
Result, IF( Help?,
Help,
LET(
// Check inputs - Set defaults
IgnoreCase?, IF( ISLOGICAL( IgnoreCase?), IgnoreCase?, TRUE),
Labels, CHOOSECOLS(ValueLabels, 1),
// Procedure
Matches, IF( IgnoreCase?,
SEARCH( FilterByText, Labels),
FIND( FilterByText, Labels)
),
Filtered, FILTER( ValuesToFilter, ISNUMBER( Matches)),
Result, BYCOL( Filtered, SUM),
Result
)
),
Result
)
);
/* 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λ
May 29 2025 Craig Hatmaker Pro Pack Prep
*/
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: May 29 2025¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-sumperiods%CE%BB¶" &
"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.",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( ValuesTimeline),
ISOMITTED( ModelTimeline)
),
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
// Get Starts and Ends
Timelines, DeriveTimelinesλ( ModelTimeline, EndDates?),
Starts, CHOOSEROWS( Timelines, 1),
Ends, CHOOSEROWS( Timelines, 2),
// Procedure
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.",
"→", "¶" )
),
// 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: AmortizeLabelsλ
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
Nov 24 2025 Craig Hatmaker Renamed
*/
AmortizeLabelsλ = LAMBDA(
// Parameter Declarations
[LoanNames],
[LoanAmounts],
[LoanAPRs],
[LoanTerms],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →AmortizeLabelsλ([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: Nov 24 2025¶" &
"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.",
"→", "¶"
)
),
// 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: AmortizeSumλ
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
Nov 24 2025 Craig Hatmaker Renamed
*/
AmortizeSumλ = LAMBDA(
// Parameter Declarations
[AmoritizationSchedule],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →AmortizeSumλ(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: Nov 24 2025¶" &
"PARAMETERS: →¶" &
" AmoritizationSchedule→(Required) An array produced by Amoritizeλ.",
"→", "¶"
)
),
// 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)
)
);
// Corkscrew suite
/* FUNCTION NAME: Corkscrewλ
DESCRIPTION:*//**Creates a corkscrew section from inflows (additions) and outflows (subtractions).*/
/* REVISIONS: Date Developer Description
Dec 01 2025 Craig Hatmaker Rewrite
*/
Corkscrewλ = LAMBDA(
// Parameter Declarations
[Opening],
[InFlows],
[OutFlows],
[PositiveOnly?],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Corkscrewλ( [Opening], InFlows, OutFlows, [PositiveOnly?])¶" &
"DESCRIPTION: →Creates a corkscrew section from inflows (additions) and outflows (subtractions).¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-corkscrew%CE%BB¶" &
"VERSION: →BXL: Dec 01 2025¶" &
"PARAMETERS:→¶" &
" Opening →(Optional) First period's opening balance. This defaults to 0. ¶" &
" InFlows →(Required/optional) A row, or several rows HSTACKed to be added to the balance¶" &
"→ NOTE! If this is omitted, OutFlows must be present.¶" &
" OutFlows →(Required/Optional) A row, or several rows HSTACKed to be subtracted from the balance¶" &
"→ NOTE! These must be positive values.¶" &
"→ NOTE! If this is omitted, InFlows must be present.¶" &
" PositiveOnly? →(Optional) TRUE = display flows as positive values. Opens and Closes are not affected.¶" &
"→ Default is FALSE.¶" &
"→ NOTE! If PositiveOnly?=TRUE, indicate which flow(s) are subtracted in corkscrew line labels.",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, AND( ISOMITTED( InFlows), ISOMITTED( OutFlows)),
Result, IF( Help?,
Help,
// Processing
LET(
// Set defaults
Opening, IF( ISOMITTED( Opening), 0, Opening),
PosOnly?, IF( ISOMITTED( PositiveOnly?), FALSE, PositiveOnly?),
Flows, IF( ISOMITTED( InFlows),
-OutFlows,
IF( ISOMITTED( OutFlows),
InFlows,
VSTACK( InFlows, -OutFlows)
)
),
// Process
Totals, BYCOL( Flows, SUM),
Close, SCAN( Opening, Totals, SUM),
Open, DROP( HSTACK( Opening, Close), , -1),
PosFlows, IF( ISOMITTED( InFlows),
OutFlows,
IF( ISOMITTED( OutFlows),
InFlows,
VSTACK( InFlows, OutFlows)
)
),
Result, VSTACK(Open, IF( PosOnly?, PosFlows, Flows), Close),
Result
)
),
Result
)
);
/* FUNCTION NAME: CorkscrewAλ
DESCRIPTION:*/ /**Create a corkscrew account that handles actuals*/
/* REVISIONS: Date Developer Description
Oct 01 2025 Craig Hatmaker Copyright
*/
CorkscrewAλ = LAMBDA(
// Arguments
[Actuals],
[ActualsPeriods],
[Inflows],
[Outflows],
LET(
// Help
Help, TRIM( TEXTSPLIT(
"FUNCTION: →CorkscrewAλ( Actuals, [ActualsPeriods],¶" &
"→ [Inflows], [Outflows]) ¶" &
"DESCRIPTION: →Create a corkscrew account that handles actuals¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-corkscrewa%CE%BB¶" &
"VERSION: →BXL: Oct 01 2025¶" &
"PARAMETERS: →¶" &
" Actuals →(Required) Closing balances from historic periods.¶" &
"→ NOTE! To treat this as an opening balance, set ActualPeriods to 0¶" &
"→ in which case, this should be a single number¶" &
" ActualsPeriods →(Optional) The number of periods (columns) occupied by Actuals.¶" &
"→ Set to zero to treat Actuals as the opening balance.¶" &
"→ This defaults to the number of columns in Actuals.¶" &
"→ NOTE! If this is omitted then Actuals must not include ¶" &
"→  columns reserved for forecasted values.¶" &
" InFlows, →(Optional) One or more rows of values to add ¶" &
"→ to the opening balance. NOTE! If omitted, Outflows are required.¶" &
" OutFlows, →(Optional) One or more rows of values to subtract ¶" &
"→ from the opening balance. NOTE! If omitted, Inflows are required." ,
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Actuals),
ActualsPeriods < 0,
AND( ISOMITTED( InFlows), ISOMITTED( OutFlows))
),
// Handle defaults
ActualsPeriods, IF( ISOMITTED( ActualsPeriods), COLUMNS( Actuals) , ActualsPeriods),
// Procedure
Count, IF( ISOMITTED( Inflows), COLUMNS( Outflows), Columns( Inflows)),
Counter, SEQUENCE( , Count),
Closing, SCAN( IF( ActualsPeriods = 0, TAKE( Actuals, 1, 1), 0), Counter,
LAMBDA( Acc, Period,
LET(
InFlow, IF( ISOMITTED( InFlows), 0, CHOOSECOLS( Inflows, Period)),
OutFlow, IF( ISOMITTED( OutFlows), 0, CHOOSECOLS( Outflows, Period)),
Result, IF( Period <= ActualsPeriods,
INDEX( Actuals, Period),
Acc + SUM(Inflow) - SUM(Outflow)
),
Result
)
)
),
Opening, HSTACK(
SUM(INDEX( Closing, 1) -
IF( ISOMITTED( Inflows), 0, SUM( CHOOSECOLS( Inflows, 1))),
IF( ISOMITTED( Outflows), 0, -SUM( CHOOSECOLS( Outflows, 1)))),
TAKE( Closing, 1, Count - 1)
),
Result, IF( ISOMITTED( InFlows),
VSTACK( Opening, Outflows, Closing),
IF( ISOMITTED( OutFlows),
VSTACK( Opening, InFlows, Closing),
VSTACK( Opening, Inflows, OutFlows, Closing)
)
),
// 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
May 16 2025 Craig Hatmaker Replace REDUCE and STACK with SCAN
*/
CorkscrewRxOλ = LAMBDA(
// Parameter Declarations
[Opening],
[Rates],
[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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-corkscrewrxo%CE%BB¶" &
"VERSION: →BXL: May 16 2025¶" &
"PARAMETERS:→¶" &
" Opening →(Required) First periods opening balance.¶" &
" Rates →(Required) a sinble percent or rate or row of rates to multiple 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. Opens and Closes are not affected.¶" &
"→ Default is FALSE.",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Opening),
ISOMITTED( Rates),
ISOMITTED( Flows)
),
// Processing
Flow, BYCOL( Flows, SUM),
Rates, EXPAND( Rates, 1, COLUMNS( Flow), TAKE( Rates, -1, -1)),
PeriodArray, IF( PositiveOnly, ABS( Flows), Flows),
Thunk, LAMBDA( x, LAMBDA( x)),
// Load Thunks
Thunks, BYCOL( VSTACK(Rates, Flow), Thunk),
Result, LET(
Close, SCAN( Opening, Thunks,
LAMBDA( Acc, Val, Acc + Acc * INDEX( Val(), 1) + INDEX( Val(), 2))
),
Open, HSTACK( Opening, DROP( Close, , -1)),
Rated, Open * Rates,
Result, VSTACK( Open, Rated, PeriodArray, Close),
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
Jul 21 2025 Craig Hatmaker Changed ReversalFlags to indicate which prior period to reverse
Jul 23 2025 Craig Hatmaker Added PositiveOnly
*/
CorkScrewReversalλ=LAMBDA(
// Parameter Declarations
[Opening],
[ReversalFlags],
[Flow1],
[Flow2],
[Flow3],
[Flow4],
[PositiveOnly],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →CorkscrewReversalλ( Opening, FLow1, [Flow2], [Flow3], [Flow4], [PositiveOnly])¶" &
"DESCRIPTION: →Create a corkscrew where the opening balance is reversed in the next period based on a timing parameter¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-corkscrewreversal%CE%BB¶" &
"VERSION: →BXL: Apr 19 2024¶" &
"PARAMETERS:→¶" &
" Opening →(Required) First periods opening balance.¶" &
" ReversalFlags →(Required) A row of numbers indicating how many periods back from current to reverse.¶" &
"→ 0=no reversal this period. 1=Reverse close 1 period prior. 2=Reverse close 2 periods prior, etc.¶" &
" 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. Opens and Closes are not affected.¶" &
"→ Default is FALSE.¶" &
"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
Rows, IF(ISOMITTED(Flow2), 1,
IF(ISOMITTED(Flow3), 2,
IF(ISOMITTED(Flow4), 3, 4)
)
),
Count, COLUMNS(Flow1),
Periods, SEQUENCE( , Count),
// Procedure
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, BYCOL(PeriodArray, LAMBDA(Col, SUM(Col))),
Result, REDUCE( Opening, Periods,
LAMBDA( Acc, Period,
LET(
Open, TAKE( Acc, -1, -1),
ReversePeriod, INDEX( ReversalFlags, Period),
ReverseAmount, IF( VALUE( ReversePeriod) = 0, 0, INDEX( TAKE( Acc, -1), Period - ReversePeriod) ),
DisplayColumn, CHOOSECOLS( PeriodArray, Period),
CalcColumn, CHOOSECOLS( FlowArray, Period),
Column, VSTACK(
Open,
ReverseAmount,
DisplayColumn,
SUM(Open, -ReverseAmount, CalcColumn)),
Result, IF(Period = 1, Column, HSTACK( Acc, Column)),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: CorkScrewAvgBalIntλ
DESCRIPTION:*//**Creates a corkscrew account applying an interest rate to the average of opening and closing balances.*/
/* REVISIONS: Date Developer Description
Jul 31 2025 Craig Hatmaker Copyright
*/
CorkScrewAvgBalIntλ = LAMBDA(
[InterestRate],
[OpeningBalance],
[Additions],
[PositiveOnly],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →CorkScrewAvgBalIntλ( InterestRate, OpeningBalance, [Additions], [PositiveOnly])¶" &
"DESCRIPTION: →Calculates interest on the average of opening and closing plus interest balances ¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-financial-library/5g-corkscrewavgbalint%CE%BB¶" &
"VERSION: →BXL: Jun 31 2025¶" &
"PARAMETERS: →¶" &
" InterestRate →(Required) The APR divided by the number of periods in a year.¶" &
" OpeningBalance→(Required) The first period's opening balance.¶" &
" Additions, →(Optional) One or more rows (VSTACKed if more than 1 row) of period values to add to close. Default = 0¶" &
" PositiveOnly →(Optional) TRUE = display flows as positive values. Opens and Closes are not affected.¶" &
"→ Default is FALSE.¶" &
"NOTE! →All flows are added. To subtract a flow make it negative.",
"→", "¶" )
),
Help?, OR( ISOMITTED( InterestRate),
ISOMITTED( OpeningBalance)),
// Defaults
Additions, IF( ISOMITTED( Additions), 0, Additions),
PositiveOnly, IF( ISOMITTED( PositiveOnly), FALSE, PositiveOnly),
// Pre-Calcs
Count, COLUMNS( Additions),
Periods, SEQUENCE( , Count),
// Recursive function (fnInterest) definition
fnInterest, LAMBDA(fn, InterestRate, Open, Additions, Close,
LET(
AvgBalance, Average(Open, Close),
Interest, AvgBalance * InterestRate,
Closing, Open + Additions + Interest,
Difference, AvgBalance - Average( Open, Closing),
Result, IF( ABS(Difference) < .001,
Interest,
fn( fn, InterestRate, Open, Additions, Closing)
),
Result
)
),
// Procedure
Result, REDUCE( OpeningBalance, Periods,
LAMBDA( Acc, Period,
LET(
// Get Opening
Open, TAKE( Acc, -1, -1),
// Get Current Period Values
TotalAdds, CHOOSECOLS( Additions, Period),
TotalAdd, SUM( TotalAdds),
Interest, fnInterest( fnInterest, InterestRate, Open, TotalAdd, 0),
// Create Period Column
Stack, VSTACK(
Open,
TotalAdds,
Interest,
Open + TotalAdd + Interest
),
Column, IF( PositiveOnly, ABS(Stack), Stack),
Result, IF( Period = 1,
Column,
HSTACK( Acc, Column)
),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
// Depreciation Suite
/* FUNCTION NAME: Depreciateλ
DESCRIPTION:*//**Create a block of MCost, 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
Nov 24 2025 Craig Hatmaker 2025 rewrite
*/
Depreciateλ = LAMBDA(
// Parameter Declarations
[Timeline],
[EndDates?],
[AssetCosts],
[InServiceDates],
[LifeInYears],
[SalvageValues],
[DisposalDates],
[NetDisposals],
[Methods],
[Factors],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Depreciateλ( Timeline, [EndDates?], AssetCosts, InServiceDates, LifeInYears, ¶" &
" → [SalvageValues], [DisposalDates], [NetDisposals], [Methods], [Factor])¶" &
"DESCRIPTION: →Creates an array of period costs, depreciations, and book values for each asset¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/5g-depreciate%CE%BB ¶" &
"VERSION: →BXL: Nov 22 2025¶" &
"PARAMETERS: →¶" &
" Timeline →(Required) The model's timeline or a row of period start dates. ¶" &
" → Timeline can be in Months, Quarters, or Years¶" &
" EndDates? →(Optional) TRUE=Model's timeline displays period end dates. ¶" &
" → FALSE=Start dates. Default=TRUE¶" &
" AssetCosts →(Required) The acquisition cost 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¶" &
" SalvageValues →(Optional) The book values of each asset after fully depreciated. Default = 0¶" &
" DisposalDates →(Optional) The dates on which the AssetCount are removed from the books. Default = End of life¶" &
" NetDisposals →(Optional) The net of the asset sale less disposal costs. Default = 0¶" &
" Methods →(Optional) The desired depreciation method. Must be one of these Excel function names: ¶" &
"→SLN=Straight Line Depreciation (Default)¶" &
"→SYD=Sum of Year's Digits¶" &
"→DB =Declining Balance¶" &
"→DDB=Double Declining Balance¶" &
"→VDB=Variable Declining Balance (same as DDB)¶" &
" Factors →(Optional) Only applicable to DDB and VDB methods. Default = 2¶" &
"RESULTS: →This function produces the following lines for each asset¶" &
" Asset Cost →¶" &
" Opening Value →¶" &
"  Less Depreciation→¶" &
" Book Value →¶" &
" Net Disposal Sale/Cost→¶" &
" Gain/(Loss) →",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Timeline),
ISOMITTED( AssetCosts),
ISOMITTED( InServiceDates),
ISOMITTED( LifeInYears)
),
IF( Help?,
Help,
LET(
// Set Constants
MpY, 12,
AssetCount, ROWS( AssetCosts),
AssetCounter, SEQUENCE( AssetCount),
BlankLine, IF( Timeline, ""),
// Set optional argument defaults
SalvageValues, IF( ISOMITTED( SalvageValues),
IF( AssetCosts, 0),
MAP( SalvageValues, LAMBDA(v, IFERROR( VALUE( v), 0)))),
InServiceDates, IF( NOT( ISOMITTED( InServiceDates)),
IF( ISNUMBER( InServiceDates),
InServiceDates,
DATEVALUE( InServiceDates))),
DisposalDates, IF( ISOMITTED( DisposalDates),
EDATE( InServiceDates, LifeinYears * MpY),
MAP( AssetCounter,
LAMBDA( Asset,
IF( INDEX( DisposalDates, Asset) = "",
EDATE( INDEX( InServiceDates, Asset), INDEX(LifeinYears, Asset) * MpY),
INDEX( DisposalDates, Asset)
)
)
)
),
NetDisposals, IF( ISOMITTED( NetDisposals),
SalvageValues,
MAP( NetDisposals, SalvageValues,
LAMBDA(NetDisposal, SalvageValue,
IF( TRIM( NetDisposal) = "", SalvageValue, NetDisposal)))),
Methods, IF( ISOMITTED( Methods),
IF( AssetCosts, "SLN"),
MAP( Methods, LAMBDA(Method, IF( TRIM( Method) = "", "SLN", Method)))),
Factors, IF( ISOMITTED( Factors),
IF( AssetCosts, 2),
MAP( Factors, LAMBDA(Factor, IF( TRIM( Factor) = "", 2, Factor)))),
// Set up model timeline starts and ends
Timelines, DeriveTimelinesλ( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
// Process each asset,
Result, REDUCE( 0, AssetCounter,
LAMBDA( Block, Asset,
LET(
// Get individual asset's values
Cost, @INDEX( AssetCosts, Asset),
InService, @INDEX( InServiceDates, Asset),
Life, @INDEX( LifeInYears, Asset),
Disposal, @INDEX( DisposalDates, Asset),
Salvage, @INDEX( SalvageValues, Asset),
Net, @INDEX( NetDisposals, Asset),
Method, @INDEX( Methods, Asset),
Factor, @INDEX( Factors, Asset),
// Create asset's timeline
APeriods, DATEDIF(Inservice, Disposal, "M") + 1,
AStarts, EDATE( InService, SEQUENCE( , APeriods, 0)),
AEnds, EDATE( Inservice, SEQUENCE( , APeriods, 1)) - 1,
// Create timing flags and counters
LastPeriodFlag, N( AEnds >= Disposal),
DeprYears, SEQUENCE( , Life),
Prd2DeprYear, TAKE( QUOTIENT( SEQUENCE( , APeriods, 0), MpY) + 1, 1, APeriods),
// Calculate asset's annual depreciation
AnnualDepr, SWITCH(Method,
"SLN", SLNλ( Cost, Salvage, Life),
"SYD", SYDλ( Cost, Salvage, Life),
"DB", DBλ( Cost, Salvage, Life),
"DDB", DDBλ( Cost, Salvage, Life, Factor),
"VDB", VDBλ( Cost, Salvage, Life, Factor, FALSE)
),
// Create rows for each asset's period values
ADepr, TAKE( XLOOKUP( Prd2DeprYear, DeprYears, AnnualDepr / MpY, 0), 1, APeriods),
ASalvage, Salvage * LastPeriodFlag,
ABookValue, SCAN( Cost, -(ADepr + ASalvage), SUM),
AOpening, DROP( HSTACK( Cost, ABookValue), 0, -1),
ANet, Net * LastPeriodFlag,
AGainLoss, ( ANet - ABookValue) * LastPeriodFlag,
// Define function to aggregate asset periods by model periods
fnAggregate, LAMBDA( Values,
MAP( MStarts, MEnds,
LAMBDA( MStart, MEnd,
SUM(( MStart <= AEnds) * (MEnd >= AEnds) * Values)))),
// Aggregate asset's period values (monthly) under model's timeline
EffFlags, ( Inservice <= MEnds) * ( Disposal >= MStarts),
MCost, Cost * ( MStarts <= InService) * ( MEnds >= InService),
MOpening, XLOOKUP( MStarts, AStarts, AOpening, 0, 1) * EffFlags,
MDepr, fnAggregate( ADepr),
MSalvage, fnAggregate( ASalvage),
MBookValue, MOpening - MDepr - MSalvage,
MNet, fnAggregate( ANet),
MGainLoss, fnAggregate( AGainLoss),
// Create new assets block
NewBlock, VSTACK( MCost, MOpening, MDepr, MSalvage, MBookValue, MNet, MGainLoss),
// Add new asset block to prior blocks
Result, IF( Asset = 1,
NewBlock,
VSTACK( Block, BlankLine, NewBlock)),
Result
)
)
),
Result
)
)
)
);
/* FUNCTION NAME: DepreciateLabelsλ
DESCRIPTION:*//**Create labels for Depreciateλ*/
/* REVISIONS: Date Developer Description
Oct 24 2023 Craig Hatmaker Original Development
*/
DepreciateLabelsλ = LAMBDA(
// Parameter Declarations
[AssetNames],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →DepreciateLabelsλ( 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: Nov 22 2025¶" &
"PARAMETERS: →¶" &
"AssetNames → (Required) The names or descriptions of each asset in teh depreciation schedule produced by Depreciateλ.",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( AssetNames),
IF( Help?,
Help,
LET(
Result, MAKEARRAY( ROWS(AssetNames) * 8 - 1, 1,
LAMBDA(R, C,
LET(AssetName, INDEX( AssetNames, QUOTIENT(R-1, 8) + 1),
SWITCH( MOD(R-1, 8) + 1,
1, AssetName & " Asset Cost",
2, AssetName & " Opening Value",
3, AssetName & " Less Depreciation",
4, AssetName & " Less Salvage",
5, AssetName & " Book Value",
6, AssetName & " Net Disposal Sale/(Cost)",
7, AssetName & " Gain/(Loss)",
8, ""
)
)
)
),
Result
)
)
)
);
/* FUNCTION NAME: DepreciateSumsλ
DESCRIPTION:*//**Create row totals for Depreciateλ*/
/* REVISIONS: Date Developer Description
Dec 21 2023 Craig Hatmaker Original Development
*/
DepreciateSumsλ = LAMBDA(
// Parameter Declarations
[DepreciationSchedule],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →DepreciateSumsλ(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λ.",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( DepreciationSchedule),
// Procedure
Result, MAKEARRAY(ROWS(DepreciationSchedule), 1,
LAMBDA( R, C,
SWITCH( MOD( R, 8),
1, SUM(CHOOSEROWS( DepreciationSchedule, R)),
3, SUM(CHOOSEROWS( DepreciationSchedule, R)),
4, SUM(CHOOSEROWS( DepreciationSchedule, R)),
6, SUM(CHOOSEROWS( DepreciationSchedule, R)),
7, SUM(CHOOSEROWS( DepreciationSchedule, R)),
0
)
)
),
// Return Result
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.",
"→", "¶"
)
),
// 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.",
"→", "¶"
)
),
// 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.",
"→", "¶"
)
),
// 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.",
"→", "¶"
)
),
// 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.",
"→", "¶"
)
),
// 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.",
"→", "¶"
)
),
// 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)
)
);
// Sub-functions
/* 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.",
"→", "¶" )
),
// 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: 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).",
"→", "¶"
)
),
// 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.",
"→", "¶"
)
),
// 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: 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.",
"→", "¶"
)
),
// 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)
)
);
// Subroutines referenced in this library copied from other libraries
/* 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
May 16 2025 Craig Hatmaker Added D and W intervals
Jun 17 2025 Craig Hatmaker Efficiency improvements
*/
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: May 28 2025¶" &
"PARAMETERS:→¶" &
" ModelTimeline →(Required) Either the model's start or end timeline¶" &
" EndDates? →(Optional) TRUE (default) = The model's timeline displays period end dates.",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( ModelTimeline),
// Understand the model's timeline
// Set Constants
IntervalDays, {1;7;28;88;360},
IntervalPeriod, {"D";"W";"M";"Q";"Y"},
// Determine how many days are in model's periods
FirstPeriod, TAKE( ModelTimeline, , 1),
LastPeriod, TAKE( ModelTimeline, , -1),
DpP, INDEX( ModelTimeline, 2) - FirstPeriod,
// Set timeline dependent defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Interval, XLOOKUP( DpP, IntervalDays, IntervalPeriod, ,-1),
// Select interval formula
DateFunction, IF( OR( AND( NOT( EndDates?), DAY( FirstPeriod = 1)),
AND( EndDates?, DAY( FirstPeriod > 27))),
EOMONTH, EDATE),
// Calculate Start Dates
Starts, IF( EndDates?,
HSTACK(
SWITCH( Interval,
"D", FirstPeriod,
"W", FirstPeriod - 6,
"M", DateFunction( FirstPeriod, -2) + 2,
"Q", DateFunction( FirstPeriod, -4) + 2,
"Y", DateFunction( FirstPeriod, -14) + 2
),
DROP( ModelTimeline, , -1)
),
ModelTimeline
),
// Calculate End Dates
Ends, IF( EndDates?,
ModelTimeline,
HSTACK(
DROP( ModelTimeline, , 1) - 1,
SWITCH( Interval,
"D", LastPeriod,
"W", LastPeriod + 6,
"M", DateFunction( LastPeriod, 1) - 1,
"Q", DateFunction( LastPeriod, 3) - 1,
"Y", DateFunction( LastPeriod, 12) - 1
)
)
),
// Procedure
Result, VSTACK(Starts, Ends, IF( Starts, Interval)),
// Result
CHOOSE(Help? + 1, Result, Help)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment