Hi all,
When I try to solve for the debt size, using sculpting, I do: CFADS then divide it by DSCR and then, do I take the NPV (at rate of the interest rate) of these values to arrive at the total debt? I am asking because I did the Wallstreet Prep course and they don't take the NPV, they just add the resulting cashflows and that is the total debt, is this version correct?..
2nd question: I read that to get to debt size you have to use the CFADS after.. ..paying interest. But, isn't the whole point of debt sculpting to arrive at the total debt load? So what interest are they even referring to? the CFADS will be EBITDA, so no debt had been paid out yet. What is the context of this technique (to use CFADS after paying interest)?
Thanks all!
In the real world, interest rates are floating to some degree so it isn't as simple as =XNPV
We don't do NPV. You simply use CFADS / Target DSCR and use macro to get your principal and interest expenses. Sum of the principal amount over your sizing period will be your total debt size.
An alternative to using DSCR debt sculpting with an NPV formula is to use what's known as an LLCR or Loan Life Coverage Ratio which is applied to the discounted future CFADS at the cost of your debt (i.e. interest rate). If you set your discount rates to actual interest rates, in theory your debt sizing using DSCR and LLCR should give you the same debt sizing result using identical ratios.
A more comprehensive explanation can be found here:
https://financialmodelling.mazars.com/resources/loan-life-coverage-rati…
As for removing interest from CFADS for debt sizing, it's because you start with a certain DSCR ratio that you apply to your CFADS to get to your Debt Service (Debt Service = Interest + Principal Repayment). So once you calculate your Debt Service amount (CFADS / target DSCR ratio) you then subtract your Interest paid to get to your Principal Repayment amount that remains. The sum of Principal Repayment amounts over your debt sizing period will be your calculated debt size.
Brilliantly explained. Thanks a lot! :)
Pardon my ignorance, but how do I get the interest expense then if I still don't know the debt size? Or I get the sum of the resulting CFs after DSCR adjustment and use that number to calculate interest at the interest rate and subtract the it from the debt size I got?
There’s circular logic involved. You don’t calculate interest based off the overall starting debt balance. You calculate interest based off the “Beginning Balance” for each period. You have to put the formulas in first, then drag them across, then refresh. E.g. heres what the formulas look like in the first period.
These 5 rows are built top down:
CFADS
(/) DSCR
—————-
= Debt Service
(-) Interest Payment (Rate * Beg Balance below)
—————-
= Cash Available for Principal Repayment
These 3 rows are built from bottom UP:
Beg Balance = End Balance (below) + Principal Repayment
(+) Principal Repayment (= calculated last line above)
End Balance = NEXT period’s beginning balance
Drag all lines right, make sure circular reasoning is on in Excel settings, and refresh and you will have the starting debt quantum in the “Beg Balance” cell of the first year. You should understand how the logic is starting backwards and building UP to your beginning balance.
Good question. There are many ways to size debt, but most of them either require a macro or guess and check to ensure your debt is repaid in the amort period. In order to avoid circular references, you will use the following formula (called the backward induction method) in each interest period for interest payments:
Starting Balance = Ending Balance + Principal Payment
Principal Payment = CFADS - Interest Payment
Interest Payment = (CFADS - (Ending Debt Balance * Interest Rate))/(1+Interest Rate)
Ending Balance = Starting Balance in Next Period
Your debt sizing is the sum of all principal payments.
Officia itaque assumenda cupiditate impedit quaerat rem voluptatibus. Aut libero ut sed. Commodi deleniti est quo exercitationem.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Quasi sint quaerat voluptas neque maiores. Ipsum eum veritatis et. Blanditiis possimus voluptas vel voluptatem aspernatur ipsam cupiditate. Magnam dolor sunt perferendis voluptas.
Qui fuga minima quam blanditiis quia. Quia qui earum fuga ut id expedita quis ipsa. Accusamus beatae ab et mollitia nihil. Dolorem aliquid nemo voluptate qui.
Quod nobis consequuntur facilis ut aut. Omnis sed doloremque quas officia consequatur dolor ducimus ad. Fugit non placeat fugit atque sit totam in voluptatem. Esse accusantium est laborum et sint nostrum et. Perferendis esse similique quas explicabo saepe saepe explicabo.