Last active
February 3, 2024 00:07
-
-
Save CHatmaker/ea47b2fa510c62023eec6e4403f6e3e8 to your computer and use it in GitHub Desktop.
5G functions for Excel for debt modeling
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 10 2023 Craig Hatmaker Copyright | |
| Feb 02 2024 Craig Hatmaker See DebtSculptλ | |
| */ | |
| Aboutλ = TRIM(TEXTSPLIT( | |
| "About: →BXL's Debt module. Suggested module name: BXLDebt¶" & | |
| "Version: →Aug 10 2023¶" & | |
| "Gist URL: →https://gist.github.com/CHatmaker/ea47b2fa510c62023eec6e4403f6e3e8 ¶" & | |
| "Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" & | |
| "→¶" & | |
| "Function →Description¶" & | |
| "Aboutλ →Produces this table¶" & | |
| "Range2DAλ →Transform a static reference to a dynamic array for use in array calculations.¶" & | |
| "Amortizeλ →Creates an amoritizaton schedule for a debt/loan. ¶" & | |
| "DebtSculptFixedλ →Creates a Debt Sculpting schedule where APR and DSCR is fixed for all periods.¶" & | |
| "DebtSculptVariableλ →Creates a Debt Sculpting schedule where APR and DSCR is set for each period.¶" & | |
| "DebtSculptVariableLRVλ →Creates a Debt Sculpting schedule where APR and DSCR is set for each period (Lance Rubin Variant).¶" & | |
| "InterestLRVλ →Calculates debt sculpting interest using method presented by Lance Rubin", | |
| "→","¶" | |
| ) | |
| ); | |
| Range2DAλ = LAMBDA( | |
| // Parameter Declarations | |
| [CellReference], | |
| [Columns], | |
| [Rows], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION: →Creates a dynamic array from a static reference¶" & | |
| " →so the reference can be used in array calculations.¶" & | |
| " →Created to facilitate converting FAST to FAST+ 5G.¶" & | |
| " →This is the same as: =OFFSET(CellReference, 0, 0, Rows, Columns)¶" & | |
| "VERSION: →Aug 08 2023¶" & | |
| "PARAMETERS:→¶" & | |
| "CellReference →(Required) The range's first (top left) cell reference¶" & | |
| "Columns →(Required) Usually the number of model periods¶" & | |
| "Rows →(Optional) Rows in reference. Defaults to 1¶" & | |
| "→¶" & | |
| "EXAMPLE: →=Range2DAλ($J$10, 60)", | |
| "→", "¶" ) | |
| ), | |
| // Error Messages | |
| ErrMsgs, {"CellReference must be a cell address or named reference"; | |
| "Columns must be the # of columns for the array which is usually the # of model periods"}, | |
| // Check inputs | |
| CellReference, IF(ISERROR(CELL("address", CellReference)), #Value!, INDEX(CellReference,1,1)), | |
| Columns, IF(ISNUMBER(Columns), Columns, #Value!), | |
| Rows, IF(ISNUMBER(Rows), Rows, 1), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors, VSTACK(ISERROR( CellReference), ISERROR( Columns)), | |
| Messages, FILTER(ErrMsgs, Errors, ""), | |
| // Procedure | |
| Result, OFFSET(CellReference, 0, 0, Rows, Columns), | |
| // Handle Error | |
| Return, IF( AND( Errors), 2, IF( OR( Errors), 3, IF( OR( ISERROR( Result)), 2, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Amortizeλ | |
| DESCRIPTION:*//**Creates an amortization schedule for a debt/loan*/ | |
| /* REVISIONS: Date Developer Description | |
| Jun 22 2023 Craig Hatmaker Original Development | |
| Aug 10 2023 Craig Hatmaker Transfered to this module and revised to be 100% self contained | |
| */ | |
| Amortizeλ = LAMBDA( | |
| // Parameter Declarations | |
| [Principal], | |
| [Rate], | |
| [Payment], | |
| [StartPeriod], | |
| [ModelPeriods], | |
| [DoNotUse], | |
| // LET | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION: →Creates an amortization schedule for a debt/loan¶" & | |
| "VERSION: →Aug 10 2023¶" & | |
| "PARAMETERS:→¶" & | |
| "Principal →(Required) Principal Balance.¶" & | |
| "Rate →(Required) APR per Period¶" & | |
| "Payment →(Required) Period Payment amount.¶" & | |
| "StartPeriod →(Optional) Defaults to first period.¶" & | |
| "ModelPeriods →(Required) Number of model periods¶" & | |
| "DoNotUse →DO NOT USE - Internal use only (Period Counter)¶" & | |
| "→¶" & | |
| "EXAMPLES: →Amortizeλ(10000, 3%/12, 1000, 3, 60)", | |
| "→", "¶" ) | |
| ), | |
| // Error Messages | |
| ErrMsgs, {"Principal must be a single number providing the opening debt amount."; | |
| "Rate must be a single number providing interest rate for the period (eg. APR/12)."; | |
| "Payment must be a single number providing the amount repaid in each period"; | |
| "Start Period must be a single number providing the model period when loan is issued."; | |
| "Model Periods must be a single number providing the number of periods in this model."}, | |
| // Check inputs | |
| Principal, IF( ISNUMBER( Principal), Principal, #Value!), | |
| Rate, IF( ISNUMBER( Rate), Rate, #Value!), | |
| Payment, IF( ISNUMBER( Payment), Payment, #Value!), | |
| StartPeriod, IF( ISNUMBER( StartPeriod), StartPeriod, #Value!), | |
| ModelPeriods, IF( ISNUMBER( ModelPeriods), ModelPeriods, #Value!), | |
| // Error Messages | |
| Errors, VSTACK(ISERROR( Principal), ISERROR( Rate), ISERROR( Payment), ISERROR( StartPeriod), ISERROR( ModelPeriods)), | |
| Messages, FILTER(ErrMsgs, Errors, ""), | |
| // Procedure | |
| Period, IF(DoNotUse = 0, 1, DoNotUse), | |
| OpeningBalance, IF(Period < StartPeriod, 0, Principal), | |
| InterestPortion, OpeningBalance * Rate, | |
| NewPayment, IF(Period < StartPeriod, 0, MIN(Payment, OpeningBalance + InterestPortion)), | |
| PrincipalPortion, NewPayment - InterestPortion, | |
| ClosingBalance, OpeningBalance + InterestPortion - NewPayment, | |
| NewColumn, VSTACK(OpeningBalance, InterestPortion, NewPayment, ClosingBalance, PrincipalPortion), | |
| Result, IF( Period = ModelPeriods, | |
| NewColumn, | |
| HSTACK( | |
| NewColumn, | |
| Amortizeλ( | |
| IF(Period < StartPeriod, Principal, ClosingBalance), | |
| Rate, | |
| Payment, | |
| StartPeriod, | |
| ModelPeriods, | |
| Period + 1 | |
| ) | |
| ) | |
| ), | |
| // Handle Error | |
| Error, IF( AND( Errors), 2, IF( OR( Errors), 3, IF( OR( ISERROR( Result)), 2, 1))), | |
| // Return Result | |
| CHOOSE(Error, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: DebtSculptλ | |
| DESCRIPTION:*//**Calculates Debt balance, interest, and debt repayment based on CFAD and DSCR target*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 08 2023 Craig Hatmaker Copyright | |
| Feb 02 2024 Craig Hatmaker Modified for anonymous recursion. | |
| */ | |
| DebtSculptλ = LAMBDA( | |
| // Parameter Declarations | |
| [OpeningBalance], | |
| [Debts], | |
| [CFADs], | |
| [DSCRs], | |
| [APRs], | |
| [Timeline], | |
| // Procedure | |
| LET( | |
| // Inline Help | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION: →Creates a Debt Sculpting schedule where APR and DSCR is set for each period¶" & | |
| "VERSION: →Aug 07 2023¶" & | |
| "PARAMETERS:→¶" & | |
| "OpeningBalance →(Optional) Model's debt on opening. Defaults to 0¶" & | |
| "Debts →(Required) New debt taken on for each period¶" & | |
| "CFADs →(Required) Cash Flow Available for Debt Services¶" & | |
| "DSCRs →(Required) Debt Service Coverage Ratio¶" & | |
| "APRs →(Required) Interest's Annual Percentage Rate¶" & | |
| "Timeline →(Required) The model's timeline.¶" & | |
| "→¶" & | |
| "EXAMPLE: →=DebtSculptVariableλ(, Debt, CFADS, DSCR, APR)¶" & | |
| "SUGGESTED ROW LABELS: →Debt opening balance¶" & | |
| " →Interest¶" & | |
| " →Deby repayments¶" & | |
| " →Debt closing balance", | |
| "→", "¶" ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( Debts), | |
| ISOMITTED( CFADS), | |
| ISOMITTED( DSCRs), | |
| ISOMITTED( APRs) | |
| ), | |
| Help?, OR( OmittedArgs), | |
| // Constants | |
| MpY, 12, //Months Per Year | |
| ADpM, 30.42, //Average Days Per Month | |
| // Determine timeline's interval (length of periods) and orientation (horizontal or vertical) | |
| FirstPeriod, INDEX( Timeline, 1), | |
| SecondPeriod, INDEX( TImeline, 2), | |
| MpP, @ROUND(( SecondPeriod - FirstPeriod) / ADpM, 0), | |
| PpY, MpY / MpP, | |
| ModelPeriods, Columns( Timeline), | |
| // Check inputs | |
| OpeningBalance, IF(ISNUMBER( OpeningBalance), OpeningBalance, 0), | |
| DSCRs, IF(COUNTA( DSCRs) = ModelPeriods, DSCRs, EXPAND( DSCRs, , ModelPeriods, DSCRs)), | |
| APRs, IF(COUNTA( APRs) = ModelPeriods, APRs, EXPAND( APRs, , ModelPeriods, APRs)), | |
| // Recursive function (fn) definition | |
| Recursion, | |
| LAMBDA(fn, OpeningBalance, Debts, CFADs, DSCRs, APRs, Mpy, MpP, Period, | |
| LET( | |
| Rate, INDEX(APRs, 1, Period) / MpY * MpP, | |
| Debt, INDEX(Debts, 1, Period), | |
| CFAD, INDEX(CFADs, 1, Period), | |
| DSCR, INDEX(DSCRs, 1, Period), | |
| Principal, OpeningBalance + Debt, | |
| Interest, Principal * Rate, | |
| Payment, MIN(CFAD/DSCR, Principal + Interest), | |
| ClosingDebt, Principal + Interest - Payment, | |
| NewColumn, VSTACK(Principal, Interest, -Payment, ClosingDebt), | |
| Result, IF( Period = ModelPeriods, | |
| NewColumn, | |
| HSTACK( | |
| NewColumn, | |
| fn(fn, | |
| ClosingDebt, | |
| Debts, | |
| CFADs, | |
| DSCRs, | |
| APRs, | |
| MpY, | |
| MpP, | |
| Period + 1 | |
| ) | |
| ) | |
| ), | |
| Result) | |
| ), | |
| // Procedure | |
| Result, Recursion( Recursion, OpeningBalance, Debts, CFADs, DSCRs, APRs, Mpy, MpP, 1 ), | |
| // Return Result | |
| CHOOSE(Help? + 1, Result, Help) | |
| ) | |
| ); | |
| /* FUNCTION NAME: DebtSculptFixedλ | |
| DESCRIPTION:*//**Creates a Debt Sculpting schedule where APR and DSCR is fixed for all periods*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 08 2023 Craig Hatmaker Original Development | |
| */ | |
| DebtSculptFixedλ = LAMBDA( | |
| // Parameter Declarations | |
| [OpeningBalance], | |
| [Debt], | |
| [CFADS], | |
| [DSCR], | |
| [APR], | |
| [MonthsPerPeriod], | |
| [DoNotUse], | |
| // Procedure | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION: →Creates a Debt Sculpting schedule where APR and DSCR are fixed for all periods¶" & | |
| "SUGGESTED ROW IDS: →Debt opening balance¶" & | |
| " →Interest on Principal Balance¶" & | |
| " →Principal repayments¶" & | |
| " →Debt closing balance¶" & | |
| "VERSION: →Aug 08 2023¶" & | |
| "PARAMETERS:→¶" & | |
| "OpeningBalance →(Optional) Model's debt on opening. Defaults to 0¶" & | |
| "Debt →(Required) New debt taken on for the period¶" & | |
| "CFADS →(Required) Cash Flow Available for Debt Services¶" & | |
| "DSCR →(Required) Debt Service Coverage Ratio¶" & | |
| "APR →(Required) Interest's Annual Percentage Rate¶" & | |
| "MonthsPerPeriod →(Optional) Defaults to one month per period.¶" & | |
| "DoNotUse →This is required internally for counting period columns¶" & | |
| "→¶" & | |
| "EXAMPLES: →=DebtSculptFixedλ(, Debt, CFADS, DSCR, APR)", | |
| "→", "¶" ) | |
| ), | |
| // Error Messages | |
| ErrMsgs, {"Debt must be a dynamic array holding new debt (if any) in each period"; | |
| "CFADS must be a dynamic array holding cash available for debt service in each period"; | |
| "DSCR must be a single number holding debt service coverage ratio for all periods"; | |
| "APR must be a single number holding interest's annual percentage rate for all periods"}, | |
| // Check inputs | |
| OpeningBalance, IF(ISNUMBER(OpeningBalance), OpeningBalance, 0), | |
| Debt, IF(COLUMNS(Debt) <= 1, #Value!, Debt), | |
| CFADS, IF(COLUMNS(CFADS) <= 1, #Value!, CFADS), | |
| DSCR, IF( AND( ISNUMBER(DSCR), COLUMNS(DSCR) = 1), DSCR, #Value!), | |
| APR, IF( AND( ISNUMBER(APR), COLUMNS(APR) = 1), APR, #Value!), | |
| MonthsPerPeriod,IF(ISNUMBER(MonthsPerPeriod), MonthsPerPeriod, 1), | |
| Period, IF(VALUE(DoNotUse) = 0, 1, DoNotUse), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors, VSTACK( | |
| OR( ISERROR( Debt)), | |
| OR( ISERROR( CFADS)), | |
| OR( ISERROR( DSCR)), | |
| OR( ISERROR( APR)) | |
| ), | |
| Messages, FILTER(ErrMsgs, Errors, ""), | |
| // Procedure | |
| ModelPeriods, Columns(Debt), | |
| Rate, APR / 12 * MonthsPerPeriod, | |
| PeriodDebt, INDEX(Debt, 1, Period), | |
| PeriodCFADS, INDEX(CFADS, 1, Period), | |
| Principal, OpeningBalance + PeriodDebt, | |
| Interest, Principal * Rate, | |
| Payment, MIN(PeriodCFADS/DSCR, Principal + Interest), | |
| ClosingDebt, Principal + Interest - Payment, | |
| NewColumn, VSTACK(Principal, Interest, -Payment, ClosingDebt), | |
| Result, IF( Period = ModelPeriods, | |
| NewColumn, | |
| HSTACK( | |
| NewColumn, | |
| DebtSculptFixedλ( | |
| ClosingDebt, | |
| Debt, | |
| CFADS, | |
| DSCR, | |
| APR, | |
| MonthsPerPeriod, | |
| Period + 1 | |
| ) | |
| ) | |
| ), | |
| // Handle Error | |
| Return, IF( AND( Errors), 2, IF( OR( Errors), 3, IF( OR( ISERROR( Result)), 2, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: DebtSculptVariableλ | |
| DESCRIPTION:*//**Creates a Debt Sculpting schedule where APR and DSCR is set for each period*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 08 2023 Craig Hatmaker Original Development | |
| */ | |
| DebtSculptVariableλ = LAMBDA( | |
| // Parameter Declarations | |
| [OpeningBalance], | |
| [Debt], | |
| [CFADS], | |
| [DSCR], | |
| [APR], | |
| [MonthsPerPeriod], | |
| [DoNotUse], | |
| // Procedure | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION: →Creates a Debt Sculpting schedule where APR and DSCR is set for each period¶" & | |
| "SUGGESTED ROW IDS: →Debt opening balance¶" & | |
| " →Interest on Principal Balance¶" & | |
| " →Principal repayments¶" & | |
| " →Debt closing balance¶" & | |
| "VERSION: →Aug 07 2023¶" & | |
| "PARAMETERS:→¶" & | |
| "OpeningBalance →(Optional) Model's debt on opening. Defaults to 0¶" & | |
| "Debt →(Required) New debt taken on for the period¶" & | |
| "CFADS →(Required) Cash Flow Available for Debt Services¶" & | |
| "DSCR →(Required) Debt Service Coverage Ratio¶" & | |
| "APR →(Required) Interest's Annual Percentage Rate¶" & | |
| "MonthsPerPeriod →(Optional) Defaults to one month per period.¶" & | |
| "DoNotUse →This is required internally for counting period columns¶" & | |
| "→¶" & | |
| "EXAMPLE: →=DebtSculptVariableλ(, Debt, CFADS, DSCR, APR)" , | |
| "→", "¶" ) | |
| ), | |
| // Error Messages | |
| ErrMsgs, {"Debt must be a dynamic array holding new debt (if any) in each period"; | |
| "CFADS must be a dynamic array holding cash available for debt service in each period"; | |
| "DSCR must be a dynamic array holding debt service coverage ratio for each period"; | |
| "APR must be a dynamic array holding interest's annual percentage rate for each period"}, | |
| // Check inputs | |
| OpeningBalance, IF(ISNUMBER(OpeningBalance), OpeningBalance, 0), | |
| Debt, IF(COLUMNS(Debt) <= 1, #Value!, Debt), | |
| CFADS, IF(COLUMNS(CFADS) <= 1, #Value!, CFADS), | |
| DSCR, IF(COLUMNS(DSCR) <= 1, #Value!, DSCR), | |
| APR, IF(COLUMNS(APR) <= 1, #Value!, APR), | |
| MonthsPerPeriod,IF(ISNUMBER(MonthsPerPeriod), MonthsPerPeriod, 1), | |
| Period, IF(DoNotUse = 0, 1, DoNotUse), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors, VSTACK( | |
| OR( ISERROR( Debt)), | |
| OR( ISERROR( CFADS)), | |
| OR( ISERROR( DSCR)), | |
| OR( ISERROR( APR)) | |
| ), | |
| Messages, FILTER(ErrMsgs, Errors, ""), | |
| // Procedure | |
| ModelPeriods, Columns(Debt), | |
| Rate, APR / 12 * MonthsPerPeriod, | |
| PeriodRate, INDEX(Rate, 1, Period), | |
| PeriodDebt, INDEX(Debt, 1, Period), | |
| PeriodCFADS, INDEX(CFADS, 1, Period), | |
| PeriodDSCR, INDEX(DSCR, 1, Period), | |
| Principal, OpeningBalance + PeriodDebt, | |
| Interest, Principal * PeriodRate, | |
| Payment, MIN(PeriodCFADS/PeriodDSCR, Principal + Interest), | |
| ClosingDebt, Principal + Interest - Payment, | |
| NewColumn, VSTACK(Principal, Interest, -Payment, ClosingDebt), | |
| Result, IF( Period = ModelPeriods, | |
| NewColumn, | |
| HSTACK( | |
| NewColumn, | |
| DebtSculptVariableλ( | |
| ClosingDebt, | |
| Debt, | |
| CFADS, | |
| DSCR, | |
| APR, | |
| MonthsPerPeriod, | |
| Period + 1 | |
| ) | |
| ) | |
| ), | |
| // Handle Error | |
| Return, IF( AND( Errors), 2, IF( OR( Errors), 3, IF( OR( ISERROR( Result)), 2, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: DebtSculptVariableLRVλ | |
| DESCRIPTION:*//**Creates a Debt Sculpting schedule where APR and DSCR is set for each period*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 08 2023 Craig Hatmaker Original Development | |
| */ | |
| DebtSculptVariableLRVλ = LAMBDA( | |
| // Parameter Declarations | |
| [OpeningBalance], | |
| [Debt], | |
| [CFADS], | |
| [DSCR], | |
| [APR], | |
| [MonthsPerPeriod], | |
| [DoNotUse], | |
| // Procedure | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION: →Creates a Debt Sculpting schedule where APR and DSCR is set for each period¶" & | |
| "SUGGESTED ROW IDS: →Debt opening balance¶" & | |
| " →Interest on Principal Balance¶" & | |
| " →Principal repayments¶" & | |
| " →Debt closing balance¶" & | |
| "VERSION: →Aug 07 2023¶" & | |
| "PARAMETERS:→¶" & | |
| "OpeningBalance →(Optional) Model's debt on opening. Defaults to 0¶" & | |
| "Debt →(Required) New debt taken on for the period¶" & | |
| "CFADS →(Required) Cash Flow Available for Debt Services¶" & | |
| "DSCR →(Required) Debt Service Coverage Ratio¶" & | |
| "APR →(Required) Interest's Annual Percentage Rate¶" & | |
| "MonthsPerPeriod →(Optional) Defaults to one month per period.¶" & | |
| "DoNotUse →This is required internally for counting period columns¶" & | |
| "→¶" & | |
| "EXAMPLE: →=DebtSculptVariableλ(, Debt, CFADS, DSCR, APR", | |
| "→", "¶" ) | |
| ), | |
| // Error Messages | |
| ErrMsgs, {"Debt must be a dynamic array holding new debt (if any) in each period"; | |
| "CFADS must be a dynamic array holding cash available for debt service in each period"; | |
| "DSCR must be a dynamic array holding debt service coverage ratio for each period"; | |
| "APR must be a dynamic array holding interest's annual percentage rate for each period"}, | |
| // Check inputs | |
| OpeningBalance, IF(ISNUMBER(OpeningBalance), OpeningBalance, 0), | |
| Debt, IF(COLUMNS(Debt) <= 1, #Value!, Debt), | |
| CFADS, IF(COLUMNS(CFADS) <= 1, #Value!, CFADS), | |
| DSCR, IF(COLUMNS(DSCR) <= 1, #Value!, DSCR), | |
| APR, IF(COLUMNS(APR) <= 1, #Value!, APR), | |
| MonthsPerPeriod,IF(ISNUMBER(MonthsPerPeriod), MonthsPerPeriod, 1), | |
| Period, IF(DoNotUse = 0, 1, DoNotUse), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors, VSTACK( | |
| OR( ISERROR( Debt)), | |
| OR( ISERROR( CFADS)), | |
| OR( ISERROR( DSCR)), | |
| OR( ISERROR( APR)) | |
| ), | |
| Messages, FILTER(ErrMsgs, Errors, ""), | |
| // Procedure | |
| ModelPeriods, Columns(Debt), | |
| Rate, APR / 12 * MonthsPerPeriod, | |
| PeriodRate, INDEX(Rate, 1, Period), | |
| PeriodDebt, INDEX(Debt, 1, Period), | |
| PeriodCFADS, INDEX(CFADS, 1, Period), | |
| PeriodDSCR, INDEX(DSCR, 1, Period), | |
| Principal, OpeningBalance + PeriodDebt, | |
| Interest, InterestLRVλ(PeriodCFADS, PeriodDSCR, Principal, PeriodRate), | |
| Payment, MIN(PeriodCFADS/PeriodDSCR - Interest, Principal - Interest), | |
| ClosingDebt, Principal + Interest - Payment, | |
| NewColumn, VSTACK(Principal, Interest, -Payment, ClosingDebt), | |
| Result, IF( Period = ModelPeriods, | |
| NewColumn, | |
| HSTACK( | |
| NewColumn, | |
| DebtSculptVariableLRVλ( | |
| ClosingDebt, | |
| Debt, | |
| CFADS, | |
| DSCR, | |
| APR, | |
| MonthsPerPeriod, | |
| Period + 1 | |
| ) | |
| ) | |
| ), | |
| // Handle Error | |
| Return, IF( AND( Errors), 2, IF( OR( Errors), 3, IF( OR( ISERROR( Result)), 2, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: InterestLRVλ | |
| DESCRIPTION:*//**Calculates debt sculpting interest using method presented by Lance Rubin*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 09 2023 Craig Hatmaker Original Development | |
| */ | |
| InterestLRVλ= LAMBDA( | |
| // Parameter Declarations | |
| [CFADS], | |
| [DSCR], | |
| [Principal], | |
| [InterestRate], | |
| [DoNotUse], | |
| Let( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "DESCRIPTION: →Calculates debt sculpting interest using method presented by Lance Rubin¶" & | |
| "VERSION: →Aug 08 2023¶" & | |
| "PARAMETERS:→¶" & | |
| "CFADS →(Required) Cash Flow Available for Debt Services for this period¶" & | |
| "DSCR →(Required) Debt Service Coverage Ratio for this period¶" & | |
| "Principal →(Required) Principal balance for this period¶" & | |
| "InterestRate →(Required) Interest's Percentage Rate for this period (eg. APR/Days in this year * Days in this period)¶" & | |
| "DoNotUse →This is required internally for calculating rates¶" & | |
| "→¶" & | |
| "EXAMPLES:→¶" & | |
| "Result →Formula¶" & | |
| "222.90 →=InterestLRVλ( 6666.37, 3.50, 90000, 3%/12)", | |
| "→", "¶" ) | |
| ), | |
| // Error Messages | |
| ErrMsgs, {"CFADS must be a single number holding cash available for debt service for this period"; | |
| "DSCR must be a single number holding debt service coverage ratio for this periods"; | |
| "Principal must be a single number holding this period's opening debt balance"; | |
| "InterestRate must be a single number holding this period's interest rate (APR/Days in this year * Days in this period)"}, | |
| // Check inputs | |
| CFADS, IF( AND( ISNUMBER(CFADS), COLUMNS(CFADS) = 1), CFADS, #Value!), | |
| DSCR, IF( AND( ISNUMBER(DSCR), COLUMNS(DSCR) = 1), DSCR, #Value!), | |
| Principal, IF( AND( ISNUMBER(Principal), COLUMNS(Principal) = 1), Principal, #Value!), | |
| InterestRate, IF( AND( ISNUMBER(InterestRate), COLUMNS(InterestRate) = 1), InterestRate, #Value!), | |
| DoNotUse, IF(ISOMITTED(DoNotUse), 0, DoNotUse), | |
| // Error messages to display (if any) | |
| Errors, VSTACK( | |
| OR( ISERROR( CFADS)), | |
| OR( ISERROR( DSCR)), | |
| OR( ISERROR( Principal)), | |
| OR( ISERROR( InterestRate)) | |
| ), | |
| Messages, FILTER(ErrMsgs, Errors, ""), | |
| // Procedure | |
| Payment, IF(DoNotUse = 0, CFADS/DSCR, DoNotUse), | |
| Interest, (Principal - Payment/2) * InterestRate, | |
| Repayment, -CFADS/DSCR + Interest, | |
| Balance, Principal + Interest + Repayment, | |
| Result, If( ABS( Payment + Repayment ) < .01, | |
| MAX(Interest, 0), | |
| InterestLRVλ(CFADS, DSCR, Principal, InterestRate, -Repayment) | |
| ), | |
| // Handle Error | |
| Return, IF( AND( Errors), 2, IF( OR( Errors), 3, IF( OR( ISERROR( Result)), 2, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment