Introduction#
How to use one single formula to hack Debt schedule with CashSwept. This technique utilises the latest Excel dynamic array features to achieve low errors and resilience, however, this approach has a very steep learning curve that might defeat the excel’s principle, visual calculation.
The person who was promoting this called this approach 5G modelling. You can find more here.
Getting Started#
Create a lambda function as below.
DebtSchedule(
[OpenBalance] = Debt Open Balance,\
[CashBalance] = Cash Open Balance,\
[CFArray] = Cashflow Array,\
[MandatoryAmort] = Mandatory Amortisation,\
[CashSweep] = % of available cash after mandatory amortisation allocated to optional amortisation,\
[PIKInterest] = PIK Interest, applied to the opening balance instead of the average balance,\
[CashInterest] = Cash interest applied to the average balance,\
)
Activate Advanced Formula Environment#
Insert the following in your advanced formula environment.
DebtSchedule = LAMBDA(
[OpenBalance],
[CashBalance],
[CFArray],
[MandatoryAmort],
[CashSweep],
[PIKRate],
[CashRate],
LET(
_Title, TOCOL({
"Debt Opening",
"Mandatory Amortisation",
"Optional Amortisation",
"Cash Interest",
"PIK Interest",
"Total Interest",
"Debt Closing",
"Cash Opening",
"Cash Closing"
}),
_Process,
REDUCE(
0, SEQUENCE( 1, COUNTA( CFArray )),
LAMBDA(
Accumulator, n ,
LET(
// Individual values of arrays
_Opening, IF( n=1, OpenBalance, INDEX( CHOOSECOLS( Accumulator, -1 ), 7)),
_CashOpening, IF( n=1, CashBalance, INDEX( CHOOSECOLS( Accumulator, -1 ), 9)),
_CFAvail, INDEX( CFArray, n ),
// Calculations
_MandatoryAmort, - MIN( MandatoryAmort, _Opening) ,
_OptionalAmort, - MIN( _Opening +_MandatoryAmort, _CashOpening + _CFAvail+_MandatoryAmort ) * CashSweep,
_PIKInterest, _Opening * PIKRate,
_Closing, _Opening + _MandatoryAmort + _OptionalAmort + _PIKInterest,
_AvgDebtBalance, AVERAGE(_Closing, _Opening),
_CashInterest, _AvgDebtBalance * CashRate,
_TotalInterest, _PIKInterest + _CashInterest,
_CashClosing, _CashOpening + _CFAvail + _MandatoryAmort + _OptionalAmort -_CashInterest ,
// Formatting the result
_Stack, VSTACK(
_Opening,
_MandatoryAmort,
_OptionalAmort,
_CashInterest,
_PIKInterest,
_TotalInterest,
_Closing,
_CashOpening,
_CashClosing
),
// Result
Result, IF(
n = 1,
_Stack,
HStACK(
Accumulator,
_Stack
)
),
Result
)
)
) ,
HSTACK( _Title, _Process)
))
