In this workout we're asked to establish cash flow based debt capacity for Pizza Plc Banks are willing to provide four debt products to the company An amortizing five year term loan A, a 6 year bullet repayment term loan B A 7 year bullet repayment term loan C and an 8 year maturity PIK note So we have cash flows available for debt service that extend all the way to year 8 And we're going to start now with the PIK note The PIK note interest rate is 10% but after tax That comes to 7% We now need to workout how much we can borrow for the PIK notes If I go to year 8, that's the cash flow that we're going to be using to repay the PIK 574.5 So what's happening with a PIK note (a paid in kind note) is that you take out debt at the beginning And you repay more debt, you do not pay interest payments along the way The interest expense to you is accrued, so you end up with more debt than you borrowed So PIK note principal is exactly the same as my cash flow available for debt service in year 8 I've got 574 to use, that's how much the principal will be However that includes a lot of accrued interest, so I'm now going to back to year 0 What I'm going to do is I'm going to take that amount at the end of year 8 And I'm going to present value it, so I divide it by one plus my interest rate post tax To the power of 8 years So I borrow 334.4 and then after 8 years worth of interest I repay 574.5 as a bullet repayment in year 8 And that's our PIK note I now move onto term C, term C has an interest rate of 8% but after tax that comes to 5.6% I now want to figure out what cash flow I can use for term C's principal and interest in year 7 And that is the 563.6 at the top. I know that has to repay principal and interest So how much is the principal going to be? It will that cash flow less the interest payment or I will discount it by one year's interest payment So that comes 533.7 Let's now calculate the interest on that, 533.7 times by the interest rate post tax comes to 29.9 If I add those two together, it comes to 563.6 Exactly the same as the cash flow Now I know that's going to be a bullet repayment, It's a term loan so the amount of principal outstanding during the debt's life won't change I borrow 533 in year 0 and by year 7 it is still 533.7 But along the way we've had to pay some interest and the amount of interest is going to be 29.9 every time So I linked that interest in year 7 That is now my year 1 to 6 interest as well I now move onto term B, term B has an interest rate of 7% but we know that after tax it's going to be a bit less It comes to 4.9% I now want to ask, what cash flows available to term B? Term B is going to be paid off in year 6 Well initially (if I go up to the top), I thought I would have the year 6 cash flow 552.9 However we've had a cash outflow in year 6, if we go down to the term C we get a cash outflow of 29.9 So actually the cash flow available for term B is a bit less, it's the 552.9 minus the interest So the cash flow available for term loan B is a bit less, it comes 523 Okay cool, well I know that's got to pay off the term B principal and the term B interest So to calculate the principal I'm going to discount it by one year's worth of interest So divide it by one plus the interest rate post tax I'm going to have 498.8 of term B principal in year 6. Let's calculate the interest on that It comes to 24.4, if I add those two together it comes 523 Therefore the cash I've got available for term loan B pays off exactly 523 worth of principal plus interest Term B is a lot like term C, it's a term loan bullet repayment That means the amount of debt does not change over the life of the debt So if I've got 498.6 in year 6 I will also have borrowed exactly the same amount in year 0 So in year 0, we take out 498.6 It will stay the same even to year 6 However along the way we need to make interest payments each year So my interest payment in year 1 is going to be exactly the same as the amount in year 6 I'll link to it, lock it. Copy it right into years 1 to 5 as well So that's term loan B done, we now come down to term loan A That's got an interest rate of 6% but after tax it comes to 4.2% Now term A is an amortizing loan, that means I'm going to take the cash flows in year 1to 5 and I'll use an NPV on it However, which cash flows are we actually linking to? If I scroll to the top and I find the figures for years 1 to 5, I'm tempted to use the 456 for year 1 However what cash outflows have happened in year 1, well we had a cash outflow for term C. We had to pay some interest And for term B we had to pay some interest as well And the same thing happened in years 2, 3, 4 and 5 So my cash flow for term loan A is the original 456 less the term C interest Also less the term B interest of 24.4 So I've actually only got 401.7 of cash flow available for term loan A in year 1 I'm going to copy that right into years 2, 3, 4 and 5 I've now got the cash flows available Remember term A amortizing, therefore I can use the NPV formula The NPV formula asks for a rate, 4.2% And the cash flows for years 1 to 5 Great! We're able to borrow term A principal of 1,962.1 Let's just show the term A balance over time, in year 0 we take out 1,962.1 In year 1, the beginning balance is the same But we then have to add on interest, so the balance is going up However, we're able to make a repayment of 401.7, so that pays the interest and a little bit of capital as well So the amount of capital or principal gradually goes down We borrowed 1,962.1, it goes down to 1,642.8 As I copy it the right, the ending balance goes down And it goes down again and in year 4 down again and the final period gets to zero Let's check the amount of principal that was actually being repaid each year Well we had 401.7 cash payment, however that had to pay off some of the interest So if we take off the amount that was used for interest, the amount that was actually used for principal repayment 319.3 If I copy that to the right I can see all of the principal payments and if we add them up, summed at the bottom of the screen, it's 1,962.1 Exactly the same as the amount that we borrow So how much debt did we take out in total, term A was 1,962.1 Term B, little bit further up 498.6 Term C 533.7 And last but not least, the PIK note right up at the top 334.4 In total giving me 3,328.8 So taking out the four products gives us a lot more debt than if we had just taken out A or A and B or A + B and C