Debt Sculpting VBA Macro
This VBA macro sculpts the debt principal payments to achieve the Sculpted Debt Service Coverage Ratio from the Financing section on the Inputs worksheet. The macro works by iteratively copying and pasting 1) the calculated Total Project Costs (Calculations worksheet) to the macro-pasted field on the Inputs worksheet, 2) calculated Principal Payment (Calculations worksheet) to the macro-pasted Principal Payment row (Sculpted Debt Service on the Calculations worksheet), and 3) the Calculated Fees, IDC, and Initial Working Capital on the Calculations worksheet to the Macro-Pasted Fees and IDC on the Calculations worksheet. This iterative process is necessary to avoid the circular logic created by calculating fees, reserves, and interest due on a loan that is sized to include them. After several iterations, the difference between the calculated values and the pasted values reduces to zero.
To run the macro, you must ensure macros are enabled in Excel, then press the “Sculpt Debt” button located under the Sculpting Macro inputs in the Financing section of the Inputs worksheet, or located at the top of the Calculations tab. The macro takes approximately 10 seconds to complete.
Importantly, this macro will not make adjustments to the macro-generated Project Leverage input or the target Sculpted Debt Service Coverage Ratio under the Sculpting Macro (All Macro-Pasted) in the Financing section of the Inputs worksheet. Only the Maximize Debt macro (described below) will adjust these inputs.
Maximize Debt VBA Macro
This VBA macro maximizes the project leverage and loan tenor, based on the Maximum Leverage input and Debt-Sculpting Minimum DSCR inputs in the Financing section of the Inputs worksheet. It maximizes project leverage to the lesser of the Maximum Leverage input or the Leverage at Debt-Sculpting DSCR input, which is calculated as the Maximum Sculpted Leverage value. The macro copies the Maximum Sculpted Leverage amount, and then enters the the Loan Life Coverage Ratio (LLCR) if it exceeds the Debt-Sculpting Minimum DSCR. This extends principal payments over time to make use of the entire tenor of the loan. The LLCR serves as a proxy for the target DSCR which would achieve a target debt level, should all CFADS throughout the tenor of the loan be sculpted at that DSCR. After maximum Project Leverage and Sculpted DSCR are determined, the macro then runs the Debt Sculpting VBA Macro (described above) and then iterates the process again multiple times.
To run the macro, ensure you have enabled macros in Excel, and press the Maximize Debt button located under the Sculpting Macro inputs (Inputs, row 118). This macro takes approximately 20-30 seconds to complete.