How do I go about this making a debt schedule given these sets of assumptions?
Financing Details
A bank will provide a project finance loan of up to 60% of the total project cost. The drawdown will be based on the actual project construction.
The loan tenor is up to 7 years from the drawdown date, inclusive of 16 months grace period on principal.
The principal repayment is payable in 30 equal quarterly amortizations with 25% balloon repayment due at maturity date. Principal repayment to commence after the grace period.
The company has to renew the ballooned balance for the remaining economic life of the project.
Cost of money for the loan and equity are 5% p.a. and 15% p.a. respectively.
Questions:
- How do I incorporate the drawdown in the model's debt schedule
- How do I amortize the principal repayment given the balloon repayment?
- How will the balloon repayment affect the cash flow and balance sheet.
- How would you go about making this schedule?
- Any online resources where I could learn how to make this
Thanks guys!
Hi Biriyo Neru, hope I can help. Do any of these links cover what you're looking for:
If we're lucky, the following users may have something to say: AAA7 cg3008 AmoryBlaine
Fingers crossed that one of those helps you.
Modeling debt schedule for a holdco with two opcos (Originally Posted: 12/29/2017)
Hey guys,
I have a question about modeling out the debt schedule for a Holdco, which comprises two entities with their own three statements. Basically, the Holdco is a shell company and is putting on new debt with the cash to pay debt coming from the Opcos.
My question is: in the projection years, after the Holdco pays down debt (mandatory amort and optional repayment), what happens to the remaining cash balance? How do I distribute it to the Opcos? Should I distribute the remaining cash balance to the Opcos based on EBITDA contribution or some other mechanism? Any ideas would be appreciated. Thanks.
JFirst, shame nobody has responded. Maybe one of these topics will help:
Or maybe the following users have something to say: cwambua Badash16 @Henryxyz"
Hope that helps.
Revenue to net income is consolidated at holdco level. In CFFO you reverse net income contribution of opcos then add in distributions received from opcos (only impact they will have to CF statements). CFFI and CFFF exclude opco level capex/debt service. Then you just model like you would a normal company. Unlikely you would be investing cash in an opco that previously distributed you money. Also doesn't happen often between opcos that have entity-level debt (no cross guarantee) via parent holdco due to bankruptcy laws that might reverse or collapse those transactions.
First, I would suggest reducing cash dividends paid from the opcos to the holdco by a minimum cash balance needed on an opco level to sustain business / keep up with working capital requirements. The minimum cash balance of each opco can be calculated as a percentage of sales.
After debt on the holdco level is paid off your further actions really depend on whether or not the opcos are able to finance operations and capex on their own or need funding from the holdco. If your opcos can finance themself on their own I would think about starting to pay cash dividends to the holdcos shareholders.
Details, however, are deal specific. I hope this helps nevertheless.
Question about debt schedule in operating model (Originally Posted: 12/17/2017)
Hi everyone,
I have been teaching myself financial modeling and for the most part, I understand everything, however the one thing which always gives me problems is building / projecting the debt schedule.
I was following along on WSP how they build the debt schedule in the three statement model, but they don't do a good job in explaining it. As an example, I was building a model for Dollar Tree (DLTR) and in note 6 of the 2016 10-K they provide details about their debt. Now, how I was thinking of doing this was including all the debt into one tranche and then calculating a weighted average interest rate to calculate interest expense in the projection period. The company also provides details on its debt amortizations so I was just going to use that for the pay down. Is this an OK way to approach this? Also, in the event a company does not disclose its debt payments for future years, what do you do? For example, another company I was looking at (HCA Healthcare) does not provide their payments.
Any advice is much appreciated, this is something I always get stuck on. Also wondering, on the job are three statement models build often or is it unnecessary?
Hey JckBunce23, I swear if I had a silver banana for every lonely thread I posted too I'd be richer than @compbanker ...
Maybe one of our professional members will share their wisdom: @dzcelts" Hugh33 privilege
You're welcome.
modelling interest / debt schedule (Originally Posted: 09/16/2014)
hi,
I have an upcoming modelling interview that will focus heavily on modelling a debt schedule with pike, rcfs, cash sweep etc.
does anyone have a recommendation on any online resources that are particularly good for this area, either paid or free?
had a look at WSP, but the debt schedule in its main model seems to be quite basic.
thks
https://www.macabacus.com/
Check out the operating model template
How to convert from a monthly debt schedule to an dynamic annual one? (Originally Posted: 04/23/2016)
Dear All, I would like to convert a monthly debt schedule to an annual one? is there a professional approach for this? Needless to say, the annual debt schedule should be dynamic.
Not sure what the issue is here. If you're looking to keep things driven by your monthly schedule then your annual schedule is really just a summarized reflection of your monthly schedule. Just pull beginning balance (Jan 1) and ending balance (Dec 31) and then sum your monthly principal and interest payments to show those on an annualized basis.
This is hardcoding the result which is pretty much a simple exercise - so for example, you are summing up all monthly interest amounts in 2016.
However, the trick here is to make it dynamic (not hardcoded) so for example interest in 2016 January is based on beg. balance Jan x interest rate Interest February is based on beg balance on February x interest rate So my question is, which i feel is impossible, is how to i make annualized debt schedule that will is dynamic and would calculate the total interest in 2016 Hope you got my point and the issue.
Lol not impossible and you're not hardcoding anything in the way I explained it. You keep your monthly schedule as is and reference it in your annual schedule. If you just want a standalone annual schedule that doesn't reference a monthly schedule, your best bet is to just use an average balance (of beg. and end balance) to calculate interest expense for the year.
EDIT: Fuggit, I'm bored so I'm just going to make it for you, here you go: https://docs.google.com/spreadsheets/d/1WjdUzkemse5fyVFDFrO85zHH4Mt9r6e…
Help w/ Debt Schedule (Originally Posted: 07/16/2013)
I'm trying to make a debt schedule for a model. Very simple schedule, but I have never made one before. I've consulted 2 textbooks and the internet, but can't find a way to retain inputs while allowing the loan to amortize properly. Please PM if you are willing to take a look at it. This is time sensitive. I really appreciate the help.
Yeah sure, send it over.
I'll take a look
Thanks for the replies! PM'd Cruncharoo.
Debt schedule (Originally Posted: 08/12/2014)
I am trying to make a debt schedule for a firm but I am unsure about how to deal with "unamortized deferred loan issuance costs" and if I should be assuming any repayment of long term debt or additional debt (since the company already has lots of assets on order and will pay for them on delivery)
Minus et commodi reiciendis neque incidunt itaque. Nulla consequuntur expedita quam.
Earum voluptatum occaecati voluptatem incidunt optio ea ut eum. Perspiciatis deleniti in perspiciatis vitae. Facere delectus consequuntur tenetur. Quis quae laborum iusto voluptatem. Vitae nihil praesentium repellat amet est.
Vero aut ullam illo qui dolorem. Enim et voluptatem ipsa eos. In in dolor quas quasi neque. Molestiae facere dolorem cumque sit sit unde earum.
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...