niomcosmo.blogg.se

We can use the same approach of deducting interest from this debt service profile to calculate the principal repayment amount that will give us a constant DSCR. We have now calculated the periodic debt service to maintain our target ratio. When we calculated the amortising debt profile, we first calculated the period debt service and then deducted the interest to arrive at the principal repayment amount. Step 5: Calculate periodic principal repayment amount to maintain the target ratio. I'm therefore introducing a simple switching mechanism. I want my model to easily switch between DSCR based debt sizing and a straightforward leverage percentage input as we have now. Step 4: Switch between DSCR and leverage debt sizing If the maximum leverage were 70%, we would not be able to borrow more than that even if the DSCR debt sizing indicated that we could. Typically lenders will provide minimum DSCR and maximum leverage constraints. We can see that using the target ratio for debt sizing gives us a higher debt amount than our initial assumption around leverage. SUMPRODUCT is an efficient function to use for this. Now that I have both the ingredients I need, I can calculate the debt capacity by multiplying the debt service in each period by the discount factor for that period.

Remember when you are moving calculations around, use Ctrl+x to cut rather than Ctrl+c to copy. As your model build progresses, don't be afraid to move calculations around to improve the structure and flow of the model. Since we need this discount factor for both calculations, I will move it further up the debt sheet. We have already created a Senior debt discount factor when calculating the amortising debt profile. This will be the maximum debt capacity of the business.įor this, we need to discount the debt service using the period debt interest rate as our discount rate.

Next, we want to figure out the amount of debt drawn at the transaction date that will generate the debt service we have calculated. Step 2: Bring up the senior debt discount factor We divide the CFADS by the target ratio to calculate the debt service. We can then use this to calculate debt service at the target ratio. Our first step, therefore, is to calculate CFADS during the debt term: Given that DSCR is an "affordability ratio" - looking only at the cash flows in each period, we can only consider the CFADS during the debt term. Our first step is to calculate the maximum debt service in each period, given the target ratio of 1.3x. Let's say that lenders require a ratio of 1.3x. Since we know CFADS (it's on our cash flow statement), we can rearrange the formula to determine the level of Debt service required to meet a certain coverage ratio.