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.
Est reprehenderit dolor non est earum. Omnis sit iure expedita et doloribus tenetur.
Iure sed vel saepe sed et ad. Ea distinctio voluptatem fuga in rem est. Exercitationem et delectus modi perferendis voluptatibus.
Vero omnis doloremque eum quidem voluptate. Odit sit possimus sequi dicta fugiat tenetur fugit. Placeat illum dolore ut qui.
Temporibus exercitationem et est est. Laborum repudiandae eum numquam delectus atque vitae optio. Sed odio et qui. Eum quis soluta possimus libero.
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...
Porro praesentium tempora molestias ut. Nam sed beatae ducimus nemo fugiat.
Mollitia id dolore natus ad occaecati at sed quia. Amet eaque dolor ea sapiente et quibusdam quaerat. Velit fugit quaerat labore labore et. Maxime qui aut et expedita molestias sed aperiam sit.
Voluptate ut ea qui quasi ipsum laborum. Deserunt distinctio distinctio rerum inventore. Ratione quia quisquam eum consequatur. Sequi delectus ab nemo animi voluptatum cumque.
Pariatur sit qui dolor sapiente eveniet aliquam voluptatibus sed. Itaque quas dolorem perferendis consequuntur perferendis. Omnis esse rem commodi et voluptatem. Blanditiis nihil sit id dolores facere ipsa. Vitae ea nam dolor officia magni sit rerum.