In this workout we're asked to establish cash flow based debt capacity for Pizza Plc. Banks are willing to provide two debt products to the company An amortizing five year term loan A at 6% pre tax cash interest And a 6 year bullet repayment term loan B at pre-tax cash interest rate of 7% So just to watch out there, our first product is amortizing regarding the first 5 years And our second repayment is a bullet repayment looking just at year 6 So we've got our cash flows available for debt service, we're going to start with term B So I want to focus just on their cash flow which is the year 6 cash flow So we're given an interest rate of 7%, but I also know that there's a tax rate of 30% Interest rate post tax, 7% times by one minus the 30% is 4.9% (that's the amount actually suffered by the company) Now we need to work out how much principal can be borrowed. We're going to start in year 6. I know in year 6 that the cash flow I've got 552.9 Has to pay off the principal and the interest Therefore I know the difference between the principal here and the cash flow available must be the interest in the middle So I'm going to discount that cash flow by one year's interest So I take the cash flow and I divide it by one plus the interest rate post tax Giving me 527.1 Okay, let's now workout the interest on that 527.1 And it comes to 25.8, so let's check, the cash flow I had available is 552.9 and the amount I spend in year 6 is 552.9, fantastic! However, that's the value of the term B principal in year 6, what's the value of the term B principal in year 0? Well this is a bullet repayment. The value of the principal doesn't change over the life of the debt That means the term B principal in year 0 has to the same as the principal in year 6, 527.1 Now there will be some cash flows occurring, those cash flows are going to be interest The interest will the same every period It's going to be 25.8 So I lock onto that Copy to the right So I start with 527.1, it stays the same every period And each year I just pay off the interest, 25.8 25.8 25.8 etc Now that's quite different to how we're going to calculate term loan A, we're going to use an NPV formula there The big difference is that term loan A is an amortizing debt. The amount of the debt principal changes over time However, term loan B is a bullet The amount of principal does not change over time, the value of the principal in year 0 is also the same as the amount of principal in year 6 This can be very tricky to understand, particularly, why did we discount the cash flow available by just one year? The reason is, this term B principal at the start of year 6 and the amount of cash flow I'll have at the end of year 6 The only difference between them is one year's interest So I discounted by one year's interest to get 527.1 We then have to pay the interest and the amount of cash flows used to pay the interest and the principal Adds up to the cash flow available Okay So so far I've borrowed 527.1, fantastic got myself some debt But the bank has also offered my another debt product, so I now go down to cash flows available for term loan A And initially I'm looking at the cash flows available at the top and I'm thinking "ahh" "Those first five cash flows, they look like they're available to me" But not quite, along the way we've had to pay some interest in years one, two, three, four and five That interest was regarding term B So my cash flows actually available for term loan A are going to be year 1 cash flow minus term B interest Giving me 430.2, I copy that to the right And what I've now got highlighted in green, are the cash flows actually available for term loan A And they're little bit lower than the cash flows we had in the first three years Okay, so my term A interest rate is 6% However after tax, that comes to 4.2% Because this is an amortizing loan, I can use the NPV function Which firstly asks for the rate and then asks for the values to calculate the amount of debt And the amount of debt we can borrow for term loan A is 2,088.3 Let's just check what's happening to term A over time I've got 2,088.3 at the end of year 0 At the beginning of year 1, yep same figure And that goes up by interest, oh no we owe some interest I need to lock onto that interest rate, times it by beginning balance Yikes! So I now owe even more, however I use the cash flow from year one Ahh it's now come down, my ending balance if I add the items above together comes to 1,745.8 So by the end of year 1 it's gone down a bit Let's now go to year 2, has it gone down again? Yes it has, year 3? Yes it's gone down again to 950.6, year 4? It's gone down to 496 And in year5? Down again, I started with 496 but I add on the interest of 20.8 And I then use the cash flow to pay it off and it comes to exactly zero, great! So let's prove why that 2,088.3 is correct Well the principal amount that we repay every period is the cash flow I have available less the amount of it that was spent on interest Some of it was spend on interest So how much was spent on principal repayment? 342.5 If I copy that to the right, I could sum up those five principal repayments and they come to (if you can see the bottom of my screen) 2,088.3 That is exactly the amount that we borrowed, so how much was raised? Well term A I had 2,088.3 and for term B I had 527.1 Great! Using two debt products covering years 1 to 6, gives us more debt than if we had just gone from years 1 to 5