Skip to content

Instantly share code, notes, and snippets.

@CHatmaker

CHatmaker/BXL 5g Functions LAMBDA Debt

Last active December 7, 2025 00:25
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save CHatmaker/28031fac3d3e91dc5997c80fd4a180ec to your computer and use it in GitHub Desktop.
Download ZIP
BXL 5g Functions LAMBDA Debt
Raw
BXL 5g Functions LAMBDA Debt
/* Module Contains 5g Compliant functions that deal with debt and loans. */
/* FUNCTION NAME: Aboutλ
DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/* REVISIONS: Date Developer Description
Nov 07 2025 Craig Hatmaker Copyright
Nov 18 2025 Craig Hatmaker Loan Label Adjustments
Nov 27 2025 Craig Hatmaker Added AdaptToTimelineλ
Nov 29 2025 Craig Hatmaker See DebtLabelsλ
Dec 06 2025 Craig Hatmaker See Installmentλ & AdaptToTimelineλ
*/
Aboutλ = TRIM(
TEXTSPLIT(
"About: →5g Compliant functions that deal with debt and loans. Suggested module name: BXL¶" &
"Version: →BXL: Dec 06 2025¶" &
"Gist URL: →https://gist.github.com/CHatmaker/28031fac3d3e91dc5997c80fd4a180ec ¶" &
"Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
"→¶" &
"Function →Description¶" &
" Aboutλ →Produces this table¶" &
" PMTAλ →Determine monthly payment amount for commercial loans¶" &
" GetBalanceλ →Determine debt with accrued interest when debt payments start¶" &
" TLAλ →Term Loan A Debt Schedule¶" &
" TLAActλ →Term Loan A Debt Schedule with Actuals¶" &
" TLBλ →Term Loan B Debt Schedule (Balance due at maturity)¶" &
" TLBActλ →Term Loan B Debt Schedule (Balance due at maturity) with Actuals¶" &
" Installmentλ →Monthly installment loan schedule where total payment remains constant.¶" &
" Installmentsλ →Process multiple monthly installment loans.¶" &
" DebtLabelsλ →Generate debt schedule line labels¶" &
"→¶" &
"SUB FUNCTIONS →Functions used by other functions or general purpose functions.¶" &
" DeriveTimelineλ →Derive other timeline (starts or ends) from model's timeline",
" AdaptToTimelineλ →Derive other timeline (starts or ends) from model's timeline",
"→","¶"
)
);
/* FUNCTION NAME: PMTAλ
DESCRIPTION:*//**Determine monthly payment amount for commercial loans*/
/* REVISIONS: Date Developer Description
Oct 23 2025 Craig Hatmaker Copyright
*/
PMTAλ = LAMBDA(
[APR],
[TermInMonths],
[DebtAmount],
[StartDate],
[DayCountConv],
[Tolerance],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →PMTAλ( APR, TermInMonths, DebtAmount, StartDate, DayCountConv, [Tolerance], [MaxIterations])¶" &
"DESCRIPTION: →Determine monthly payment amount for Actual/ (360, 365, or Actual)¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jul 03 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¶" &
" DayCountConv →(Optional) Select: 1=30/360, 2=Actual/360, 3=Actual/365, or 4=Actual/Actual. Default = 3.¶" &
" 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)
),
// Defaults
Tolerance, IF( ISOMITTED( Tolerance), .001, Tolerance),
DayCountConv, IF( OR( ISOMITTED( DayCountConv), DayCountConv=0), 3, DayCountConv),
// Pre Calculations
Counter, SEQUENCE( TermInMonths),
DatesArray, EOMONTH( StartDate, Counter - 1),
MonthDays, IF( DayCountConv = 1, IF( DatesArray, 30), DAY( DatesArray)),
LeapYears, MONTH( DATE( YEAR( DatesArray), 2, 29)) = 2,
YearDays, CHOOSE( DayCountConv,
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 +, guess too low. Increase lower bound
fn( fn, MidPayment, HiGuess),
// If balance 0 or -, guess too high or just right. Decrease upper bound
fn( fn, LoGuess, MidPayment)
)
)
)
),
// Run solver-like function to find equal monthly payments
Result, fnPMT( fnPMT, LoGuess, HiGuess),
// Result
IF( Help?, Help, Result)
)
);
/* FUNCTION NAME: GetBalanceλ
DESCRIPTION:*//**Determine debt with accrued interest when debt payments start*/
/* REVISIONS: Date Developer Description
Oct 23 2025 Craig Hatmaker Copyright
*/
GetBalanceλ = LAMBDA(
[APR],
[TermInMonths],
[DebtAmount],
[StartDate],
[AccrualMethod],
[Payment],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →GetBalanceλ( APR, TermInMonths, DebtAmount, StartDate, AccrualMethod, [Tolerance], [Payment])¶" &
"DESCRIPTION: →Determine month payment amount for Actual/ (360, 365, or Actual)¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jul 08 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 interest is applied¶" &
" AccrualMethod →(Required) Select: 1=30/360, 2=Actual/360, 3=Actual/365, or 4=Actual/Actual¶" &
" Payment →(Required) Amount applied each month to service the debt." ,
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( APR),
ISOMITTED( TermInMonths),
ISOMITTED( DebtAmount),
ISOMITTED( StartDate),
ISOMITTED( AccrualMethod),
ISOMITTED( Payment)
),
Result, IF( Help?,
Help,
LET(
// Pre Calculations
Counter, SEQUENCE( TermInMonths),
DatesArray, EOMONTH( StartDate, Counter - 1),
MonthDays, IF( AccrualMethod = 1, IF( DatesArray, 30), DAY( DatesArray)),
LeapYears, N( MONTH( DATE( YEAR( DatesArray), 2, 29)) = 2),
YearDays, CHOOSE( AccrualMethod,
IF( DatesArray, 360),
IF( DatesArray, 360),
IF( DatesArray, 365),
IF( DatesArray, 365) + LeapYears
),
Result, REDUCE( DebtAmount, Counter,
LAMBDA( Acc, n,
LET(
MDays, INDEX( MonthDays, n),
YDays, INDEX( YearDays, n),
Acc + Acc * APR / YDays * MDays - Payment
)
)
),
Result
)
),
Result
)
);
// Term loans - single
/* FUNCTION NAME: TLAλ
DESCRIPTION:*//**Create a Term Loan A debt schedule */
/* REVISIONS: Date Developer Description
Oct 23 2025 Craig Hatmaker Copyright
*/
TLAλ = LAMBDA(
[DrawAmount],
[DrawDate],
[Payments],
[APRs],
[DayCountConv],
[Timeline],
[AllPositive?],
[Average?],
LET(
// Help
Help, TRIM( TEXTSPLIT(
"FUNCTION: →TLAλ(DrawAmount, [DrawDate], Payments, APRs, [DayCountConv], ¶" &
"→ Timeline, [AllPositive?], [Average?])¶" &
"DESCRIPTION: →Create a debt schedule (No cash sweep)¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/<coming soon>¶" &
"VERSION: →BXL: Oct 20, 2025¶" &
"PARAMETERS: →¶" &
" DrawAmount →(Required) Opening Balance.¶" &
" DrawDate →(Optional) Date of draw. Default is the period before timeline.¶" &
"→ Payments start the period after draw date¶" &
" Payments →(Optional) One or more payment values. Default = 0.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last rate, set to 0 in the period after last payment.¶" &
" APRs →(Required) One or more annual interest rate percentages.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last value, set to 0 in the period after last payment.¶" &
" DayCountConv →(Optional) Determine the Day-count convention used for calculating interest.¶" &
"→ 1=30/360, 2=Actual/360, 3=Actual/365, 4=Actual/Actual. Default = 3 (Actual/365).¶" &
" Timeline →(Required) The model's timeline.¶" &
" AllPositive? →(Optional) Set to TRUE to make all values positive. Default = FALSE.¶" &
" Average? →(Optional) If TRUE, interest is applied to each period's open and close balance average.¶" &
"→ If FALSE, interest is applied to each period's open balance. Default = FALSE¶" &
"→ NOTE! Not applicable when timeline is in months.¶" &
"SUGGESTED LINE LABELS: →¶" &
" Use DebtLabelsλ",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(DrawAmount),
ISOMITTED(Payments),
ISOMITTED(APRs),
ISOMITTED(Timeline)
),
// Set Defaults
DrawDate, IF( ISOMITTED( DrawDate), 0, DrawDate),
Average?, IF( ISOMITTED( Average?), FALSE, Average?),
DayCountConv, IF( ISOMITTED( DayCountConv), 3, DayCountConv),
// Determine Day-Count Convention settings
FirstPrdDays, INDEX( Timeline, 2) - INDEX( Timeline, 1),
MonthInterval, XLOOKUP( FirstPrdDays, { 0, 90, 365}, { 1, 3, 12}, , -1),
Starts?, DAY( INDEX( Timeline, 1)) = 1,
MissingDate, IF( Starts?,
EOMONTH( TAKE( Timeline, 1, -1), MonthInterval -1),
EDATE( TAKE( Timeline, 1, 1), -MonthInterval ) +1
),
Starts, IF( Starts?,
Timeline,
HSTACK( MissingDate, DROP( Timeline + 1, 0, -1))
),
Ends, IF( Starts?,
HSTACK( DROP( Timeline - 1, 0, 1), MissingDate),
Timeline
),
PeriodDays, IF( DayCountConv = 1,
IF( Timeline, MonthInterval * 30),
Ends - Starts + 1
),
YearDays, CHOOSE( DayCountConv,
IF( Timeline, 360),
IF( Timeline, 360),
IF( Timeline, 365),
DATE( YEAR( Starts), 12, 31) - DATE( YEAR( Starts), 1, 1) + 1
),
// Set period counters
Count, COLUMNS( Timeline),
Periods, SEQUENCE( , Count),
// Convert single values to a row
Payments, IF( COLUMNS( Payments) = 1,
IF(Timeline, Payments),
EXPAND(Payments, 1, Count, TAKE( Payments, -1, -1))
),
APRs, IF( COLUMNS( APRs) = 1,
IF(Timeline, APRs),
EXPAND( APRs, 1, Count, TAKE( APRs, -1, -1))
),
Rates, APRs * PeriodDays / YearDays,
// Configure ColumnStore. Load each period's values into an array of ColumnStores
ColumnStore, LAMBDA( X, LAMBDA( X)),
ArrayStore, BYCOL( VSTACK( Starts, Ends, Periods, Payments), ColumnStore),
// Procedure
OpenBal, IF( DrawDate = 0, DrawAmount, 0),
Close, SCAN( OpenBal, ArrayStore,
LAMBDA( Acc, ArrayColumn,
LET(
Start, INDEX( ArrayColumn(), 1),
End, INDEX( ArrayColumn(), 2),
Period, INDEX( ArrayColumn(), 3),
Payment, INDEX( ArrayColumn(), 4),
Result, IF( AND(DrawDate >= Start, DrawDate <=End),
DrawAmount,
MAX( Acc - Payment, 0)
),
Result
)
)
),
Open, TAKE( HSTACK( OpenBal, Close), 1, Count),
Flow, Close - Open,
Draws, Flow * (Flow > 0),
Pmts, IF( AllPositive?, -1, 1) * Flow * (Flow < 0),
IntBal, IF( AND(Average?, MonthInterval>1),
BYCOL( VSTACK( Open, Close), AVERAGE) * (Open <> 0),
Open
),
Interest, IntBal * Rates * (Open <> 0),
BlankLine, IF( Timeline, ""),
Effective, IF( Open, 1),
Result, VSTACK(
Open, Draws, Pmts, Close,
BlankLine,
PeriodDays * Effective, YearDays * Effective,
BlankLine,
IntBal, Rates * (Open > 0), Interest),
// Return Result
IF(Help?, Help, Result)
)
);
/* FUNCTION NAME: TLAActλ
DESCRIPTION:*//**Create a Term Loan A with Actuals debt schedule (No cash sweep)*/
/* REVISIONS: Date Developer Description
Oct 23 2025 Craig Hatmaker Copyright
*/
TLAActλ = LAMBDA(
[Actuals],
[DrawAmount],
[DrawDate],
[Payments],
[APRs],
[DayCountConv],
[Timeline],
[AllPositive?],
[Average?],
LET(
// Help
Help, TRIM( TEXTSPLIT(
"FUNCTION: →TLAActλ(Actuals, [DrawAmount], [DrawDate], [Payments], APRs, ¶" &
"→ [DayCountConv], Timeline, [AllPositive?], [Average?])¶" &
"DESCRIPTION: →Create a Term Loan A with Actuals debt schedule (No cash sweep)¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/<coming soon>¶" &
"VERSION: →BXL: Oct 11, 2025¶" &
"PARAMETERS: →¶" &
" Actuals →(Required) One or more closing period balances. ¶" &
"→ The number of values sent determine actual periods.¶" &
" DrawAmount →(Optional) Opening Balance. Default = 0 (draw comes from Actuals).¶" &
" DrawDate →(Optional) Date of draw. Default is the period before timeline.¶" &
"→ Payments start the period after draw date¶" &
" Payments →(Optional) One or more payment values. Default = 0.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last rate, set to 0 in the period after last payment.¶" &
" APRs →(Required) One or more annual interest rate percentages.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last value, set to 0 in the period after last payment.¶" &
" DayCountConv →(Optional) Determine the Day-count convention used for calculating interest.¶" &
"→ 1=30/360, 2=Actual/360, 3=Actual/365, 4=Actual/Actual. Default = 3 (Actual/365).¶" &
" Timeline →(Required) The model's timeline.¶" &
" AllPositive? →(Optional) Set to TRUE to make all values positive. Default = FALSE.¶" &
" Average? →(Optional) If TRUE, interest is applied to each period's open and close balance average.¶" &
"→ If FALSE, interest is applied to each period's open balance. Default = FALSE¶" &
"→ NOTE! Not applicable when timeline is in months.¶" &
"SUGGESTED LINE LABELS: →¶" &
" Use DebtLabelsλ",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(Actuals),
ISOMITTED(APRs),
ISOMITTED(Timeline)
),
// Set Defaults
Payments, IF( ISOMITTED( Payments), 0, Payments),
DayCountConv, IF( ISOMITTED( DayCountConv), 3, DayCountConv),
Average?, IF( ISOMITTED( Average?), FALSE, Average?),
AllPositive?, IF( ISOMITTED( AllPositive?), FALSE, AllPositive?),
// Determine Day-Count Convention settings
FirstPrdDays, INDEX( Timeline, 2) - INDEX( Timeline, 1),
MonthInterval, XLOOKUP( FirstPrdDays, { 0, 90, 365}, { 1, 3, 12}, , -1),
Starts?, DAY( INDEX( Timeline, 1)) = 1,
MissingDate, IF( Starts?,
EOMONTH( TAKE( Timeline, 1, -1), MonthInterval -1),
EDATE( TAKE( Timeline, 1, 1), -MonthInterval ) +1
),
Starts, IF( Starts?,
Timeline,
HSTACK( MissingDate, DROP( Timeline + 1, 0, -1))
),
Ends, IF( Starts?,
HSTACK( DROP( Timeline - 1, 0, 1), MissingDate),
Timeline
),
PeriodDays, IF( DayCountConv = 1,
IF( Timeline, MonthInterval * 30),
Ends - Starts + 1
),
YearDays, CHOOSE( DayCountConv,
IF( Timeline, 360),
IF( Timeline, 360),
IF( Timeline, 365),
DATE( YEAR( Starts), 12, 31) - DATE( YEAR( Starts), 1, 1) + 1
),
// Set period and Actuals counters
Count, COLUMNS( Timeline),
Periods, SEQUENCE( , Count),
ActualsCount, COLUMNS( Actuals),
// Convert single values or incomplete rows to complete rows
Actuals, EXPAND( Actuals, 1, Count, 0),
PmtRow, IF( COLUMNS( Payments) = 1,
IF(Timeline, Payments),
EXPAND(Payments, 1, Count, TAKE( Payments, -1, -1))
),
APRs, IF( COLUMNS( APRs) = 1,
IF(Timeline, APRs),
EXPAND( APRs, 1, Count, TAKE( APRs, -1, -1))
),
Rates, APRs * PeriodDays / YearDays,
// Configure ColumnStore. Load each period's values into an array of ColumnStores
ColumnStore, LAMBDA( X, LAMBDA( X)),
ArrayStore, BYCOL( VSTACK( Starts, Ends, Periods, Actuals, PmtRow), ColumnStore),
// Procedure
Close, SCAN( 0, ArrayStore,
LAMBDA( Acc, ArrayColumn,
LET(
Start, INDEX( ArrayColumn(), 1),
End, INDEX( ArrayColumn(), 2),
Period, INDEX( ArrayColumn(), 3),
Actual, INDEX( ArrayColumn(), 4),
Payment, INDEX( ArrayColumn(), 5),
Result, IF( Period <= ActualsCount,
Actual,
if( AND(DrawDate >= Start, DrawDate <=End),
Acc + DrawAmount,
MAX( Acc - Payment, 0)
)
),
Result
)
)
),
Open, TAKE( HSTACK( INDEX( Actuals, 1), Close), 1, Count),
Flow, Close - Open,
Draws, Flow * (Flow > 0),
Pmts, IF( AllPositive?, -1, 1) * Flow * (Flow < 0),
IntBal, IF( AND(Average?, MonthInterval>1),
BYCOL( VSTACK( Open, Close), AVERAGE) * (Open <> 0),
Open
),
Interest, IntBal * Rates * (Open <> 0),
BlankLine, IF( Timeline, ""),
Effective, IF( Open, 1),
Result, VSTACK(
Open, Draws, Pmts, Close,
BlankLine,
PeriodDays * Effective, YearDays * Effective,
BlankLine,
IntBal, Rates * (Open > 0), Interest),
// Return Result
IF(Help?, Help, Result)
)
);
/* FUNCTION NAME: TLBλ
DESCRIPTION:*//**Create a Term Loan B debt schedule (balance due at maturity)*/
/* REVISIONS: Date Developer Description
Oct 23 2025 Craig Hatmaker Copyright
*/
TLBλ = LAMBDA(
[DrawAmount],
[DrawDate],
[MaturityDate],
[Payments],
[APRs],
[DayCountConv],
[Timeline],
[AllPositive?],
[Average?],
LET(
// Help
Help, TRIM( TEXTSPLIT(
"FUNCTION: →TLBλ( DrawAmount, [DrawDate], MaturityDate, [Payments], APRs, )¶" &
"→ [DayCountConv], Timeline, [AllPositive], [Average?])¶" &
"DESCRIPTION: →Create a Term Loan B debt schedule (balance due at maturity)¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/<coming soon>¶" &
"VERSION: →BXL: Oct 20, 2025¶" &
"PARAMETERS: →¶" &
" DrawAmount →(Required) Opening Balance.¶" &
" DrawDate →(Optional) Date of draw. Default is the period before timeline.¶" &
"→ Payments start the period after draw date¶" &
" MaturityDate →(Required) Date when full repayment is required.¶" &
"→ Payments start the period after draw date¶" &
" Payments →(Optional) One or more payment values. Default = 0.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last rate, set to 0 in the period after last payment.¶" &
" APRs →(Required) One or more annual interest rate percentages.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last value, set to 0 in the period after last payment.¶" &
" DayCountConv →(Optional) Determine the Day-count convention used for calculating interest.¶" &
"→ 1=30/360, 2=Actual/360, 3=Actual/365, 4=Actual/Actual. Default = 3 (Actual/365).¶" &
" Timeline →(Required) The model's timeline.¶" &
" AllPositive? →(Optional) Set to TRUE to make all values positive. Default = FALSE.¶" &
" Average? →(Optional) If TRUE, interest is applied to each period's open and close balance average.¶" &
"→ If FALSE, interest is applied to each period's open balance. Default = FALSE¶" &
"→ NOTE! Not applicable when timeline is in months.¶" &
"SUGGESTED LINE LABELS: →¶" &
" Use DebtLabelsλ",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(DrawAmount),
ISOMITTED(MaturityDate),
ISOMITTED(APRs),
ISOMITTED(Timeline)
),
// Set Defaults
DrawDate, IF( ISOMITTED( DrawDate), 0, DrawDate),
Payments, IF( ISOMITTED( Payments), 0, Payments),
DayCountConv, IF( ISOMITTED( DayCountConv), 3, DayCountConv),
Average?, IF( ISOMITTED( Average?), FALSE, Average?),
AllPositive?, IF( ISOMITTED( AllPositive?), FALSE, AllPositive?),
// Determine Day-Count Convention settings
FirstPrdDays, INDEX( Timeline, 2) - INDEX( Timeline, 1),
MonthInterval, XLOOKUP( FirstPrdDays, { 0, 90, 365}, { 1, 3, 12}, , -1),
Starts?, DAY( INDEX( Timeline, 1)) = 1,
MissingDate, IF( Starts?,
EOMONTH( TAKE( Timeline, 1, -1), MonthInterval -1),
EDATE( TAKE( Timeline, 1, 1), -MonthInterval ) +1
),
Starts, IF( Starts?,
Timeline,
HSTACK( MissingDate, DROP( Timeline + 1, 0, -1))
),
Ends, IF( Starts?,
HSTACK( DROP( Timeline - 1, 0, 1), MissingDate),
Timeline
),
PeriodDays, IF( DayCountConv = 1,
IF( Timeline, MonthInterval * 30),
Ends - Starts + 1
),
YearDays, CHOOSE( DayCountConv,
IF( Timeline, 360),
IF( Timeline, 360),
IF( Timeline, 365),
DATE( YEAR( Starts), 12, 31) - DATE( YEAR( Starts), 1, 1) + 1
),
// Set period counters
Count, COLUMNS( Timeline),
Periods, SEQUENCE( , Count),
// Convert single values or incomplete rows to complete rows
PmtRow, IF( COLUMNS( Payments) = 1,
IF(Timeline, Payments),
EXPAND(Payments, 1, Count, TAKE( Payments, -1, -1))
),
APRs, IF( COLUMNS( APRs) = 1,
IF(Timeline, APRs),
EXPAND( APRs, 1, Count, TAKE( APRs, -1, -1))
),
Rates, APRs * PeriodDays / YearDays,
// Configure ColumnStore then load each period's column into a storage array: ArrayStore
ColumnStore, LAMBDA( X, LAMBDA( X)),
ArrayStore, BYCOL( VSTACK( Starts, Ends, Periods, PmtRow), ColumnStore),
// Procedure
OpenBal, IF( DrawDate = 0, DrawAmount, 0),
Close, SCAN( OpenBal, ArrayStore,
LAMBDA( Acc, ArrayColumn,
LET(
Start, INDEX( ArrayColumn(), 1),
End, INDEX( ArrayColumn(), 2),
Period, INDEX( ArrayColumn(), 3),
Payment, INDEX( ArrayColumn(), 4),
Result, IF( AND(DrawDate >= Start, DrawDate <=End),
DrawAmount,
IF( AND(MaturityDate >= Start, MaturityDate <=End),
0,
MAX( Acc - Payment, 0)
)
),
Result
)
)
),
Open, TAKE( HSTACK( OpenBal, Close), 1, Count),
Flow, Close - Open,
Draws, Flow * (Flow > 0),
Pmts, IF( AllPositive?, -1, 1) * Flow * (Flow < 0),
IntBal, IF( AND(Average?, MonthInterval>1),
BYCOL( VSTACK( Open, Close), AVERAGE) * (Open <> 0),
Open
),
Interest, IntBal * Rates * (Open <> 0),
BlankLine, IF( Timeline, ""),
Effective, IF( Open, 1),
Result, VSTACK(
Open, Draws, Pmts, Close,
BlankLine,
PeriodDays * Effective, YearDays * Effective,
BlankLine,
IntBal, Rates * (Open > 0), Interest),
// Return Result
IF(Help?, Help, Result)
)
);
/* FUNCTION NAME: TLBActλ
DESCRIPTION:*//**Create a Term Loan B debt schedule (balance due at maturity) with Actuals*/
/* REVISIONS: Date Developer Description
Oct 23 2025 Craig Hatmaker Copyright
*/
TLBActλ = LAMBDA(
[Actuals],
[DrawAmount],
[DrawDate],
[MaturityDate],
[Payments],
[APRs],
[DayCountConv],
[Timeline],
[AllPositive?],
[Average?],
LET(
// Help
Help, TRIM( TEXTSPLIT(
"FUNCTION: →TLBActλ(Actuals, [DrawAmount], [DrawDate], MaturityDate, [Payments], APRs, ¶" &
"→ [DayCountConv], Timeline, [AllPositive?], [Average?])¶" &
"DESCRIPTION: →Create a Term Loan A with Actuals debt schedule (No cash sweep)¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/<coming soon>¶" &
"VERSION: →BXL: Oct 11, 2025¶" &
"PARAMETERS: →¶" &
" Actuals →(Required) One or more closing period balances. ¶" &
"→ The number of values sent determine actual periods.¶" &
" DrawAmount →(Required) Opening Balance.¶" &
" DrawDate →(Optional) Date of draw. Default is the period before timeline.¶" &
"→ Payments start the period after draw date¶" &
" MaturityDate →(Required) Date when full repayment is required.¶" &
" Payments →(Optional) One or more payment values. Default = 0.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last rate, set to 0 in the period after last payment.¶" &
" APRs →(Required) One or more annual interest rate percentages.¶" &
"→ The last value specified will be applied to all remaining periods.¶" &
"→ To stop applying last value, set to 0 in the period after last payment.¶" &
" DayCountConv →(Optional) Determine the Day-count convention used for calculating interest.¶" &
"→ 1=30/360, 2=Actual/360, 3=Actual/365, 4=Actual/Actual. Default = 3 (Actual/365).¶" &
" Timeline →(Required) The model's timeline.¶" &
" AllPositive? →(Optional) Set to TRUE to make all values positive. Default = FALSE.¶" &
" Average? →(Optional) If TRUE, interest is applied to each period's open and close balance average.¶" &
"→ If FALSE, interest is applied to each period's open balance. Default = FALSE¶" &
"→ NOTE! Not applicable when timeline is in months.¶" &
"SUGGESTED LINE LABELS: →¶" &
" Use DebtLabelsλ",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(Actuals),
ISOMITTED(MaturityDate),
ISOMITTED(APRs),
ISOMITTED(Timeline)
),
Result, IF( Help?,
Help,
LET(
// Set Defaults
Payments, IF( ISOMITTED( Payments), 0, Payments),
DayCountConv, IF( ISOMITTED( DayCountConv), 3, DayCountConv),
Average?, IF( ISOMITTED( Average?), FALSE, Average?),
AllPositive?, IF( ISOMITTED( AllPositive?), FALSE, AllPositive?),
// Determine Day-Count Convention settings
FirstPrdDays, INDEX( Timeline, 2) - INDEX( Timeline, 1),
MonthInterval, XLOOKUP( FirstPrdDays, { 0, 90, 365}, { 1, 3, 12}, , -1),
Starts?, DAY( INDEX( Timeline, 1)) = 1,
MissingDate, IF( Starts?,
EOMONTH( TAKE( Timeline, 1, -1), MonthInterval -1),
EDATE( TAKE( Timeline, 1, 1), -MonthInterval ) +1
),
Starts, IF( Starts?,
Timeline,
HSTACK( MissingDate, DROP( Timeline + 1, 0, -1))
),
Ends, IF( Starts?,
HSTACK( DROP( Timeline - 1, 0, 1), MissingDate),
Timeline
),
PeriodDays, IF( DayCountConv = 1,
IF( Timeline, MonthInterval * 30),
Ends - Starts + 1
),
YearDays, CHOOSE( DayCountConv,
IF( Timeline, 360),
IF( Timeline, 360),
IF( Timeline, 365),
DATE( YEAR( Starts), 12, 31) - DATE( YEAR( Starts), 1, 1) + 1
),
// Set period and Actuals counters
Count, COLUMNS( Timeline),
Periods, SEQUENCE( , Count),
ActualsCount, COLUMNS( Actuals),
// Convert single values or incomplete rows to complete rows
Actuals, EXPAND( Actuals, 1, Count, 0),
PmtRow, IF( COLUMNS( Payments) = 1,
IF(Timeline, Payments),
EXPAND(Payments, 1, Count, TAKE( Payments, -1, -1))
),
APRs, IF( COLUMNS( APRs) = 1,
IF(Timeline, APRs),
EXPAND( APRs, 1, Count, TAKE( APRs, -1, -1))
),
Rates, APRs * PeriodDays / YearDays,
// Configure ColumnStore. Load each period's values into an array of ColumnStores
ColumnStore, LAMBDA( X, LAMBDA( X)),
ArrayStore, BYCOL( VSTACK( Starts, Ends, Periods, Actuals, PmtRow), ColumnStore),
// Procedure
Closes, SCAN( 0, ArrayStore,
LAMBDA( Acc, ArrayColumn,
LET(
Start, INDEX( ArrayColumn(), 1),
End, INDEX( ArrayColumn(), 2),
Period, INDEX( ArrayColumn(), 3),
Actual, INDEX( ArrayColumn(), 4),
Payment, INDEX( ArrayColumn(), 5),
Result, IF( Period <= ActualsCount,
Actual,
if( AND(DrawDate >= Start, DrawDate <=End),
Acc + DrawAmount,
IF( AND(MaturityDate >= Start, MaturityDate <=End),
0,
MAX( Acc - Payment, 0)
)
)
),
Result
)
)
),
Opens, TAKE( HSTACK( INDEX( Actuals, 1), Closes), 1, Count),
Flow, Closes - Opens,
Draws, Flow * (Flow > 0),
Pmts, IF( AllPositive?, -1, 1) * Flow * (Flow < 0),
IntBal, IF( AND(Average?, MonthInterval>1),
BYCOL( VSTACK( Opens, Closes), AVERAGE) * (Opens <> 0),
Opens
),
Interest, IntBal * Rates * (Opens <> 0),
BlankLine, IF( Timeline, ""),
Effective, IF( Opens, 1),
Result, VSTACK(
Opens, Draws, IF( AllPositive?, Pmts, -Pmts) , Closes,
BlankLine,
PeriodDays * Effective, YearDays * Effective,
BlankLine,
IntBal, Rates * Effective, Interest, IF( AllPositive?, Interest, -Interest),
BlankLine,
Pmts + Interest
),
Result
)
),
Result
)
);
/* FUNCTION NAME: Installmentλ
DESCRIPTION:*//**Monthly installment loan schedule where total payment remains constant.*/
/* REVISIONS: Date Developer Description
Oct 23 2025 Craig Hatmaker Copyright
Nov 26 2025 Craig Hatmaker Adapt to varying model timelines
Dec 04 2025 Craig Hatmaker Inline Help changes
*/
Installmentλ = LAMBDA(
[Timeline],
[EndDates?],
[DrawAmount],
[DrawDate],
[APR],
[TermInMonths],
[DayCountConv],
[StartIntChg],
[StartIntPmt],
[StartDebtPmt],
[AllPositive?],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Installmentλ( Timeline, [EndDates?], DrawAmount, DrawDate, ¶" &
"→ APR, TermInMonths, [DayCountConv], [StartIntChg], [StartIntPmt], , ¶" &
"→ [StartDebtRepmt], [AllPositive?], [Average?]) ¶" &
"DESCRIPTION: →Monthly installment loan schedule where total payment remains constant. ¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Dec 06 2025¶" &
"PARAMETERS: →¶" &
" Timeline →(Required) The model's timeline. Timeline can be in Months, Quarters, or Years¶" &
" EndDates? →(Optional) TRUE (Default): Timeline displays period end dates. FALSE: Start dates.¶" &
" DrawAmount →(Required) Opening Balance.¶" &
" DrawDate →(Required) Date of draw.¶" &
" APR →(Required) A single annual interest rate percentage.¶" &
" TermInMonths →(Required) How many months to repay the debt.¶" &
" DayCountConv →(Optional) Determine the Day-count convention used for calculating interest.¶" &
"→ 1=30/360, 2=Actual/360, 3=Actual/365, 4=Actual/Actual. Default = 3 (Actual/365).¶" &
" StartIntChg →(Optional) When interest charges start.Default = DrawDate + 1 Month¶" &
" StartIntPmt →(Optional) When current month's interest payments start. Unpaid interest is¶" &
"→accrued and applied to debt balance. Default = Start Interest Charges¶" &
" StartDebtPmt →(Optional) When Payment on draw plus accrued interest starts.¶" &
"→Default = Interst Payment Start¶" &
" AllPositive? →(Optional) Set to TRUE to make all values positive. Default = FALSE.¶" &
"SUGGESTED LINE LABELS: →¶" &
"(Loan label)→¶" &
" Period days→¶" &
" Year days→¶" &
" (APR) x period days/year days→¶" &
" Draw→¶" &
" Debt balance open→¶" &
"  Plus draw→¶" &
"  Less repayment→¶" &
" Debt balance close→¶" &
" →¶" &
" Interest balance open→¶" &
"  Plus interest charge→¶" &
"  Less interest payment→¶" &
" Interest balance close→",
"→", "¶")
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Timeline),
ISOMITTED( DrawAmount),
ISOMITTED( DrawDate),
ISOMITTED( APR),
ISOMITTED( TermInMonths)
),
Result, IF( Help?,
Help,
LET(
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
DayCountConv, IF( ISOMITTED( DayCountConv), 3, DayCountConv),
StartIntChg, IF( ISOMITTED( StartIntChg), EDATE( DrawDate, 1), StartIntChg),
StartIntPmt, IF( ISOMITTED( StartIntPmt), StartIntChg, StartIntPmt),
StartDebtPmt, IF( ISOMITTED( StartDebtPmt), StartIntPmt, StartDebtPmt),
AllPositive?, IF( OR( ISOMITTED( AllPositive?), AllPositive?="") , FALSE, AllPositive?),
// Set up Loan timeline starts and ends
LStarts, EDATE( DrawDate, SEQUENCE( , TermInMonths, 0)),
LEnds, EDATE( DrawDate, SEQUENCE( , TermInMonths, 1)) - 1,
// Setup timing Flags
ChgIntFlags, N( StartIntChg <=LStarts),
PayIntFlags, N( StartIntPmt <= LStarts),
PayDebtFlags, N( StartDebtPmt <= LStarts),
// Set up Day Count Convention days
APRs, IF( LStarts, APR),
PeriodDays, IF( DayCountConv = 1,
IF( LStarts, 30),
LEnds - LStarts + 1),
YearDays, CHOOSE( DayCountConv,
IF( LStarts, 360),
IF( LStarts, 360),
IF( LStarts, 365),
DATE( YEAR( LStarts), 12, 31) - DATE( YEAR( LStarts), 1, 1) + 1
),
Rates, ChgIntFlags * APR * PeriodDays / YearDays,
// Determine Payment Amount
PmtCount, SUM( PayDebtFlags),
MoDiff, TermInMonths - PmtCount,
StartPmtBal, IF( MoDiff < 1,
DrawAmount,
REDUCE( DrawAmount, SEQUENCE( , MoDiff),
LAMBDA( Acc, n, Acc + Acc * INDEX( Rates, n)))),
Payment, PMTAλ( APR, PmtCount, StartPmtBal, StartDebtPmt, DayCountConv),
Payments, PayDebtFlags * Payment,
// Procedure
Counter, SEQUENCE( ,TermInMonths),
// Create loan values based on loan's monthly timeline
LResult, REDUCE( 0, Counter,
LAMBDA( Acc, Period,
LET(
Draw, IF( Period = 1, DrawAmount, 0),
DebtOpen, IF( Period = 1, 0, INDEX( Acc, 4, Period -1 )),
Rate, INDEX( Rates, Period),
IntChg, DebtOpen * Rate,
TotPmt, MIN( DebtOpen + IntChg, INDEX( Payments, Period)),
Principal, TotPmt - IntChg,
PayIntFlag, INDEX( PayIntFlags, Period),
DebtClose, DebtOpen + Draw - Principal,
IntOpen, IF( Period = 1, 0, TAKE( Acc, -1, -1)),
IntPmt, (IntOpen + IntChg) * PayIntFlag,
IntClose, IntOpen + IntChg - IntPmt,
Block, VSTACK(
DebtOpen, Draw, IF( AllPositive?, Principal, -Principal), DebtClose, "",
IntOpen, IntChg, IF( AllPositive?, IntPmt, -IntPmt), IntClose),
Result, IF( Period = 1, Block, HSTACK( Acc, Block)),
Result
)
)
),
// Adapt to timeline
Timelines, DeriveTimelinesλ( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
MPeriod, TAKE( Timelines, -1, 1),
BlankLine, IF( PeriodDays, ""),
Values, VSTACK( BlankLine, PeriodDays, YearDays, Rates, BlankLine, lResult, BlankLine),
AggrTypes, {"Spc"; "Sum"; "ANZ"; "Sum"; "Spc";
"Opn"; "Sum"; "Sum"; "Cls"; "Spc";
"Opn"; "Sum"; "Sum"; "Cls"; "Spc"},
Result, AdaptToTimelineλ( Values, AggrTypes, LStarts, LEnds, MStarts, Mends),
Result
)
),
Result
)
);
// Term loans - multiples
/* FUNCTION NAME: Installmentsλ
DESCRIPTION:*//**Process multiple monthly installment loan schedules.*/
/* REVISIONS: Date Developer Description
Nov 07 2025 Craig Hatmaker Copyright
Nov 24 2025 Craig Hatmaker Added varying timelines support
Dec 06 2025 Craig Hatmaker Inline Help changes
*/
Installmentsλ = LAMBDA(
[Timeline],
[EndDates?],
[DrawAmounts],
[DrawDates],
[APRs],
[TermsInMonths],
[DayCountConvs],
[StartIntChgs],
[StartIntPmts],
[StartDebtPmts],
[AllPositive?],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →Installmentsλ( Timeline, [EndDates?], DrawAmounts, DrawDates, ¶" &
"→ APRs, TermInMonths, [DayCountConvs], [StartIntChgs], [StartIntPmts], ¶" &
"→ [StartDebtPmts], [AllPositive?]) ¶" &
"DESCRIPTION: →Process multiple monthly installment loan schedules.¶" &
"→ See Installmentλ website or inline help for more information.¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Dec 06 2025¶" &
"PARAMETERS: →¶" &
" Timeline →(Required) The model's timeline. Timeline can be in Months, Quarters, or Years¶" &
" EndDates? →(Optional) TRUE (Default): Timeline displays period end dates. FALSE: Start dates.¶" &
" DrawAmounts →(Required) Amounts drawn.¶" &
" DrawDate →(Required) Dates drawn.¶" &
" APRs →(Required) Interest rate percentages.¶" &
" TermsInMonths →(Required) How many months to repay the debt.¶" &
" DayCountConvs →(Optional) Conventions used for calculating interest.¶" &
"→ 1=30/360, 2=Actual/360, 3=Actual/365, 4=Actual/Actual. Default = 3 (Actual/365).¶" &
" StartIntChgs →(Optional) Dates interest charges start. Default = DrawDate + 1 Month¶" &
" StartIntPmts →(Optional) Dates interest payments start. Unpaid interest is¶" &
"→accrued and applied to debt balance. Default = Start Interest Charges¶" &
" StartDebtPmts →(Optional) Date Repayment on draw starts.¶" &
"→Default = Interst Payment Start¶" &
" AllPositive? →(Optional) Set to TRUE to make all values positive. Default = FALSE.¶" &
"SUGGESTED LINE LABELS: →¶" &
"(Loan label)→¶" &
" Period days→¶" &
" Year days→¶" &
" (APR) x period days/year days→¶" &
" Draw→¶" &
" Debt balance open→¶" &
"  Plus draw→¶" &
"  Less repayment→¶" &
" Debt balance close→¶" &
" →¶" &
" Interest balance open→¶" &
"  Plus interest charge→¶" &
"  Less interest payment→¶" &
" Interest balance close→",
"→", "¶")
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Timeline),
ISOMITTED( DrawAmounts),
ISOMITTED( APRs),
ISOMITTED( TermsInMonths)
),
Result, IF( Help?,
Help,
LET(
// Set Defaults
EndDates?, IF( OR( ISOMITTED( EndDates?), EndDates? = ""),
IF( DrawAmounts, TRUE),
EndDates?),
DayCountConvs, IF( OR( ISOMITTED( DayCountConvs), DayCountConvs = ""),
IF( DrawAmounts, 3),
DayCountConvs),
StartIntChgs, IF( OR( ISOMITTED( StartIntChgs), StartIntChgs = ""),
IF( DrawAmounts, EDATE( +DrawDates, 1)),
StartIntChgs),
StartIntPmts, IF( OR( ISOMITTED( StartIntPmts), StartIntPmts = ""),
IF( DrawAmounts, StartIntChgs),
StartIntChgs),
StartDebtPmts, IF( OR( ISOMITTED( StartDebtPmts), StartDebtPmts = ""),
IF( DrawAmounts, StartIntPmts),
StartDebtPmts),
AllPositive?, IF( OR( ISOMITTED( AllPositive?), AllPositive? = ""),
IF( DrawAmounts, FALSE),
AllPositive?),
BlankLine, IF( Timeline , ""),
// Setup Counter
Count, ROWS( TOCOL( APRs)),
Counter, SEQUENCE( Count),
// Process each Loan
Result, REDUCE( 0, Counter,
LAMBDA( Acc, n,
LET(
Draw, INDEX( DrawAmounts, n),
Date, INDEX( DrawDates, n),
Apr, INDEX( APRs, n),
Term, INDEX( TermsInMonths, n),
DCC, INDEX( DayCountConvs, n),
Pos, INDEX( AllPositive?, n),
StrIntchg, INDEX( StartIntchgs, n),
StrIntPmt, INDEX( StartIntPmts, n),
StrDebtPmt, INDEX( StartDebtPmts, n),
Loan, Installmentλ( Timeline, EndDates?, Draw, Date, APR, Term, DCC,
StrIntchg, StrIntPmt, StrDebtPmt, Pos),
Result, IF(n = 1, Loan, VSTACK( Acc, Loan)),
Result
)
)
),
Result
)
),
Result
)
);
/* FUNCTION NAME: DebtLabelsλ
DESCRIPTION:*//**Generate debt schedule line labels.*/
/* REVISIONS: Date Developer Description
Nov 18 2025 Craig Hatmaker Copyright
Nov 29 2025 Craig Hatmaker Added loan label
*/
DebtLabelsλ = LAMBDA(
[Labels],
[APRs],
[Currency],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →DebtLabelsλ( [Labels], APRs, [Currencies]) ¶" &
"DESCRIPTION: →Creates line labels for 5g Debt functions. ¶" &
"→ See Installmentλ website or inline help for more information.¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Nov 18 2025¶" &
"PARAMETERS: →¶" &
" Labels →(Optional) A label for each loan. Numbers will be used if omitted.¶" &
" APRs →(Required) Annual interest rate percentages for each loan.¶" &
" Currency →(Optional) Currency label for all loans. Default=USD",
"→", "¶")
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( APRs),
Result, IF( Help?,
Help,
LET(
// Set Defaults
Labels, IF( ISOMITTED( Labels), "Loan " & SEQUENCE( ROWS( APRs)), Labels),
Currency, IF( ISOMITTED( Currency), "USD", Currency),
Curs, IF( APRs, Currency),
// Prepare variables
BlankLine, {"", "", ""},
Count, ROWS( TOCOL( APRs)),
Counter, SEQUENCE( Count),
// Process
Result, REDUCE( "", Counter,
LAMBDA( Acc, n,
LET(
Cur, INDEX( Curs, n),
Label, INDEX( Labels, n),
Apr, INDEX( APRs, n),
Block, VSTACK(
HSTACK( Label, "", ""),
HSTACK( "Period days", "", "days"),
HSTACK( "Year days", "", "days"),
HSTACK( TEXT(APR, "#.00%") & " x period days/year days", "", "rate"),
BlankLine,
HSTACK( "Debt balance open", "", Cur),
HSTACK( " Plus draw", "", Cur),
HSTACK( " Less repayment", "", Cur),
HSTACK( "Debt balance close", "", Cur),
BlankLine,
HSTACK( "Interest balance open", "", Cur),
HSTACK( " Plus interest charge", "", Cur),
HSTACK( " Less interest payments", "", Cur),
HSTACK( "Interest balance close", "", Cur),
BlankLine
),
Result, IF(n = 1, Block, VSTACK( Acc, Block)),
Result
)
)
),
Result
)
),
Result
)
);
// 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
Nov 26 2025 Craig Hatmaker Simplified
*/
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:→¶" &
" Timeline →(Required) Model's Timeline¶" &
" EndDates? →(Optional) TRUE (Default): Timeline displays period end dates. FALSE: Start dates.",
,
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( ModelTimeline),
Result, IF( Help?,
Help,
LET(
// 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),
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?,
SWITCH( Interval,
"D", ModelTimeline,
"W", ModelTimeline - 6,
"M", DateFunction( +ModelTimeline, -1) + 1,
"Q", DateFunction( +ModelTimeline, -3) + 1,
"Y", DateFunction( +ModelTimeline, -12) + 1
),
ModelTimeline
),
// Calculate End Dates
Ends, IF( EndDates?,
ModelTimeline,
SWITCH( Interval,
"D", ModelTimeline,
"W", ModelTimeline + 6,
"M", DateFunction( +ModelTimeline, 1) - 1,
"Q", DateFunction( +ModelTimeline, 3) - 1,
"Y", DateFunction( +ModelTimeline, 12) - 1
)
),
// Procedure
Result, VSTACK(Starts, Ends, IF( Starts, Interval)),
Result
)
),
Result
)
);
// Functions from Finance Library
// Functions from Finance Library
/* FUNCTION NAME: AdaptToTimelineλ
DESCRIPTION:*//**Positions and adjusts an array to fit in a model's timeline*/
/* REVISIONS: Date Developer Description
Nov 27 2025 Craig Hatmaker Copyright
Dec 06 2025 Craig Hatmaker Fixed Opn
*/
AdaptToTimelineλ = LAMBDA(
// Parameter Declarations
[Values],
[ValuesRowTypes],
[ValuesStarts],
[ValuesEnds],
[ModelStarts],
[ModelEnds],
// Help
LET(
Help, TRIM(TEXTSPLIT(
"FUNCTION: →AdaptToTimelineλ( Values, ValuesRowTypes,¶" &
"→ ValuesStarts, ValuesEnds, ModelStarts, ModelEnds, )¶" &
"DESCRIPTION: →Adapts an array based on one timeline, to the model's timeline.¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Nov 27 2025¶" &
"PARAMETERS:→¶" &
" Values →(Required) 2 dimensional array/range containing values to be placed in the¶" &
"→model's timeline.¶" &
" ValuesRowTypes→(Required) A column of indicators for each row where values row contains: ¶" &
"→ Avg=Average values in period.¶" &
"→ ANZ=Average non-zero values in period.¶" &
"→ Sum=Transactions - SUM'd by model period.¶" &
"→ Opn=Opening Balances - Pulled from first Value fitting in a model period.¶" &
"→ Cls=Closing Balances - Pulled from last Value fitting in a model period.¶" &
"→ Spc=Blank lines.¶" &
" ValuesStarts →(Required) The values timeline in period start dates¶" &
" ValuesEnds →(Required) The values timeline in period End dates¶" &
" ModelStarts →(Required) The model's timeline period start dates¶" &
" ModelEnds →(Required) The model's timeline period End dates",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( ValuesRowTypes),
ISOMITTED( ValuesStarts),
ISOMITTED( ValuesEnds),
ISOMITTED( ModelStarts),
ISOMITTED( ModelEnds)
),
Result, IF( Help?,
Help,
LET(
// Pre-calcs
Rows, ROWS( Values),
Cols, COLUMNS( ModelStarts),
// Define functions that handle processing each row type.
Functions, MATCH( ValuesRowTypes, {"Opn", "Cls", "Avg", "ANZ", "Sum", "Spc"}, 0),
// Procedure
Result, REDUCE( 0, SEQUENCE( Rows),
LAMBDA( Acc, r,
LET(
Function, INDEX( Functions, r),
Row, CHOOSEROWS( Values, r),
Effective, N((TAKE( ValuesStarts, , 1) <= ModelStarts) * (TAKE( ValuesEnds, , -1) >= ModelEnds)),
Line, CHOOSE( Function,
// Open
XLOOKUP( ModelStarts, ValuesStarts, Row, 0, 1), // * Effective,
// Close
XLOOKUP( ModelEnds, ValuesStarts, Row, 0, -1), //* Effective,
// Average
MAP( ModelStarts, ModelEnds,
LAMBDA( MStart, MEnd,
AVERAGE(( MStart <= ValuesEnds) * (MEnd >= ValuesEnds) * Row))),
// Average non-zero
MAP( ModelStarts, ModelEnds,
LAMBDA( MStart, MEnd,
LET(
Values, ( MStart <= ValuesEnds) * (MEnd >= ValuesEnds) * Row,
Filter, FILTER( Values, Values <> 0) ,
IFERROR(AVERAGE(Filter), 0)
))),
// Sum
MAP( ModelStarts, ModelEnds,
LAMBDA( MStart, MEnd,
SUM(( MStart <= ValuesEnds) * (MEnd >= ValuesEnds) * Row))) ,
// Space
IF( ModelStarts, "")
),
Result, IF( r = 1, Line, VSTACK( Acc, Line)),
Result
)
)
),
Result
)
),
Result
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment