Skip to content

Instantly share code, notes, and snippets.

@CHatmaker

CHatmaker/BXL 5g Functions for Excel: Debt Library 2026

Last active September 6, 2026 00:08
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save CHatmaker/2ebb5b42457f35a36da103dd9b8d15fb to your computer and use it in GitHub Desktop.
Download ZIP
BXL 5g Functions for Excel: Debt Library 2026
Raw
BXL 5g Functions for Excel: Debt Library 2026
/* FUNCTION NAME: Aboutλ
DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/* REVISIONS: Date Developer Description
Aug 21 2026 Craig Hatmaker Copyright - New Version
Sep 05 2026 Craig Hatmaker See Balloonλ, TermLoanλ, InputLabelsλ
*/
Aboutλ = TRIM(
TEXTSPLIT(
"About: →5g Compliant functions that deal with debt and loans. Suggested module name: BXL¶" &
"Version: →BXL: Sep 05 2026¶" &
"Gist URL: →https://gist.github.com/CHatmaker/2ebb5b42457f35a36da103dd9b8d15fb ¶" &
"Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
"→¶" &
"Function →Description¶" &
" Aboutλ →Produces this table¶" &
"→¶" &
"DEBT FUNCTIONS →Functions for creating debt schedules¶" &
" Balloonλ →Creates a fixed-rate loan schedule with constant periodic debt service on an¶" &
"→ extended amortization schedule, executing a full principal balance sweep ¶" &
"→ at maturity. For more complex needs, use TermSimpleλ or TermCapλ ¶" &
" Bulletλ →Creates an interest-only debt schedule with no intermediate principal repayments¶" &
"→ and entire principal due at maturity. For more complex needs, use FlexLoanλ.¶" &
" FlexLoanλ →Creates a flexible facility debt schedule offering flexible payment options.¶" &
" PIKλ →Creates a Payment-In-Kind (PIK) debt schedule with cash/PIK splits that adapts to any timeline.¶" &
" TermLoanλ →Creates a fully amortizing debt schedules with fixed periodic debt service. ¶" &
"→¶" &
"SUPPORT FUNCTIONS →Functions used by other functions or general purpose functions.¶" &
" DCCλ →Create Day Count Convention rates per Timeline dates. ¶" &
" DCCDisplayλ →Show day count convention calculations in model's timeline which include:¶" &
"→ ✓ DCC period days¶" &
"→ ✓ DCC year days¶" &
"→ ✓ APR¶" &
"→ ✓ Resulting DCC rate: APR * (DCC Days / DCC Year Days) ¶" &
" FlagChangesλ →Create flags indicating when a row of flags changes from 0/FALSE to 1/TRUE.¶" &
" IndexedRatesλ →Determine DCC based period APRs based on an index (eg. SOFR, SONIA, SARON, TONA) ¶" &
"→ and one or more margins above index. If more than one margin, there must also be ¶" &
"→ some trigger amount (eg. Total Debt or Total Debt/EBITDA) used to search thresholds ¶" &
"→ for the appropriate margin.¶" &
" Intervalsλ →Create a row of flags indicating when something repeats¶" &
"→ based on a start date and an interval code.¶" &
" MonthDifλ →Determine months between two dates without regard to day of month¶" &
" MonthlyIntRatesλ →Create a row of monthly interest rates for any day count convention¶" &
" PrdIntRatesλ →Create a row of period interest rates for any day count convention¶" &
" PmtAλ →Determine equal monthly payments for any day count convention ¶" &
" SchedRatesλ →Schedule rates that repeat at intervals¶" &
" StartStopλ →Create a row of flags indicating when something is effective¶" &
"→ based on start/stop dates¶" &
"→¶" &
"LABEL FUNCTIONS →Labels for other functions' outputs.¶" &
" DebtLabelsλ →Create line labels for debt schedules other than PIK.¶" &
" InputLabelsλ →Creaste labels for debt schdule linked inputs.¶" &
" PIKLabelsλ →Create line labels PIK debt schedules.¶" &
"→¶" &
"INTERNAL FUNCTIONS →DO NOT USE - This are for internal use only¶" &
" λAdaptToTimeline →Adapt values based on an internal timeline to the model's timeline. ¶" &
" λDCC →Calculate Day Count Convention interst rates ¶" &
" λDeriveTimeline →Determine the model timeline's attributes ¶" &
" λFlexInterest → ¶" &
" λFlexRepay → ¶" &
" λIntervals →Create flags showing when something repeats ¶" &
" λLoanPrep →Create all rows required to process flex and term based loans ¶" &
" λPmtA →Calculate payments for any day count convention ¶" &
" λSchedRates →Schedule rates that repeat at intervals ¶" &
" λStartStop →Create a row of flags indicating when something is effective¶" &
"→ based on start/stop dates¶" &
"→¶" &
"HELP TEXT →Argument help dictionary¶" &
" λAllPositiveHelp →Help text for argument AllPositive? ¶" &
" λArrearsHelp →Help text for argument Arrears? ¶" &
" λAPRHelp →Help text for argument APR rates and dates ¶" &
" λDCCHelp →Help text for argument DCC ¶" &
" λDrawHelp →Help text for argument Draw amounts, dates, and repeats ¶" &
" λExtraHelp →Help text for argument Extra amounts, dates, and repeats ¶" &
" λExtraRecastHelp →Help text for argument ExtraRecast? ¶" &
" λInterestHolidayHelp→Help text for argument Interest charges and payment grace periods and holidays ¶" &
" λMaturityHelp →Help text for argument MaturityDate ¶" &
" λPaymentHelp →Help text for argument Payment amount, dates, and repeats ¶" &
" λPaymentHolidayHelp→Help text for argument Payment grace periods and holidays ¶" &
" λSimpleHelp →Help text for argument Simple? ¶" &
" λTimelineHelp →Help text for argument Timeline and EndDates?",
"→","¶"
)
);
/* FUNCTION NAME: Balloonλ
DESCRIPTION:*//**Creates a fixed-rate loan schedule with constant periodic debt service on an
extended amortization schedule, executing a full principal balance sweep at maturity.
For more complex needs, use FlexLoanλ */
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
Flexloanλ
REVISIONS: Date Developer Description
Aug 19 2026 Craig Hatmaker Copyright
Sep 05 2026 Craig Hatmaker Replaced Arrears? argument with TRUE
*/
Balloonλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[DrawAmounts], // A single value or array of Draw Amounts
[DrawDates], // A single value or array of dates indicating when those Draws occur
[MaturityDate], // Term date of loan
[APR], // A single Annual Interest Percentage Rate
[DCC], // Day Count Convention
[Simple?], // A flag indicating the loan uses simple interest or capitalizes interest
[PaymentAmount], // A single amount to pay each month covering prinicipal plus interest
[AllPositive?], // A flag indicating, when TRUE, reductions should display as positive values
// Check required arguments
IF( OR( ISOMITTED( Timeline),
ISOMITTED( DrawAmounts),
ISOMITTED( DrawDates),
ISOMITTED( MaturityDate),
ISOMITTED( APR),
ISOMITTED( PaymentAmount)
),
TRIM(TEXTSPLIT(
"FUNCTION: →Balloonλ( Timeline, [EndDates?], DrawAmounts, DrawDates, MaturityDate, ¶" &
"→ APRs, [APRDates], [DCC] ,[Simple?], PaymentAmount, [AllPositive?])¶" &
"DESCRIPTION: →Creates a fixed-rate loan schedule with constant periodic debt service on an ¶" &
"→ extended amortization schedule, executing a full principal balance sweep at maturity.¶" &
"→ For more complex needs, use TermSimpleλ or TermCapλ ¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Aug 12, 2026¶" &
"PARAMETERS:→¶" &
λTimelineHelp &
"DrawAmounts →A single value or array of Draw Amounts.¶" &
"DrawDates →A single value or array of dates indicating when those Draws occur.¶" &
"→ NOTE! For simplicity, these entries are one time only draws and do not repeat.¶" &
λMaturityHelp &
"APR →A single Annual Interest Percentage Rate¶" &
λDCCHelp &
"PaymentAmount, →(Required) A single amount to pay each month covering prinicipal plus interest¶"&
"→ NOTE! Balloon loans are often short term loans with payments based on longer terms.¶"&
"→ PmtAλ can be used to calculate payments for longer terms.¶"&
λAllPositiveHelp &
" Simple? →(Optional) TRUE/FALSE. When TRUE, interest is not compounded. Default = FALSE.¶" &
"→", "→", "¶" )),
LET(
// Set Defaults (not handled by FlexSimpleλ or FlexCapλ)
Simple?, IF( ISOMITTED( Simple?), FALSE, Simple? = TRUE),
// Determine function to use and call it
Result, FlexLoanλ( Timeline, EndDates?, DrawAmounts, DrawDates, ,
MaturityDate, APR, , DCC, Simple?, TRUE, PaymentAmount,,,,,,AllPositive?),
Result
)
)
);
/* FUNCTION NAME: Bulletλ
DESCRIPTION:*//**Creates an interest-only debt schedule with zero intermediate principal repayments,
executing a 100% principal balloon payoff at maturity. For more complex needs, use FlexSimpleλ or FlexCapλ
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
FlexSimpleλ, FlexCapλ
REVISIONS: Date Developer Description
Aug 10 2026 Craig Hatmaker Copyright
*/
Bulletλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[DrawAmounts], // A single value or array of Draw Amounts
[DrawDates], // A single value or array of dates indicating when those Draws occur
[MaturityDate], // Term date of loan
[APR], // A single Annual Interest Percentage Rate
[DCC], // Day Count Convention
[Simple?], // A flag indicating the loan uses simple interest or capitalizes interest
[AllPositive?], // A flag indicating, when TRUE, reductions should display as positive values
// Check required arguments
IF( OR( ISOMITTED( Timeline),
ISOMITTED( DrawAmounts),
ISOMITTED( DrawDates),
ISOMITTED( MaturityDate),
ISOMITTED( APR)
),
TRIM(TEXTSPLIT(
"FUNCTION: →Bulletλ( Timeline, [EndDates?], DrawAmounts, DrawDates, ¶" &
"→ MaturityDate, APRs, [DCC] ,[Simple?], [AllPositive?])¶" &
"DESCRIPTION: →Creates an interest-only debt schedule with zero intermediate principal repayments,¶" &
"→ executing a 100% principal balloon payoff at maturity.¶" &
"→ For more complex needs, use FlexLoanλ ¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Aug 19, 2026¶" &
"PARAMETERS:→¶"&
λTimelineHelp &
"DrawAmounts →A single value or array of Draw Amounts.¶" &
"DrawDates →A single value or array of dates indicating when those Draws occur.¶" &
"→ NOTE! For simplicity, these entries are one time only draws and do not repeat.¶" &
λMaturityHelp &
"APR →A single Annual Interest Percentage Rate¶" &
λDCCHelp &
λSimpleHelp &
λAllPositiveHelp &
"→", "→", "¶" )),
LET(
// Set Defaults (not handled by FlexSimpleλ or FlexCapλ)
Simple?, IF( ISOMITTED( Simple?), TRUE, Simple? = TRUE),
// Determine function to use and call it
Result, FlexLoanλ( Timeline, EndDates?, DrawAmounts, DrawDates, ,
MaturityDate, APR,, DCC, Simple?,,,,,,,, AllPositive?),
Result
)
)
);
/* FUNCTION NAME: FlexLoanλ
DESCRIPTION:*//**Creates a flexible facility debt schedule offering borrower felxible payment options.*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λFlexSimpleHelp, λLoanPrep
REVISIONS: Date Developer Description
Aug 10 2026 Craig Hatmaker Copyright
*/
FlexLoanλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[DrawAmounts], // Single value or array of Draw Amounts
[DrawDates], // Single value or array of dates indicating when those Draws occur
[DrawRepeats], // Single value or array of how often those Draws repeat
[MaturityDate], // Term date of loan
[APRs], // Single value or array of APRs
[APRDates], // Single value or array of dates indicating when those APR rates are in effect
[DCC], // Day Count Convention
[Simple?], // Flag: TRUE (Default) unpaid interest is paid from cash vs added to debt (FALSE)
[Arrears?], // Flag: TRUE (Default) Interest is in arrears vs in advance (FALSE)
[PaymentAmounts], // Single value or array of Payment Amounts
[PaymentDates], // Single value or array of dates indicating when those Payments are in effect
[PaymentRepeats], // Single value or array of how often those Payments repeat
[PaymentHolidays], // Single value or array of Payment grace and holiday period schedule
[IntChgHolidays], // Single value or array of Interest Charges grace and holiday period schedule
[IntPmtHolidays], // Single value or array of Interest Payment grace and holiday period schedule
[AllPositive?], // Flag: TRUE (Default) reductions display as positive vs negative (FALSE)
// Check required arguments
IF( OR( ISOMITTED( Timeline),
ISOMITTED( DrawAmounts),
ISOMITTED( DrawDates),
ISOMITTED( MaturityDate),
ISOMITTED( APRs)
),
TRIM(TEXTSPLIT(
"FUNCTION: →FlexSimpleλ( Timeline, [EndDates?], DrawAmounts, [DrawDates], [DrawRepeats],¶" &
"→ MaturityDate, APRs, [APRDates], [DCC], [Simple?], [Arrears?], ¶" &
"→ PaymentAmounts [PaymentDates], [PaymentRepeats], ¶" &
"→ [PaymentHolidays], [IntChgHolidays], [IntPmtHoliday], [AllPositive?])¶" &
"DESCRIPTION: →Creates a flexible facility debt schedule offering borrower felxible payment options.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Aug 10, 2026¶" &
"PARAMETERS:→¶"&
λTimelineHelp &
λDrawHelp &
λMaturityHelp &
λAPRHelp &
λDCCHelp &
λSimpleHelp &
λArrearsHelp &
λPaymentHelp &
λPaymentHolidayHelp &
λInterestHolidayHelp &
λAllPositiveHelp &
"→", "→", "¶" )),
LET(
// Set Defaults (not handled by λLoanPrep)
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Arrears?, IF( ISOMITTED( Arrears?), TRUE, Arrears? = TRUE),
AllPositive?, IF( ISOMITTED( AllPositive?), TRUE, AllPositive? = TRUE),
StartDate, TAKE( DrawDates, 1, 1),
MaturityDate, DATE( YEAR( MaturityDate), MONTH( MaturityDate), DAY( StartDate)),
Sign, IF( AllPositive?, 1, -1),
// Get loan prep array
PrepArray, λLoanPrep( DrawAmounts, DrawDates, DrawRepeats, MaturityDate,
APRs, APRDates, DCC, PaymentAmounts, PaymentDates, PaymentRepeats, ,,,, PaymentHolidays,
IntChgHolidays, IntPmtHolidays, Arrears?),
VStarts, INDEX( PrepArray, 2, ),
// Load Thunks
Thunk, LAMBDA( x, LAMBDA( x)),
ThunkArray, BYCOL( PrepArray, Thunk),
// Calculate debt corkscrew rows
LastPrd, COLUMNS( PrepArray),
Values, REDUCE(0, ThunkArray,
LAMBDA( Acc, ThunkColumn,
LET(
Prd, INDEX( ThunkColumn(), 1, 1),
VStart, INDEX( ThunkColumn(), 2),
PmtFlg, INDEX( ThunkColumn(), 4),
IntChgFlg, INDEX( ThunkColumn(), 5),
IntPmtFlg, INDEX( ThunkColumn(), 6),
Draw, INDEX( ThunkColumn(), 8),
Pmt, INDEX( ThunkColumn(), 10),
MIR, INDEX( ThunkColumn(), 12),
PrvPrd, Prd - 1,
Stack, IF( Simple?,
LET(
DbtOpn, IF( Prd = 1, 0, INDEX( Acc, 4, PrvPrd)),
IntOpn, IF( Prd = 1, 0, INDEX( Acc, 9, PrvPrd)),
IntChg, IF( Arrears?, DbtOpn, DbtOpn + Draw) * MIR * IntChgFlg,
IntPmt, IF( Prd = LastPrd,
IntOpn + IntChg,
MIN( Pmt, IntOpn + IntChg) * IntPmtFlg),
IntCls, IF( Prd = LastPrd, 0, IntOpn + IntChg - IntPmt),
RePmt, IF( Prd = LastPrd,
DbtOpn + Draw,
MAX( Pmt - IntPmt, 0) * PmtFlg),
DbtCls, IF(Prd = LastPrd, 0, DbtOpn + Draw - Repmt),
Stack, VSTACK( DbtOpn, Draw, Sign * RePmt, DbtCls, "",
IntOpn, IntChg, Sign * IntPmt, IntCls),
Stack
),
LET(
DbtOpn, TAKE( Acc, -1, -1),
IntChg, IF( Arrears?, DbtOpn, DbtOpn + Draw) * MIR * IntChgFlg,
IntPmt, MIN( Pmt, IntChg) * IntPmtFlg,
RePmt, IF( Prd = LastPrd,
DbtOpn + Draw + IntChg - IntPmt,
MAX( Pmt - IntPmt, 0) * PmtFlg),
DbtCls, IF(Prd = LastPrd, 0, DbtOpn + Draw + (IntChg - IntPmt) - Repmt),
Stack, IF( Arrears?,
VSTACK( DbtOpn, IntChg, Sign * IntPmt, Draw, Sign * RePmt, DbtCls),
VSTACK( DbtOpn, Draw, IntChg, Sign * IntPmt, Sign * RePmt, DbtCls)),
Stack
)
),
Result, IF( Prd = 1, Stack, HSTACK( Acc, Stack)),
Result
)
)
),
// Adapt to timeline
Timelines, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
AggrTypes, IF( Simple?,
{"Opn"; "Sum"; "Sum"; "Cls"; "Spc"; "Opn"; "Sum"; "Sum"; "Cls"},
{"Opn"; "Sum"; "Sum"; "Sum"; "Sum"; "Cls"}
),
Result, λAdaptToTimeline( Values, AggrTypes, VStarts, MStarts, MEnds),
Result
)
)
);
/* FUNCTION NAME: PIKλ
DESCRIPTION:*//**Creates a Payment-In-Kind (PIK) debt schedule with cash/PIK splits that adapts to any timeline.*/
/* REQUIREMENTS: This function relies on the following functions included in this libary
MonthDifλ, λSchedRates, λDeriveTimeline, λAdaptToTimeline
REVISIONS: Date Developer Description
Aug 10 2026 Craig Hatmaker Copyright
*/
PIKλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[DrawAmounts], // Single value or array of Draw Amounts
[DrawDates], // Single value or array of dates indicating when those Draws occur
[DrawRepeats], // Single value or array of how often those Draws repeat
[MaturityDate], // Term date of loan
[DCC], // Day Count Convention
[PIKAPRs], // A single value or array of APRs
[PIKDates], // A single value or array of dates indicating when those APR rates are in effect
[CashSplits], // A single value or array of PIK cash split percentages
[Arrears?], // Flag: TRUE (Default) Interest is in arrears vs in advance (FALSE)
[AllPositive?], // Flag: TRUE (Default) reductions display as positive vs negative (FALSE)
// Check required arguments
IF( OR( ISOMITTED( Timeline),
ISOMITTED( DrawAmounts),
ISOMITTED( DrawDates),
ISOMITTED( MaturityDate),
ISOMITTED( PIKAPRs)
),
TRIM(TEXTSPLIT(
"FUNCTION: →PIKλ( Timeline, [EndDates?], DrawAmounts, [DrawDates], [DrawRepeats],¶" &
"→ MaturityDate, [DCC], PIKAPRs, [PIKDates], [CashSplits], [Arrears?], [AllPositive?])¶" &
"DESCRIPTION: →Creates a Payment-In-Kind (PIK) debt schedule with cash/PIK splits that adapts to any timeline.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Aug 10, 2026¶" &
"PARAMETERS:→¶"&
λTimelineHelp &
λDrawHelp &
λMaturityHelp &
λDCCHelp &
" PIKAPRs →(Required) A single APR or an array of APRs for each cash split change.¶" &
" PIKDates →(Optional/Required) An array of dates for each cash split change.¶" &
"→ If there are no cash split changes, this should be the first draw date,¶" &
"→ and if omitted, this will default to the first draw date.¶" &
" CashSplits →(Optional) An array of percentages indicating how much of the interest charge ¶" &
"→ is paid in cash vs added to debt. This defaults to 100%.¶" &
λArrearsHelp &
λAllPositiveHelp &
"→", "→", "¶" )),
LET(
// Set Defaults (not handled by λLoanPrep)
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Arrears?, IF( ISOMITTED( Arrears?), TRUE, Arrears? = TRUE),
AllPositive?, IF( ISOMITTED( AllPositive?), TRUE, AllPositive? = TRUE),
StartDate, TAKE( DrawDates, 1, 1),
MaturityDate, DATE( YEAR( MaturityDate), MONTH( MaturityDate), DAY( StartDate)),
PIKAPRs, TOCOL( PIKAPRs),
PIKDates, TOCOL( IF( ISOMITTED( PIKDates), StartDate, (PIKDates = 0) * StartDate + PIKDates)),
CashSplits, TOCOL( IF( ISOMITTED( CashSplits), 1, CashSplits)),
Sign, IF( AllPositive?, 1, -1),
// Create PIK Thunk array
Months, MonthDifλ( StartDate, MaturityDate) + 1,
Periods, SEQUENCE( , Months),
VStarts, EDATE( StartDate, Periods - 1),
VEnds, EDATE( StartDate, Periods) - 1,
Draws, λSchedRates( StartDate, Months, +DrawAmounts, DrawDates, DrawRepeats, TRUE),
Splits, λSchedRates( StartDate, Months, +CashSplits, PIKDates, "M", TRUE),
APRs, λSchedRates( StartDate, Months, +PIKAPRs, PIKDates, "M", FALSE),
MIRs, TAKE( λDCC( VStarts, VEnds, APRs, @DCC), -1),
// Load Thunks
Thunk, LAMBDA( x, LAMBDA( x)),
ThunkArray, BYCOL( VSTACK(Periods, Draws, MIRs, Splits), Thunk),
// Calculate debt corkscrew rows
Values, REDUCE(0, ThunkArray,
LAMBDA( Acc, ThunkColumn,
LET(
Prd, INDEX( ThunkColumn(), 1, 1),
Draw, INDEX( ThunkColumn(), 2),
MIR, INDEX( ThunkColumn(), 3),
Split, INDEX( ThunkColumn(), 4),
DbtOpn, TAKE( Acc, -1, -1),
IntChg, IF( Arrears?, DbtOpn, DbtOpn + Draw) * MIR,
IntPmt, IntChg * Split,
RePmt, IF( Prd = Months, DbtOpn + Draw + IntChg - IntPmt, 0),
DbtCls, IF( Prd = Months, 0, DbtOpn + Draw + IntChg - IntPmt - RePmt),
Stack, IF( Arrears?,
VSTACK( DbtOpn, MIR, IntChg, Split, Sign * IntPmt, Draw, Sign * RePmt, DbtCls),
VSTACK( DbtOpn, Draw, MIR, IntChg, Split, Sign * IntPmt, Sign * RePmt, DbtCls)),
Result, IF( Prd = 1, Stack, HSTACK( Acc, Stack)),
Result
)
)
),
// Adapt to timeline
Timelines, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
AggrTypes, IF( Arrears?,
{"Opn"; "Sum"; "Sum"; "ANZ"; "Sum"; "Sum"; "Sum"; "Cls"},
{"Opn"; "Sum"; "Sum"; "Sum"; "ANZ"; "Sum"; "Sum"; "Cls"}),
Result, λAdaptToTimeline( Values, AggrTypes, VStarts, MStarts, MEnds),
Result
)
)
);
/* FUNCTION NAME: TermLoanλ
DESCRIPTION:*//**Creates a fully amortizing debt schedules with fixed periodic debt service./
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λTermCapHelp, λLoanPrep, λPmtA, λDeriveTimeline, λAdaptToTimeline
REVISIONS: Date Developer Description
Aug 10 2026 Craig Hatmaker Copyright
Sep 05 2026 Craig Hatmaker Added APR, INDEX( ThunkColumn(), 11),
*/
TermLoanλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[DrawAmounts], // Single value or array of Draw Amounts
[DrawDates], // Single value or array of dates indicating when those Draws occur
[DrawRepeats], // Single value or array of how often those Draws repeat
[MaturityDate], // Term date of loan
[APRs], // Single value or array of APRs
[APRDates], // Single value or array of dates indicating when those APR rates are in effect
[DCC], // Day Count Convention
[Simple?], // Flag: TRUE (Default) unpaid interest is paid from cash vs added to debt (FALSE)
[Arrears?], // Flag: TRUE (Default) Interest is in arrears vs in advance (FALSE)
[ExtraAmounts], // Single value or array of extra payment amounts
[ExtraDates], // Single value or array of dates indicating when extra payments occur
[ExtraRepeats], // Single value or array of how often those extra payments repeat
[ExtraRecast?], // Flag: TRUE (Default) extra payments trigger recalculating payment to preserver term
[PaymentHolidays], // Single value or array of Payment grace and holiday period schedule
[IntChgHolidays], // Single value or array of Interest Charges grace and holiday period schedule
[IntPmtHolidays], // Single value or array of Interest Payment grace and holiday period schedule
[AllPositive?], // Flag: TRUE (Default) reductions display as positive vs negative (FALSE)
// Check required arguments
IF( OR( ISOMITTED( Timeline),
ISOMITTED( DrawAmounts),
ISOMITTED( DrawDates),
ISOMITTED( MaturityDate),
ISOMITTED( APRs)
),
TRIM(TEXTSPLIT(
"FUNCTION: →TermSimpleλ( Timeline, [EndDates?], DrawAmounts, [DrawDates], [DrawRepeats],¶" &
"→ MaturityDate, APRs, [APRDates], [DCC], [Simple?], [Arrears?], ¶" &
"→ [ExtraAmounts], [ExtraDates], [ExtraRepeats], [ExtraRecast?], ¶" &
"→ [PaymentHolidays], [IntChgHolidays], [IntPmtHolidays], [AllPositive?])¶" &
"DESCRIPTION: →Creates a fully amortizing debt schedules with fixed periodic debt service.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Aug 19, 2026¶" &
"PARAMETERS: →¶"&
λTimelineHelp &
λDrawHelp &
λMaturityHelp &
λAPRHelp &
λDCCHelp &
λSimpleHelp &
λArrearsHelp &
λExtraHelp &
λPaymentHolidayHelp &
λInterestHolidayHelp &
λAllPositiveHelp & "→", "→", "¶")
),
LET(
// Set Defaults (not handled by λLoanPrep)
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates? = TRUE),
AllPositive?, IF( ISOMITTED( AllPositive?), TRUE, AllPositive? = TRUE),
StartDate, TAKE( DrawDates, 1, 1),
MaturityDate, DATE( YEAR( MaturityDate), MONTH( MaturityDate), DAY( StartDate)),
Arrears?, IF( ISOMITTED( Arrears?), TRUE, Arrears? = TRUE),
Simple?, IF( ISOMITTED( Simple?), TRUE, Simple? = TRUE),
Sign, IF( AllPositive?, 1, -1),
// Get loan prep array
PrepArray, λLoanPrep( DrawAmounts, DrawDates, DrawRepeats, MaturityDate,
APRs, APRDates, DCC, , , ,
ExtraAmounts, ExtraDates, ExtraRepeats, ExtraRecast?,
PaymentHolidays, IntChgHolidays, IntPmtHolidays, Arrears?),
VStarts, INDEX( PrepArray, 2, ),
VEnds, INDEX( PrepArray, 3, ),
// Load Thunks
Thunk, LAMBDA( x, LAMBDA( x)),
ThunkArray, BYCOL( PrepArray, Thunk),
// Calculate debt corkscrew rows
LastPrd, COLUMNS( PrepArray),
Values, REDUCE(0, ThunkArray,
LAMBDA( Acc, ThunkColumn,
LET(
Prd, INDEX( ThunkColumn(), 1, 1),
VStart, INDEX( ThunkColumn(), 2),
PmtFlg, INDEX( ThunkColumn(), 4),
IntChgFlg, INDEX( ThunkColumn(), 5),
IntPmtFlg, INDEX( ThunkColumn(), 6),
RecastFlg, INDEX( ThunkColumn(), 7),
Draw, INDEX( ThunkColumn(), 8),
Extra, INDEX( ThunkColumn(), 9),
APR, INDEX( ThunkColumn(), 11),
MIR, INDEX( ThunkColumn(), 12),
PrvPrd, Prd - 1,
Stack, IF( Simple?,
LET(
DbtOpn, IF( Prd = 1, 0, INDEX( Acc, 5, PrvPrd)),
IntOpn, IF( Prd = 1, 0, INDEX( Acc, 10, PrvPrd)),
PrvRePmt, IF( Prd = 1, 0, INDEX( Acc, 3, PrvPrd)),
PrvIntPmt, IF( Prd = 1, 0, INDEX( Acc, 9, PrvPrd)),
RemainPrds, LastPrd - Prd + 1,
Pmt, IF( RecastFlg,
λPmtA(
TAKE( VStarts, 1, - RemainPrds),
TAKE( VEnds, 1, - RemainPrds),
DbtOpn, APR, DCC),
Sign * (PrvIntPmt + PrvRePmt)),
IntChg, IF( Arrears?, DbtOpn, DbtOpn + Draw) * MIR * IntChgFlg,
IntPmt, MIN( Pmt, IntOpn + IntChg) * IntPmtFlg,
IntCls, IF( Prd = LastPrd, 0, IntOpn + IntChg - IntPmt),
RePmt, IF( Prd = LastPrd,
DbtOpn + Draw,
MAX( Pmt - IntPmt, 0) * PmtFlg),
DbtCls, IF(Prd = LastPrd, 0, DbtOpn + Draw - Repmt),
Stack, VSTACK( DbtOpn, Draw, Sign * RePmt, Extra, DbtCls, "",
IntOpn, IntChg, Sign * IntPmt, IntCls),
Stack
),
LET(
Open, TAKE( Acc, -1, -1),
PrvIntPmt, IF( Prd = 1, 0, IF( Arrears?, INDEX( Acc, 3, PrvPrd), INDEX( Acc, 4, PrvPrd))),
PrvRePmt, IF( Prd = 1, 0, INDEX( Acc, 5, PrvPrd)),
RemainPrds, LastPrd - Prd + 1,
Pmt, IF( RecastFlg,
@λPmtA( TAKE( VStarts, 1, -RemainPrds), TAKE( VEnds, 1, -RemainPrds), Open, APR, DCC),
Sign * (PrvIntPmt + PrvRePmt)),
IntChg, Open * MIR * IntChgFlg,
IntPmt, IntChg * IntPmtFlg,
Repay, (Pmt - IntPmt) * PmtFlg,
Close, Open + Draw + IntChg - IntPmt - Repay - Extra,
Stack, IF( Arrears?,
VSTACK( Open, IntChg, Sign * IntPmt, Draw, Sign * Repay, Sign * Extra, Close),
VSTACK( Open, Draw, IntChg, Sign * IntPmt, Sign * Repay, Sign * Extra, Close)
),
Stack
)
),
Result, IF( Prd = 1, Stack, HSTACK( Acc, Stack)),
Result
)
)
),
// Adapt to timeline
Timelines, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
AggrTypes, IF( Simple?,
{"Opn"; "Sum"; "Sum"; "Sum"; "Cls"; "Spc"; "Opn"; "Sum"; "Sum"; "Cls"},
{"Opn"; "Sum"; "Sum"; "Sum"; "Sum"; "Sum"; "Cls"}
),
Result, λAdaptToTimeline( Values, AggrTypes, VStarts, MStarts, MEnds),
Result
)
)
);
// Support functions
/* FUNCTION NAME: DCCλ
DESCRIPTION:*//**Create Day Count Convention rates.*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline, λDCC
REVISIONS: Date Developer Description
Jul 10 2026 Craig Hatmaker Copyright
*/
DCCλ = LAMBDA(
[Timeline],
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[APRs],
[DCC],
IF( OR( ISOMITTED( Timeline),
ISOMITTED( APRs),
ISOMITTED( DCC)
),
TRIM(TEXTSPLIT(
"FUNCTION: →DCCλ( Timeline, [EndDates?], APRs, DCC¶" &
"DESCRIPTION: →Create a row DCC rates.¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Aug 07 2026¶" &
"PARAMETERS: →¶" &
λTimelineHelp &
" APRs →(Required) A single APR for the entire timeline, or a row of APRs for timeline period.¶" &
" DCC →(Required) Day Count Convention used to calulate interest rates.¶" &
"→ Select: 1=30/360, 2=Actual/360, 3=Actual/365, or 4=Actual/Actual. Default = 3.",
"→", "¶" )
),
LET(
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
// Determine model's start and stop dates
MDates, λDeriveTimeline( Timeline, EndDates?),
VStarts, CHOOSEROWS( MDates, 1),
VEnds, CHOOSEROWS( MDates, 2),
// Convert APRs to a timeline row if not already
APRs, IF( COLUMNS( APRs) = 1, IF( VStarts, APRs), APRs),
// Calculate DCC rates
DCCArray, λDCC( VStarts, VEnds, APRs, DCC),
DPRs, CHOOSEROWS( DCCArray, -1),
DPRs
)
)
);
/* FUNCTION NAME: DCCDisplayλ
DESCRIPTION:*//**Show day count convention calculations.*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline, λDCC
REVISIONS: Date Developer Description
Jul 10 2026 Craig Hatmaker Copyright
*/
DCCDisplayλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[APRs], // Single value or array of APRs
[APRDates], // Single value or array of dates indicating when those APR rates are in effect
[DCC], // Day Count Convention
IF( OR( ISOMITTED( Timeline),
ISOMITTED( APRs),
ISOMITTED( DCC)
),
TRIM(TEXTSPLIT(
"FUNCTION: →DCCDisplayλ( Timeline, [EndDates?], APRs [APRDates], DCC)¶" &
"DESCRIPTION: →Show day count convention calculations in model's timeline which include:¶" &
"→ ✓ DCC period days¶" &
"→ ✓ DCC year days¶" &
"→ ✓ APR¶" &
"→ ✓ Resulting DCC rate: APR * (DCC Days / DCC Year Days) ¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jul 25 2026¶" &
"PARAMETERS: →¶" &
λTimelineHelp &
λAPRHelp &
λDCCHelp &
"→",
"→", "¶" )
),
LET(
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
// Determine model's start and stop dates
MDates, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( MDates, 1),
MEnds, CHOOSEROWS( MDates, 2),
// Convert APRs to a model-timeline-row
APRDates, IF( ISOMITTED( APRDates), INDEX( MStarts, 1), TOROW(APRDates)),
APRs, XLOOKUP( MStarts, APRDates, TOROW(APRs), 0, -1),
// Calculate DCC rates
Result, λDCC( MStarts, MEnds, APRs, DCC),
Result
)
)
);
/* FUNCTION NAME: FlagChangesλ
DESCRIPTION:*//**Create flags indicating when a row of flags changes from 0/FALSE to 1/TRUE.
*/
/* REVISIONS: Date Developer Description
Jul 30 2026 Craig Hatmaker Copyright
*/
FlagChangesλ = LAMBDA(
[Flags],
IF( ISOMITTED( Flags),
TRIM( TEXTSPLIT(
"FUNCTION: →FlagChangesλ( Flags)¶" &
"DESCRIPTION: →Create flags indicating when a row of flags change from 0/FALSE to 1/TRUE.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/<coming soon>¶" &
"VERSION: →BXL: Jul 30 2026¶" &
"PARAMETERS: →¶" &
" Flags →(Required) A row of TRUE/FALSE or 0/1 values",
"→", "¶"
)
),
HSTACK( 1, DROP( NOT( Flags), , -1)) * Flags
)
);
/* FUNCTION NAME: GetFieldλ
DESCRIPTION:*//**Get a field's value from a table.
*/
/* REVISIONS: Date Developer Description
Aug 19 2026 Craig Hatmaker Copyright
*/
GetFieldλ = LAMBDA(
[Table], // Table containing the value we need
[Field], // Table's column heading containing the value we need
[Key], // The value in the table's first column identifying the row we need.
IF( OR( ISOMITTED( Table),
ISOMITTED( Field),
ISOMITTED( Key)
),
TRIM( TEXTSPLIT(
"FUNCTION: →GetFieldλ( Table, Field, Key)¶" &
"DESCRIPTION: →Get a field's value from a table¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/<coming soon>¶" &
"VERSION: →BXL: Aug 19 2026¶" &
"PARAMETERS: →¶" &
" Table →(Required) Table containing the value we need ¶" &
" Field →(Required) Table's column heading containing the value we need ¶" &
" Key →(Required) The value in the table's first column identifying the row we need.",
"→", "¶"
)
),
LET(
FldCol, XMATCH( Field, OFFSET( Table, -1, 0, 1), 0),
ColData, CHOOSECOLS( Table, FldCol),
Filtered, FILTER( CHOOSECOLS( Table, FldCol), CHOOSECOLS( Table, 1) = Key),
Result, TOROW( Filtered),
Result
)
)
);
/* FUNCTION NAME: IndexedRatesλ
DESCRIPTION:*//**Determine DCC based period APRs based on an index (eg. SOFR, SONIA, SARON, TONA) and
one or more margins above index. If more than one margin, there must also be some trigger amount
(eg. Total Debt or Total Debt/EBITDA) used to search thresholds for the appropriate margin.
*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline
REVISIONS: Date Developer Description
Jul 10 2026 Craig Hatmaker Copyright
*/
IndexedRatesλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[IndexRates],
[IndexDates],
[Basis],
[Thresholds],
[MarginRates],
IF( OR( ISOMITTED( Timeline),
ISOMITTED( IndexRates),
AND( ISOMITTED( IndexDates), COUNT( IndexRates) > 1),
ISOMITTED( MarginRates),
AND( ISOMITTED( Basis), COUNT( MarginRates) > 1),
AND( ISOMITTED( Thresholds), COUNT( MarginRates) > 1)
),
TRIM( TEXTSPLIT(
"FUNCTION: →IndexedRatesλ( Timeline, [EndDates?], IndexRates, [IndexDates], ¶" &
"→ [Basis], [Thresholds], MarginRates)¶" &
"DESCRIPTION: →Determine DCC based period APRs based on an index (eg. SOFR, SONIA, SARON, TONA) ¶" &
"→ and one or more margins above index. If more than one margin, there must also be ¶" &
"→ some trigger amount (eg. Total Debt or Total Debt/EBITDA) used to search thresholds ¶" &
"→ for the appropriate margin.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" &
"→5g-component-libraries/<coming soon>¶" &
"VERSION: →BXL: Jul 10 2026¶" &
"PARAMETERS: →¶" &
λTimelineHelp &
" IndexRates →(Required) One or more of the index's rate.¶" &
" IndexDates →(Required/Optional) If there is more than one IndexRate, there must also be ¶" &
"→ corresponding effective dates.¶" &
" Basis →(Require/Optional) If there are multiple margins, there must also be be a row of ¶" &
"→ values (eg. Total Debt or Total Debt/EBITDA) that are the basis upon which¶" &
"→ margin perents are selected.¶" &
" Thresholds →(Required/Optional) If a basis is used to select margin percents, there muse also¶" &
"→ be a list of basis thresholds linked to margin percents. ¶" &
" MarginRates →(Required) A percentage added to the index which results in an APR.",
"→", "¶"
)
),
LET(
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates? = TRUE),
IndexDates, IF( ISOMITTED( IndexDates), 0, IndexDates),
Triggers, IF( ISOMITTED( Basis), 0, Basis),
Thresholds, IF( ISOMITTED( Thresholds), 0, Thresholds),
// Determine calendars
Timelines, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
Period, TAKE( Timelines, -1, 1),
StartDate, TAKE( MStarts, 1, 1),
EndDate, TAKE( MStarts, 1, -1),
Months, MonthDifλ( StartDate, EndDate) + 1,
// Index (ex. SOFR) schedule
Indexes, λSchedRates( StartDate, Months, IndexRates, IndexDates, Period, TRUE),
// Calculate triggered Margin percent
Interval, XLOOKUP( Period, {"M","Q","Y"}, {1,3,12}),
Margins, XLOOKUP( Triggers/Interval, Thresholds, MarginRates, 0, -1),
Values, VSTACK( Triggers, Margins, Indexes, Indexes + Margins),
Values
)
)
);
/* FUNCTION NAME: Intervalsλ
DESCRIPTION:*//**Create a row of flags indicating when something repeats*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline, MonthDifλ, λIntervalsλ, λAdaptToTimeline
REVISIONS: Date Developer Description
Jul 14 2026 Craig Hatmaker Copyright
*/
Intervalsλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[StartDate],
[EndDate],
[Interval],
[Beginning?],
IF( OR( ISOMITTED( Timeline),
ISOMITTED( StartDate),
ISOMITTED( EndDate),
ISOMITTED( Interval)
),
TRIM(TEXTSPLIT(
"FUNCTION: →Intervalsλ( Timeline, [EndDates?], StopDate, Interval)¶" &
"DESCRIPTION: →Create a row of flags indicating when something repeats¶" &
"→ based on a start date and an interval code.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶"&
"VERSION: →BXL: Jul 14, 2026¶"&
"PARAMETERS:→¶"&
λTimelineHelp &
" StartDate →(Required) When the intervals start (usually first draw date)¶" &
" StartDate →(Required) When the intervals end (usually maturity date)¶" &
" Interval →(Required) How often something repeats: ¶" &
"→ M=Monthly, Q=Quarterly, S=Semi-Annually, Y(or A)=Annually.¶" &
" Beginning? →(Optional) TRUE (default) = Flag the beginning of the interval¶" &
"→ FALSE = Flag the end of the interval: ",
"→","¶"
)
),
LET(
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Beginning?, IF( ISOMITTED( Beginning?), TRUE, Beginning?),
// Determine calendars
Timelines, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
Months, MonthDifλ( StartDate, EndDate) + 1,
Periods, SEQUENCE( , Months),
VStarts, EDATE( StartDate, Periods - 1),
// Get Flags
Flags, λIntervals( Months, Interval, Beginning?),
// Adapt to model Timeline
Values, VSTACK( VStarts, Flags),
AggrTypes, IF( Beginning?, "Opn", "Cls"),
Result, λAdaptToTimeline( Flags, AggrTypes, VStarts, MStarts, Mends),
Result
)
)
);
/* FUNCTION NAME: MonthDifλ
DESCRIPTION:*//**Determine months between two dates without regard to day of month*/
/* REVISIONS: Date Developer Description
Jul 07 2026 Craig Hatmaker Copyright
*/
MonthDifλ = LAMBDA(
[EarlierDate],
[LatterDate],
IF( OR( ISOMITTED( EarlierDate),
ISOMITTED( LatterDate)
),
TRIM(TEXTSPLIT(
"FUNCTION: →MonthDifλ( EarlierDate, LaterDate)¶" &
"DESCRIPTION: →Determine months between two dates without regard to day of month¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jul 07, 2026¶"&
"PARAMETERS:→¶" &
" EarlierDate →(Required) The earlier of the two dates¶"&
" LaterDate →(Required) The latter of the two dates.",
"→", "¶" )
),
LET(
Earlier, YEAR( EarlierDate) * 12 + MONTH( EarlierDate),
Latter, YEAR( LatterDate) * 12 + MONTH( LatterDate),
MonthDif, Latter - Earlier,
MonthDif
)
)
);
/* FUNCTION NAME: MonthlyIntRatesλ
DESCRIPTION:*//**Generate a row of interest rates based on day count convention.*/
/* REVISIONS: Date Developer Description
Jun 26 2026 Craig Hatmaker Copyright
*/
MonthlyIntRatesλ = LAMBDA(
[StartDate],
[Months],
[APR],
[DCC],
IF( OR( ISOMITTED( StartDate),
ISOMITTED( Months),
ISOMITTED( APR),
ISOMITTED( DCC)
),
TRIM(TEXTSPLIT(
"FUNCTION: →MonthlyIntRatesλ( StartDate, Months, APR, DCC)¶" &
"DESCRIPTION: →Create a row of monthly interest rates for any day count convention¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jun 26 2026¶" &
"PARAMETERS: →¶" &
" StartDate →(Required) Date of draw¶" &
" Months →(Required) Count of months to produce rates for.¶" &
" APRs →(Required) A single Annual interest rate percentage (APRs) or row of .¶" &
"→ APRs for each month.¶" &
" DCC →(Required) Day Count Convention used to calulate interest rates.¶" &
"→ Select: 1=30/360, 2=Actual/360, 3=Actual/365, or 4=Actual/Actual. Default = 3.",
"→", "¶" )
),
LET(
Starts, EDATE( StartDate, SEQUENCE( , Months, 0)),
Ends, EDATE( StartDate, SEQUENCE( , Months, 1)) -1,
MonthDays, IF( DCC = 1, IF( Starts, 30), Ends - Starts + 1 ),
YearDays, CHOOSE( DCC,
360,
360,
365,
365 + (MONTH( DATE( YEAR( Starts), 2, 29)) = 2)),
MPRs, APR * MonthDays / YearDays,
MPRs
)
)
);
/* FUNCTION NAME: PrdIntRatesλ
DESCRIPTION:*//**Create a row of period interest rates for any day count convention.*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline
REVISIONS: Date Developer Description
Jul 07 2026 Craig Hatmaker Copyright
*/
PrdIntRatesλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[APRs], // Single value or array of APRs
[APRDates], // Single value or array of dates indicating when those APR rates are in effect
[DCC], // Day Count Convention
IF( OR( ISOMITTED( Timeline),
ISOMITTED( APRs),
ISOMITTED( APRDates)
),
TRIM(TEXTSPLIT(
"FUNCTION: →PrdIntRatesλ( Timeline, [EndDates?], APRs, APRDates, [DCC])¶" &
"DESCRIPTION: →Create a row of period interest rates for any day count convention¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jul 07 2026¶" &
"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.¶" &
" APRs →(Required) Annual interest rate percentage.¶" &
" APRDates →(Required) Annual interest rate percentage.¶" &
" DCC →(Optional) Day Count Convention used to calulate interest rates:¶" &
"→ Select: 1=30/360, 2=Actual/360, 3=Actual/365 (Default), or 4=Actual/Actual.",
"→", "¶" )
),
LET(
// Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
DCC, IF( OR( ISOMITTED( DCC), DCC=0), 3, DCC),
// Determine model's start and stop dates, and period intervals.
MDates, λDeriveTimeline( Timeline, EndDates?),
VStarts, CHOOSEROWS( MDates, 1),
VEnds, CHOOSEROWS( MDates, 2),
Interval, INDEX( MDates, 3, 1),
// Determine Month and Year days based on model's timeline and day count convention.
PeriodDays, IF( DCC = 1,
XLOOKUP( Interval, {"M","Q","S","Y"}, {30,90,180,360} ),
VEnds - VStarts + 1),
YearDays, CHOOSE( DCC,
360,
360,
365,
365 + (MONTH( DATE( YEAR( VStarts), 2, 29)) = 2)),
// Schedule Effective Period Rates
RateSched, XLOOKUP( VStarts, APRDates, APRs, 0, -1),
// Apply Day Count Convention
EPRs, RateSched * PeriodDays / YearDays,
EPRs
)
)
);
/* FUNCTION NAME: PmtAλ
DESCRIPTION: Determine period payment amount for commercial loans*/
/* REVISIONS: Date Developer Description
Jul 25 2026 Craig Hatmaker Non-recursive version of PMTAλ
*/
PmtAλ = LAMBDA(
[DebtAmount],
[DebtDate],
[MaturityDate],
[CompoundFreq],
[APR],
[DCC],
// Help
IF( OR( ISOMITTED( DebtAmount),
ISOMITTED( DebtDate),
ISOMITTED( MaturityDate),
ISOMITTED( APR)
),
TRIM(TEXTSPLIT(
"FUNCTION: →PmtAλ( DebtAmount, DebtDate, MaturityDate, CompoundFreq, APR, [DCC])¶" &
"DESCRIPTION: →Determine equal monthly payments for any day count convention¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jul 25, 2026¶"&
"PARAMETERS: →¶" &
" DebtAmount →(Required) Debt balance¶" &
" DebtDate →(Required) Debt balance's period start date¶" &
" MaturityDate →(Required) Debt's maturity date¶" &
" CompoundFreq →(Required) Compouding frequence (M, Q, S, Y, A).¶" &
" APR →(Required) An annual interest rates¶" &
" DCC →(Optional) Day Count Convention used to calulate interest rates.¶" &
"→ Select: 1=30/360, 2=Actual/360, 3=(Default) Actual/365, or 4=Actual/Actual",
"→", "¶" )
),
LET(
// Defaults
DCC, IF( OR( ISOMITTED( DCC), DCC=0), 3, DCC),
CompoundFreq, IF( ISOMITTED( CompoundFreq), "M", CompoundFreq),
// Determine timelines
Months, MonthDifλ( DebtDate, MaturityDate) + 1,
Frequency, XLOOKUP( CompoundFreq, {"M","Q","S","Y","A"}, {1,3,6,12,12}),
Periods, SEQUENCE( , Months / Frequency),
VStarts, EDATE( DebtDate, Periods - 1),
VEnds, EDATE( DebtDate, Periods) - 1,
// Determine DCC Rates
DCCArray, λDCC( VStarts, VEnds, APR, DCC),
MonthDays, CHOOSEROWS( DCCArray, 1),
YearDays, CHOOSEROWS( DCCArray, 2),
// Determine Payment
Factors, 1 / ( 1 + ( APR * MonthDays / YearDays)),
Discounts, SCAN( 1, Factors, LAMBDA( Acc, Factor, Acc * Factor)),
Payment, DebtAmount / SUM( Discounts),
Payment
)
)
);
/* FUNCTION NAME: SchedRatesλ
DESCRIPTION:*//**Schedule rates that repeat at intervals*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline, MonthDifλ, λSchedRates, λAdaptToTimeline
REVISIONS: Date Developer Description
Jul 08 2026 Craig Hatmaker Copyright
*/
SchedRatesλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[Rates],
[RateDates],
[Repeats],
[Beginning?],
[AggrType],
IF( OR( ISOMITTED( Timeline),
ISOMITTED( Rates),
ISOMITTED( RateDates)
),
TRIM(TEXTSPLIT(
"FUNCTION: →SchedRates( Timeline, [EndDates?], Rates, RateDates, [Repeats])¶" &
"DESCRIPTION: →Schedule rates that repeat at intervals¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Jul 07, 2026¶"&
"PARAMETERS:→¶" &
" Timeline →(Required) A row of dates used for the model's timeline.¶"&
" EndDates? →(Optional) TRUE/FALSE. Timeline display end dates (TRUE, Default)¶"&
"→ or start dates (FALSE).¶"&
" Rates →(Required) One or more monthly rates to schedule ¶" &
" RateDates →(Required) When each rate starts¶" &
" Repeats →(Required) How often something repeats: ¶" &
"→ O=One time (default)¶" &
"→ M=Monthly¶" &
"→ Q=Quarterly¶" &
"→ S=Semi-Annually¶" &
"→ Y(or A)=Annually¶" &
" Beginning? →(Optional) TRUE (default) = Flag the beginning of the interval¶" &
"→ FALSE = Flag the end of the interval¶" &
" AggrType →(Optional) Aggregation type handles how monthly values are aggregated¶" &
"→ in quarterly or annual timelines. " &
"→ ""Sum""= (Default) Total values (use for things like payment amounts)¶" &
"→ ""Avg""= Average values (use for things like interest rates)¶" &
"→ ""ANZ""= Average non-zero values (also use for interest rates)",
"→", "¶" )
),
LET(
// Set Defaults
EndDates, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Intervals, IF( ISOMITTED( Repeats), IF( Rates, "O"), TOCOL( Repeats)),
Beginning?, IF( ISOMITTED( Beginning?), TRUE, Beginning?),
AggrType, IF( ISOMITTED( AggrType), "Sum", AggrType),
// Set orientation
Rates, TOCOL( Rates),
RateDates, TOCOL( RateDates),
// Derive Timeline
Timelines, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
MEnds, CHOOSEROWS( Timelines, 2),
StartDate, TAKE( MStarts, 1, 1),
EndDate, TAKE( MEnds, 1, -1),
Months, MonthDifλ( StartDate, EndDate) + 1,
Counter, SEQUENCE( , @Months, 0),
VStarts, +EDATE( +StartDate, +Counter),
Values, λSchedRates( @StartDate, @Months, Rates, RateDates, Intervals, @Beginning?),
// Adapt to model Timeline
Results, λAdaptToTimeline( Values, AggrType, VStarts, MStarts, MEnds),
Values
)
)
);
/* FUNCTION NAME: StartStopλ
DESCRIPTION:*//**Create a row of flags indicating when something is effective based on start/stop dates*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline
REVISIONS: Date Developer Description
Jul 14 2026 Craig Hatmaker Copyright
*/
StartStopλ = LAMBDA(
[Timeline], // The model's timeline
[EndDates?], // Flag: TRUE (Default) timeline display's period end dates vs start dates (FALSE)
[StartStopDates],
IF( OR( ISOMITTED( Timeline),
ISOMITTED( StartStopDates)
),
TRIM(TEXTSPLIT(
"FUNCTION: →StartStopλ( Timeline, [EndDates?], StopStartDates)¶" &
"DESCRIPTION: →Create a row of flags indicating when something is effective¶" &
"→ based on start/stop dates¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶"&
"VERSION: →BXL: Jul 14, 2026¶"&
"PARAMETERS:→¶"&
" Timeline →(Required) A row of dates used for the model's timeline.¶"&
" EndDates? →(Optional) TRUE/FALSE. Timeline display end dates (TRUE, Default)¶"&
"→ or start dates (FALSE).¶"&
" StartStopDates→(Required) An array of dates indicating when things start and stop¶" &
"→ Odd entries (1st, 3rd, 5th, etc.) indicate start dates. ¶" &
"→ Even entries (2nd, 4th, 6th, etc.) indicate stop dates.",
"→","¶"
)
),
LET(
// Set Defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
// Set orientation
StartStopDates, TOCOL( StartStopDates),
// Derive Timeline
Timelines, λDeriveTimeline( Timeline, EndDates?),
MStarts, CHOOSEROWS( Timelines, 1),
// Calculate Starts and stops
StrStpPrds, SEQUENCE( COUNT( StartStopDates)),
StrStpRow, XLOOKUP( MStarts, StartStopDates, StrStpPrds, 0, -1),
StrStpFlgs, N( ISODD( StrStpRow)),
StrStpFlgs
)
)
);
// Label functions
/* FUNCTION NAME: DebtLabelsλ
DESCRIPTION:*//**Create line lables for debt schedules other than PIK*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline
REVISIONS: Date Developer Description
Aug 21 2026 Craig Hatmaker Copyright
*/
DebtLabelsλ = LAMBDA(
[Flex?],
[Simple?],
[Arrears?],
[Currency],
[LoanNames],
IF( OR( ISOMITTED( Flex?),
ISOMITTED( Simple?),
ISOMITTED( Arrears?)
),
TRIM(TEXTSPLIT(
"FUNCTION: →DebtLabelsλ( Flex?, Simple?, [Arrears?], [Currency], [LoanName])¶" &
"DESCRIPTION: →Create line labels for debt schedules other than PIK¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Aug 21, 2026¶" &
"PARAMETERS:→¶" &
" Flex? →(Required) TRUE indicates a flexible facility (payments scheduled manually).¶" &
"→ FALSE indicates an amortized loan (payment calculated on APR & term).¶" &
" Simple? →(Required) TRUE indicates the loan has simple interest,¶" &
"→ FALSE indicates accrued interest is capitalized.¶" &
" Arrears? →(Optional) TRUE (Default) indicates interest is on debt opening balance¶" &
"→ FALSE indicates interest is based on opening plus draw.¶" &
" LoanNames →(Optional) One or more names for each loan",
"→","¶"
)
),
LET(
// Set Defaults
Arrears?, IF( ISOMITTED( Arrears?), TRUE, Arrears? = TRUE),
LoanNames, IF( ISOMITTED( LoanNames), "", LoanNames),
// Set Term Simple Arrears Format - Includes extra payments. Interest on open only
fnTS, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( " Less extra payments", "", Currency),
HSTACK( "Ending debt balance", "", Currency),
{"","",""},
HSTACK( "Opening interest balance", "", Currency),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Less interest payments", "", Currency),
HSTACK( "Ending interest balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
// Set Term Capitalized Arrears Format - Includes extra payments. Interest on open only
fnTCAr, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Less interest payments", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( " Less extra payments", "", Currency),
HSTACK( "Ending debt balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
// Set Term Capitalized Advance Format - Includes extra payments. Interest on open + draw
fnTCAd, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Less interest payments", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( " Less extra payments", "", Currency),
HSTACK( "Ending debt balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
// Set Flex Simple Arrears Format Excludes extra payments. Interest on open only
fnFS, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( "Ending debt balance", "", Currency),
{"","",""},
HSTACK( "Opening interest balance", "", Currency),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Less interest payments", "", Currency),
HSTACK( "Ending interest balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
// Set Flex Capitalized Arrears Format - Excludes extra payments. Interest on open only
fnFCAr, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Less interest payments", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( "Ending debt balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
// Set Flex Capitalized Advance Format - Excludes extra payments. Interest on open + draws
fnFCAd, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Less interest payments", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( "Ending debt balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
Count, COUNTA( LoanNames),
Counter, SEQUENCE(Count),
Result, REDUCE( 0, Counter,
LAMBDA( Acc, n,
LET(
LoanName, INDEX( LoanNames, n),
Loan, IF( LoanName = "", "", LoanName & " "),
Type, N( Flex?) & N( Simple?) & N( Arrears?),
Choice, XMATCH( Type, {"000","001","010","011","100","101","110","111"}, 0),
Block, CHOOSE( Choice,
fnTCAd( Loan, n),
fnTCAr( Loan, n),
fnTS( Loan, n),
fnTS( Loan, n),
fnFCAd( Loan, n),
fnFCAr( Loan, n),
fnFS( Loan, n),
fnFS( Loan, n),
),
Result, IF( n = 1, Block, VSTACK( Acc, Block)),
Result
)
)
),
Result
)
)
);
/* FUNCTION NAME: InputLabelsλ
DESCRIPTION:*//**Create input labels for debt schedule functions*/
/* REVISIONS: Date Developer Description
Aug 21 2026 Craig Hatmaker Copyright
Sep 05 2026 Craig Hatmaker Added inline Help
*/
InputLabelsλ = LAMBDA(
[LoanType],
IF( ISOMITTED( LoanType),
TRIM(TEXTSPLIT(
"FUNCTION: →InputLabelsλ( LoanType)¶" &
"DESCRIPTION: →Create line labels for debt schedule functions¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Sep 05, 2026¶" &
"PARAMETERS:→¶" &
" LoanType →(Required) Must be one of these:¶" &
"→ Balloon¶" &
"→ Bullet¶" &
"→ Flex¶" &
"→ PIK¶" &
"→ Term",
"→","¶"
)
),
LET(
// Associate functions with Arguments
BalloonInps, {"Draw Amounts","Draw Dates","Maturity Date","APRs","DCC","Simple?",
"Payment Amounts","All Positive?"},
BulletInps, {"Draw Amounts","Draw Dates","Maturity Date","APRs","DCC","Simple?","All Positive?"},
FlexLoanInps, {"Draw Amounts","Draw Dates","Draw Repeats","Maturity Date","APRs","APR Dates","DCC","Simple?",
"Arrears?","Payment Amounts","Payment Dates","Payment Repeats","Payment Holidays",
"Interest Charge Holidays","Interest Payment Holidays","All Positive?"},
PIKInps, {"Draw Amounts","Draw Dates","Draw Repeats","Maturity Date",
"PIKAPRs","PIKDates","Cash Splits","DCC","Arrears?","All Positive?"},
TermLoanInps, {"Draw Amounts","Draw Dates","Draw Repeats","Maturity Date","APRs","APR Dates","DCC","Simple?",
"Arrears?","Extra Amounts","Extra Dates","Extra Repeats","Extra Recast?",
"Payment Holidays","Interest Charge Holidays","Interest Payment Holidays","All Positive?"},
LoanChoice, XMATCH( LoanType, {"Balloon", "Bullet", "Flex", "PIK", "Term"}, 0),
Arguments, CHOOSE( LoanChoice, BalloonInps, BulletInps, FlexLoanInps, PIKInps, TermLoanInps),
Block, REDUCE( "", Arguments,
LAMBDA( Acc, Argument, VSTACK( Acc, Argument))
),
DROP( IFNA( Block, ""), 1)
)
)
);
/* FUNCTION NAME: PIKLabelsλ
DESCRIPTION:*//**Create line lables for PIK debt schedules*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
λDeriveTimeline
REVISIONS: Date Developer Description
Aug 21 2026 Craig Hatmaker Copyright
*/
PIKLabelsλ = LAMBDA(
[Arrears?],
[Currency],
[LoanNames],
IF( ISOMITTED( Arrears?),
TRIM(TEXTSPLIT(
"FUNCTION: →PIKLabelsλ( Flex?, Simple?, [Arrears?], [Currency], [LoanName])¶" &
"DESCRIPTION: →Create line labels for debt schedule functions¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" &
"VERSION: →BXL: Aug 21, 2026¶" &
"PARAMETERS:→¶" &
" Flex? →(Required) TRUE indicates a flexible facility (payments scheduled manually).¶" &
"→ FALSE indicates an amortized loan (payment calculated on APR & term).¶" &
" Simple? →(Required) TRUE indicates the loan has simple interest,¶" &
"→ FALSE indicates accrued interest is capitalized.¶" &
" Arrears? →(Optional) TRUE (Default) indicates interest is on debt opening balance¶" &
"→ FALSE indicates interest is based on opening plus draw.¶" &
" LoanNames →(Optional) One or more names for each loan",
"→","¶"
)
),
LET(
// Set Defaults
Arrears?, IF( ISOMITTED( Arrears?), TRUE, Arrears? = TRUE),
LoanNames, IF( ISOMITTED( LoanNames), "", LoanNames),
// In Arrears
fnArrears, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Interest Rate", "", "%"),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Cash Split", "", "%"),
HSTACK( " Less interest payments", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( "Ending debt balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
// In Advance
fnAdvance, LAMBDA( LoanName, LoanNumber,
LET(
Stack, VSTACK(
HSTACK( LoanName & "Opening debt balance", "", Currency),
HSTACK( " Plus draws", "", Currency),
HSTACK( " Interest Rate", "", Currency),
HSTACK( " Plus interest charges", "", Currency),
HSTACK( " Cash Split", "", Currency),
HSTACK( " Less interest payments", "", Currency),
HSTACK( " Less repayments", "", Currency),
HSTACK( "Ending debt balance", "", Currency)
),
IF( LoanNumber > 1, VSTACK( {"","",""}, Stack), Stack)
)
),
Count, COUNTA( LoanNames),
Counter, SEQUENCE(Count),
Result, REDUCE( 0, Counter,
LAMBDA( Acc, n,
LET(
LoanName, INDEX( LoanNames, n),
Loan, IF( LoanName = "", "", LoanName & " "),
Block, IF( Arrears?, fnArrears( Loan, n), fnAdvance( Loan, n)),
Result, IF( n = 1, Block, VSTACK( Acc, Block)),
Result
)
)
),
Result
)
)
);
// Internal functions
/* 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
Jun 18 2026 Craig Hatmaker Fixed Avg, ANZ, Sum
Jun 26 2026 Craig Hatmaker Fixed working with end dates.
*/
λAdaptToTimeline = LAMBDA(
// Parameter Declarations
Values, // Values to place in the timeline
Operation, // Opn:First period's value, Cls:Last period's value, Avg:Average, ANZ:Average non-zero, Sum:Total, SPC:Spacer
ValueDates, // Date for each values column
ModelStarts, // Model's timeline period starts
ModelEnds, // Model's timeline period ends
LET(
// Pre-calcs
Rows, ROWS( Values),
Cols, COLUMNS( ModelStarts),
Effective, ( ModelStarts <= TAKE( ValueDates, , -1)) *
( ModelEnds >= TAKE( ValueDates, , 1)),
// Define functions that handle processing each row type.
Functions, MATCH( Operation, {"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),
Line, CHOOSE( Function,
// Open
XLOOKUP( ModelStarts, ValueDates, Row, 0, 1) * Effective,
// Close
XLOOKUP( ModelEnds, ValueDates, Row, 0, -1) * Effective,
// Average
MAP( ModelStarts, ModelEnds,
LAMBDA( MStart, MEnd,
LET(
Include, (MStart <= ValueDates) *
(MEnd >= ValueDates),
IF( SUM( Include) = 0,
0,
AVERAGE( FILTER( Row, Include))
)))),
// Average non-zero
MAP( ModelStarts, ModelEnds,
LAMBDA( MStart, MEnd,
LET(
Include, (MStart <= ValueDates) *
(MEnd >= ValueDates) *
(Row <> 0),
IF( SUM( Include) = 0,
0,
AVERAGE( FILTER( Row, Include))
)))),
// Sum
MAP( ModelStarts, ModelEnds,
LAMBDA( MStart, MEnd,
SUM(( MStart <= ValueDates) * (MEnd >= ValueDates) * Row))) ,
// Space
IF( ModelStarts, "")
),
Result, IF( r = 1, Line, VSTACK( Acc, Line)),
Result
)
)
),
Result
)
);
/* FUNCTION NAME: λDCC
DESCRIPTION:*//**USE - DCCλ - This is not intended to be called from Excel's grid
Create an array of DCC days in period, DCC days in year, APRs, and resulting DCC period rates*/
/* REVISIONS: Date Developer Description
Jul 10 2026 Craig Hatmaker Copyright
*/
λDCC = LAMBDA(
StartDates, // Period start dates of a model's timeline or internal timeline
EndDates, // Period end dates of a model's timeline or internal timeline
APRs, // A single APR to apply to all dates, or a row of APRs for each date
DCC, // Day Count Convention: 1=30/360, 2=Actual/360, 3=Actual/365, or 4=Actual/Actual.
LET(
// Determine period interval
PeriodDays, EndDates - StartDates + 1,
DCCDays, IF( DCC <> 1,
PeriodDays,
LET(
Interval, ROUND( PeriodDays/30, 0),
Result, Interval * 30,
Result
)
),
// Calculate DCC rates
YearDays, CHOOSE( DCC,
360,
360,
365,
365 + (MONTH( DATE( YEAR( StartDates), 2, 29)) = 2)),
DPRs, APRs * DCCDays / YearDays,
Result, VSTACK( DCCDays, IF(DCCDays, YearDays), APRs, DPRs),
Result
)
);
/* FUNCTION NAME: λDeriveTimeline
DESCRIPTION:*//**Create internal start and end timelines based on the model's timeline*/
/* REVISIONS: Date Developer Description
Jul 24 2026 Craig Hatmaker Original Development and copyright
*/
λDeriveTimeline = LAMBDA(
// Parameter Declarations
ModelTimeline, // Either the model's start or end timeline
EndDates?, // TRUE (default) = The model's timeline displays period end dates
LET(
// Set Constants
IntervalDays, {1;7;28;88;360},
IntervalPeriod, {"D";"W";"M";"Q";"Y"},
// Get First and Last Periods
FirstPeriod, TAKE( ModelTimeline, , 1),
LastPeriod, TAKE( ModelTimeline, , -1),
// Determine how many days are in model's periods
DpP, INDEX( ModelTimeline, 2) - FirstPeriod,
Interval, XLOOKUP( DpP, IntervalDays, IntervalPeriod, ,-1),
// Select interval formula
DateFunction, IF( EndDates?,
IF( MONTH( FirstPeriod) = MONTH( FirstPeriod + 1), EDATE, EOMONTH),
IF( DAY( FirstPeriod) = 1, EOMONTH, EDATE)
),
// Calculate period to add to either the beginning or end of model's timeline
NewPeriod, IF( EndDates?,
SWITCH( Interval,
"D", FirstPeriod - 1,
"W", FirstPeriod - 6,
"M", DateFunction( FirstPeriod, -1),
"Q", DateFunction( FirstPeriod, -3),
"Y", DateFunction( FirstPeriod, -12)
),
SWITCH( Interval,
"D", LastPeriod + 1,
"W", LastPeriod + 6,
"M", DateFunction( LastPeriod, 1),
"Q", DateFunction( LastPeriod, 3),
"Y", DateFunction( LastPeriod, 12)
)
),
// Extend Model Timeline
Extended, IF( EndDates?,
HSTACK( NewPeriod, ModelTimeline),
HSTACK( ModelTimeline, NewPeriod)
),
// Calculate End Dates
Starts, IF( EndDates?, DROP( Extended, ,-1) + 1, ModelTimeline),
Ends, IF( EndDates?, ModelTimeline, DROP( Extended, , 1) -1),
// Procedure
Result, VSTACK(Starts, Ends, IF(Starts, Interval)),
Result
)
);
/* FUNCTION NAME: λFlexInterest
DESCRIPTION:*//**USE - FlexInterestλ - This is not intended to be called from Excel's grid*/
/* REVISIONS: Date Developer Description
Aug 07 2026 Craig Hatmaker Copyright
*/
λFlexInterest = LAMBDA(
DebtBalances, // If Arrears?, opening balance, else, opening + Draws
StartDate,
MaturityDate,
MIRs, // DCC adjusted monthly Interest rates in a row
IntChgFlags, // Charge Interest Flags (Compound Frequency * Grace periods & Holidays)
IntPmtFlags, // Pay Interest Flags (Repeats * Grace periods & Holidays)
AllPositive?,
// This function has no help and no defaults as it is intended to be called from a 5g function
LET(
// Timing Calculations
TotalMonths, MonthDifλ( StartDate, MaturityDate) + 1,
Periods, SEQUENCE( , TotalMonths),
VStarts, EDATE( StartDate, Periods - 1),
VEnds, EDATE( StartDate, Periods) -1,
// Precalcs
Sign, IF( ISOMITTED( AllPositive?), -1, IF( AllPositive?, 1, -1)),
// Calculate Monthly charges and paymenta
IntChgs, DebtBalances * MIRs * IntChgFlags,
IntCloses, SCAN( 0, Periods,
LAMBDA( Acc, Period,
LET(
IntOpen, Acc,
IntPmtFlg, INDEX( IntPmtFlags, Period),
IntChg, INDEX( IntChgs, Period),
IntPmt, (IntOpen + IntChg) * IntPmtFlg,
IntClose, IntOpen + IntChg - IntPmt,
IntClose
)
)
),
IntOpens, HSTACK( 0, DROP( IntCloses, , -1)),
IntPmts, (IntOpens + IntChgs) * IntPmtFlags * Sign,
Values, VSTACK( IntOpens, IntChgs, IntPmts, IntCloses),
Values
)
);
/* FUNCTION NAME: λFlexRepay
DESCRIPTION:*//**USE - FlexRepayλ - This is not intended to be called from Excel's grid*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
MonthDifλ, λSchedRates, λStartStop
REVISIONS: Date Developer Description
Jul 13 2026 Craig Hatmaker Copyright
*/
λFlexRepay = LAMBDA(
DrawAmounts,
DrawDates,
DrawRepeats,
MaturityDate,
RepayAmounts,
RepayDates,
RepayRepeats,
RepayHolidays,
AllPositive?,
// This function has no help and no defaults as it is intended to be called from a 5g function
LET(
StartDate, TAKE( DrawDates, 1, 1),
Sign, IF( AllPositive?, 1, -1),
// Preliminary Calculations
TotalMonths, MonthDifλ( StartDate, MaturityDate) + 1,
Periods, SEQUENCE( , TotalMonths),
VStarts, EDATE( StartDate, Periods - 1),
// Schedule Draw Amounts
Draws, λSchedRates( StartDate, @TotalMonths, +DrawAmounts, DrawDates, DrawRepeats, TRUE),
// Schedule Repay Amounts
RepayAmounts, λSchedRates( StartDate, @TotalMonths, +RepayAmounts, RepayDates, RepayRepeats, TRUE),
RepayHolidays, λStartStop( VStarts, RepayHolidays),
Repays, RepayAmounts * RepayHolidays,
// Create corkscrew
Thunk, LAMBDA( x, LAMBDA( x)),
Changes, BYCOL( VSTACK( Periods, Draws, Repays), Thunk),
Closes, SCAN( 0, Changes,
LAMBDA( Acc, Change,
LET(
Period, INDEX( Change(), 1),
Draw, INDEX( Change(), 2),
Repay, INDEX( Change(), 3),
Result, IF( Period = TotalMonths,
0,
MAX( 0, Acc + Draw - Repay)),
Result
)
)
),
Opens, HSTACK( 0, DROP( Closes, , -1)),
RecalcRepays, Opens + Draws - Closes,
Values, VSTACK( Opens, Draws, Sign * RecalcRepays, Closes),
Values
)
);
/* FUNCTION NAME: λIntervals
DESCRIPTION:*//**USE - Intervalsλ - This is not intended to be called from Excel's grid*/
/* REVISIONS: Date Developer Description
Jul 14 2026 Craig Hatmaker Copyright
*/
λIntervals = LAMBDA(
Months, // Number of months
Interval, // How often something repeats
Beginning?, // Place flag at beginning or ending of interval
// This function has no help and no defaults as it is intended to be called from a 5g function
LET(
// Determine calendars
Periods, SEQUENCE( , Months),
// Calculate flags
IntervalMonths, XLOOKUP( Interval, {"M","Q","S","Y","A"}, {1,3,6,12,12}),
Flags, IF( IntervalMonths = 1,
IF( Periods, 1),
N( MOD( Periods, IntervalMonths) = IF( Beginning?, 1, 0))
),
Flags
)
);
/* FUNCTION NAME: λLoanPrep
DESCRIPTION:*//**Create all rows required to process flex based loans*/
/* REQUIREMENTS: This function relies on the following functions included in this libary:¶"&
MonthDifλ, λSchedRates, λStartStop
REVISIONS: Date Developer Description
Aug 10 2026 Craig Hatmaker Copyright
*/
λLoanPrep = LAMBDA(
[DrawAmounts], // A single value or array of Draw Amounts
[DrawDates], // A single value or array of dates indicating when those Draws occur
[DrawRepeats], // A single value or array of how often those Draws repeat
[MaturityDate], // Term date of loan
[APRs], // A single value or array of APRs
[APRDates], // A single value or array of dates indicating when those APR rates are in effect
[DCC], // Day Count Convention
[PaymentAmounts], // A single value or array of Payment Amounts
[PaymentDates], // A single value or array of dates indicating when Payment Amounts are in effect
[PaymentRepeats], // A single value or array of how often those Payments repeat
[ExtraAmounts], // A single value or array of Extra Amounts
[ExtraDates], // A single value or array of dates indicating when those Extra Amounts occur
[ExtraRepeats], // A single value or array of how often those Extra Amounts repeat
[ExtraReCast?], // A flag indicating Extra Payments should cause Payment to be recalculated
[PaymentHolidays], // A single value or array of Payment grace and holiday period start and stop dates
[IntChgHolidays], // A single value or array of Interest Charges grace and holiday period start and stop dates
[IntPmtHolidays], // A single value or array of Interst Payment grace and holiday period start and stop dates
[Arrears?], // A flag indicating interest is on arrears or advance
LET(
// Convert Text Dates to Dates
MaturityDate, VALUE( MaturityDate),
// Set Orientation
DrawAmounts, TOCOL( DrawAmounts),
// Create function to check if an argument was omitted or the wrong type
Omitted?, LAMBDA( Argument, ValidTypes,
OR( ISOMITTED( Argument), ISNA( XMATCH( TYPE( Argument), ValidTypes)))),
// Set defaults and conform schedules to bank's schedule
StartDate, VALUE( TAKE( DrawDates, 1, 1)),
InterestDate, IF( Arrears?, EDATE( StartDate, 1), StartDate),
DrawDates, TOCOL( DATE( YEAR( DrawDates), MONTH( DrawDates), DAY( StartDate))),
DrawRepeats, TOCOL( IF( ISOMITTED( DrawRepeats), "O", DrawRepeats)),
PaymentAmounts, TOCOL( IF( ISOMITTED( PaymentAmounts), 0, PaymentAmounts)),
PaymentDates, TOCOL( IF( ISOMITTED( PaymentDates), EDATE( StartDate, 1), DATE( YEAR( PaymentDates), MONTH( PaymentDates), DAY( StartDate)))),
PayMentRepeats, TOCOL( IF( ISOMITTED( PayMentRepeats), "M", PayMentRepeats)),
ExtraAmounts, TOCOL( IF( ISOMITTED( ExtraAmounts), 0, ExtraAmounts)),
ExtraDates, TOCOL( IF( ISOMITTED( ExtraDates), StartDate, DATE( YEAR( ExtraDates), MONTH( ExtraDates), DAY( StartDate)))),
ExtraRepeats, TOCOL( IF( ISOMITTED( ExtraRepeats), "O", ExtraRepeats)),
ExtraReCast?, TOCOL( IF( ISOMITTED( ExtraReCast?), TRUE, ExtraReCast? = TRUE)),
APRDates, TOCOL( IF( ISOMITTED( APRDates),
StartDate,
DATE( YEAR( APRDates), MONTH( APRDates), DAY( StartDate)))),
DCC, IF( ISOMITTED( DCC), 3, DCC),
PaymentHolidays,TOCOL( IF( Omitted?( PaymentHolidays, {1, 64}), MIN( PaymentDates), PaymentHolidays)),
IntChgHolidays, TOCOL( IF( Omitted?( IntChgHolidays, {1, 64}), InterestDate, IntChgHolidays)),
IntPmtHolidays, TOCOL( IF( Omitted?( IntPmtHolidays, {1, 64}), MIN( IntChgHolidays), IntPmtHolidays)),
Arrears?, IF( ISOMITTED( Arrears?), TRUE, Arrears?),
// Internal Calendar
TotalMonths, MonthDifλ( StartDate, MaturityDate) + 1,
Periods, SEQUENCE( , @TotalMonths),
VStarts, EDATE( StartDate, Periods - 1),
VEnds, EDATE( StartDate, Periods) -1,
// Rows
Draws, λSchedRates( StartDate, @TotalMonths, +DrawAmounts, DrawDates, DrawRepeats, TRUE),
Extras, λSchedRates( StartDate, @TotalMonths, +ExtraAmounts, ExtraDates, ExtraRepeats, TRUE),
Payments, λSchedRates( StartDate, @TotalMonths, +PaymentAmounts, PaymentDates, PaymentRepeats, TRUE),
APRs, λSchedRates( StartDate, @TotalMonths, +TOCOL(APRs), APRDates, "M", FALSE),
MIRs, TAKE( λDCC( VStarts, VEnds, APRs, @DCC), -1),
// Flags
PaymentFlags, λStartStop( VStarts, PaymentHolidays),
IntChgFlags, λStartStop( VStarts, IntChgHolidays),
IntPmtFlags, λStartStop( VStarts, IntPmtHolidays),
// Recast Flags
RecastDraw, Draws <> 0,
RecastExtra, (Extras <> 0) * ExtraRecast?,
RecastFlags, N(((Periods = 1) +
RecastDraw +
RecastExtra +
HSTACK( 1, DROP( NOT( PaymentFlags), , -1)) * PaymentFlags +
HSTACK( 1, DROP( NOT( IntChgFlags), , -1)) * IntChgFlags +
HSTACK( 1, DROP( NOT( IntPmtFlags), , -1)) * IntPmtFlags) > 0),
// Return Result
VSTACK( Periods, VStarts, VEnds, PaymentFlags, IntChgFlags, IntPmtFlags,
RecastFlags, Draws, Extras, Payments, APRs, MIRs)
)
);
/* FUNCTION NAME: λPmtA
DESCRIPTION:*//**USE PmtAλ - This is not intended to be called from Excel's grid*/
/* REVISIONS: Date Developer Description
Aug 10 2026 Craig Hatmaker Copyright
*/
λPmtA = LAMBDA(
[Starts], // Remaining months start dates
[Ends], // Remaining months end dates
[DebtBalance], // Amount of debt balance
[APR], // Annual interest rate
[DCC], // Day Count convention
LET(
// Calculate DCC
MonthDays, IF( DCC = 1, IF( Starts, 30), Ends - Starts + 1),
YearDays, CHOOSE( DCC,
360,
360,
365,
365 + (MONTH( DATE( YEAR( Starts), 2, 29)) = 2)),
// Determine Payment
Factors, 1 / ( 1 + ( APR * MonthDays / YearDays)),
Discounts, SCAN( 1, Factors, LAMBDA( Acc, Factor, Acc * Factor)),
Payment, DebtBalance / SUM( Discounts),
Payment
)
);
/* FUNCTION NAME: λSchedRates
DESCRIPTION:*//**USE - SchedRatesλ - This is not intended to be called from Excel's grid**/
/* REVISIONS: Date Developer Description
Jul 15 2026 Craig Hatmaker Copyright
*/
λSchedRates = LAMBDA(
StartDate, // Start Date from which to schedule rates
Months, // Number of months to schedule out from start date
RateAmounts, // One or more rates
RateDates, // A date when each rate starts, replacing prior the rate
RateIntervals, // How often the rate repeats
Beginning?, // Flag to indicate if rates are placed at interval start or end
// This function has no help and no defaults as it is intended to be called from a 5g function
LET(
// Set internal timeline
Periods, SEQUENCE( , Months, 0),
VStarts, EDATE( +StartDate, +Periods),
// Convert Rate Dates to internal timeline's day of month.
RateDates, DATE( YEAR( RateDates), MONTH( RateDates), DAY( StartDate)),
// Make sure period begin/end flag is set to begin for one-time intervals
Beginnings, IF( RateIntervals = "O", TRUE, Beginning?),
// Determine periods each rate spans
EffectivePrds, IF( RateIntervals = "O",
VStarts = RateDates,
XLOOKUP( VStarts, RateDates, RateDates, 0, -1) = RateDates),
// Determine number of months from effective date to each effective period
PeriodCounters, (VStarts >= RateDates) * (MonthDifλ( RateDates, VStarts) + 1),
// Convert Interval Codes to months
IntervalMonths, XLOOKUP( RateIntervals, {"O","M","Q","S","Y","A"}, {999999,1,3,6,12,12}),
// Determine which periods are on interval months
IntervalSchds, IF( IntervalMonths = 1,
TRUE,
MOD( PeriodCounters, IntervalMonths) = IF( Beginnings, 1, 0)),
// Create an array where each row schedules one of the rates
RatesArray, EffectivePrds * IntervalSchds * RateAmounts,
// Reduce the Rates Array into a single row
Results, BYCOL( RatesArray, LAMBDA( Col, SUM( Col))),
Results
)
);
/* FUNCTION NAME: λStartStop
DESCRIPTION:*//**Create a row of flags indicating when something is effective based on start/stop dates*/
/* REVISIONS: Date Developer Description
Jul 22 2026 Craig Hatmaker Copyright
*/
λStartStop = LAMBDA(
[Timeline],
[StartStopDates],
// This function has no help and no defaults as it is intended to be called from a 5g function
LET(
// Calculate Starts and stops
StrStpPrds, SEQUENCE( COUNT( StartStopDates)),
StrStpRow, XLOOKUP( Timeline, StartStopDates, StrStpPrds, 0, -1),
StrStpFlgs, N( ISODD( StrStpRow)),
StrStpFlgs
)
);
// Help snippets
λAllPositiveHelp =
" AllPositive? →(Optional) TRUE (Default) Display reductions as positive vs. negative (FALSE)¶";
λArrearsHelp =
" Arrears? →(Optional) TRUE (default) Base interest on opening debt vs. opening plus draw (FALSE).¶";
λAPRHelp =
" APRs →(Required) One or more annual interest percentage rates.¶" &
" APRDates →(Required/Optional) An effective date for each APR indicating when its¶" &
"→ associated APR starts, replacing any prior APRs. If only 1 APR provided, ¶" &
"→ and this is omitted, this defaults to first draw date. ¶" &
"→ NOTE! NOT MEANT for grace periods or holidays. Use IntChgHolidays instead.¶" &
"→ NOTE! It is assumed the first draw date is the first date of the bank's¶" &
"→ loan calendar; thus, the 'day' in these dates is ignored.¶";
λDCCHelp =
" DCC →(Optional) Day-count convention selector:¶" &
"→ 1 = 30/360, ¶" &
"→ 2 = Actual/360, ¶" &
"→ 3 = (default) Actual/365, ¶" &
"→ 4 = Actual/Actual.¶" &
"→ NOTE! Month days are days in periods determined by first draw date.¶";
λDrawHelp =
" DrawAmounts →(Required) One or more draw amounts.¶" &
" DrawDates →(Required) A date for each draw amount entry.¶" &
"→ NOTE! It is assumed the first draw date is the first date of the bank's¶" &
"→ loan calendar. Subsequent draws should occur on that day (no stubs).¶" &
" DrawRepeats →(Optional) How often each draw amount repeats:¶" &
"→ O=One time (does not repeat) (Default)¶" &
"→ M=Monthly¶" &
"→ Q=Quarterly¶" &
"→ S=Semi-Annually¶" &
"→ Y or A = Yearly/Annually¶" &
"→ NOTE! To stop repetitions, schedule a 0 amount after last repetition.¶";
λExtraHelp =
" ExtraAmounts →(Optional) One or extra payment amounts.¶" &
" ExtraDates →(Optiona/Required) A date for each extra amount entry.¶" &
"→ This is required for each extra amount entry.¶" &
"→ NOTE! The day of the month is ignored.¶" &
" ExtraRepeats →(Optional) How often each extra payment repeats:¶" &
"→ O=One time (does not repeat) (Default)¶" &
"→ M=Monthly¶" &
"→ Q=Quarterly¶" &
"→ S=Semi-Annually¶" &
"→ Y or A = Yearly/Annually¶" &
"→ NOTE! To stop repetitions, schedule a 0 amount after last repetition.¶";
λExtraRecastHelp =
" ExtraRecast? →(Optional) Flag: TRUE (Default) Lower payment to preserve loan term vs.¶" &
"→ Keep original payment amount and decrease term (FALSE).¶";
λHolidayOptionHelp =
" HolidayOption →(Optional) How to handle grace or holiday exits:¶" &
"→ R=Recast/Recalculation monthly payment (default)¶" &
"→ E=Extend maturity date¶" &
"→ L=Lump sum catch up payment¶";
λInterestHolidayHelp =
" IntChgHolidays→(Optional) When interest charges start/stop.¶" &
"→ Use this to create a interest charges grace period or holidays.¶" &
"→ Odd entries (1st, 3rd, 5th, etc.) start payments. To create a grace period, delay the 1st start.¶" &
"→ Even entries (2nd, 4th, 6th, etc.) stop payments. These start holiday periods¶" &
"→ In Arrears processing this defaults to the month after first draw.¶" &
"→ In Advance processing this defaults to first draw date.¶" &
" IntPmtHolidays→(Optional) When interest payments start/stop.¶" &
"→ Use this to create a pay interest grace period or holidays.¶" &
"→ Odd entries (1st, 3rd, 5th, etc.) start payments. To create a grace period, delay the 1st start.¶" &
"→ Even entries (2nd, 4th, 6th, etc.) stop payments. These start holiday periods¶" &
"→ The default is the first charg interest month.¶";
λMaturityHelp =
" MaturityDate →(Required) Date when entire debt and interest balance is paid¶" &
"→ NOTE! The maturity date should be on a bank calendar day which this function¶" &
"→ assumes is the same day of the month as the draw date.¶";
λPaymentHelp =
" PaymentAmounts→(Optional) One or more payment amounts.¶" &
" PaymentDates →(Optional) When payments start. ¶" &
"→ In Arrears processing this defaults to the month after first draw.¶" &
"→ In Advance processing this defaults to first draw date.¶" &
" PaymentRepeats→(Optional) How often payments are made. Indicate using:¶" &
"→ M=Monthly (Default)¶" &
"→ Q=Quarterly¶" &
"→ S=Semi-Annually¶" &
"→ Y or A = Yearly/Annually¶" &
"→ NOTE! To stop repetitions, schedule a 0 amount after last repetition.¶" &
λPaymentHolidayHelp;
λPaymentHolidayHelp =
" PaymentHolidays→(Optional) When payments/repayments start/stop.¶" &
"→ Use this to create a payment grace period or holidays.¶" &
"→ Odd entries (1st, 3rd, 5th, etc.) start payments. To create a grace period, delay the 1st start.¶" &
"→ Even entries (2nd, 4th, 6th, etc.) stop payments. These start holiday periods¶" &
"→ In Arrears processing this defaults to the month after first draw.¶" &
"→ In Advance processing this defaults to first draw date.¶";
λSimpleHelp =
" Simple? →(Optional) TRUE (Default) unpaid interest is paid from cash vs added to debt (FALSE)¶";
λTimelineHelp =
" Timeline →(Required) A row of dates used for the model's timeline.¶" &
" EndDates? →(Optional) TRUE/FALSE. Timeline display end dates (TRUE, Default)¶" &
"→ or start dates (FALSE).¶";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment