Debt Part 2

Quick reference

• 00:04 Let's get more specific then.
• 00:06 We've looked at capital in particular with some folks on debt and
• 00:09 interest, but let's really focus on debt and interest now.
• 00:15 Trying to model interest expense, we can't do that until we've modeled debt
• 00:18 because interest is the actual effect of the debt that's been borrowed.
• 00:24 So, typical calculation is going to have two sets of
• 00:27 calculated in there with two control accounts.
• 00:30 The first one's going to be the return of capital, the debt draw downs and
• 00:34 the debt repayments.
• 00:35 Where as the second one is going to be the return on capital, and
• 00:39 that will be the interest.
• 00:40 But we can't do that until the first one's been done.
• 00:44 Let's go play.
• 00:46 Let's go model some debt and interest then.
• 00:49 First in the Navigator sheet, so to Assumptions, and we've got the debt and
• 00:54 related sections on here, debt and interest.
• 00:58 Movements are assumed to occur at the end of each period.
• 01:01 That means that the actual drawdowns repayments happen right at the very end.
• 01:07 Why is that important?
• 01:08 Well, that will mean that the average debt bonds for
• 01:11 the period is going to be the opening balance and that's important because then
• 01:15 we can actually calculate the interest on the opening balance for the period.
• 01:20 You see, if there's any element of the closing balance in there,
• 01:23 we're going to have to take a proportion of the opening balance plus
• 01:26 a proportion of the closing balance which can cause a circular argument.
• 01:29 So we're keeping it simple.
• 01:32 Interest is assumed to be paid in the following period, so
• 01:35 it's going to be a one period delay.
• 01:37 To be quite dramatic that, given this is a yearly model, but you know what we mean.
• 01:41 And then we've got some Assumptions about debt drawdowns and
• 01:44 repayments, the interest rate.
• 01:46 And also, we haven't talked about this yet,
• 01:49 but the interest receivable rate for cash that's not yet been paid.
• 01:54 Let's have the Calculations sheet, here's the debt draw downs and repayments
• 01:58 already in here we need to bring in first of all then just a control account.
• 02:03 Because we've got everything we need we're not trying to muddle anything,
• 02:05 we're keeping it simple.
• 02:07 So the opening balance for
• 02:08 the control account is simply going to be to the open advance sheet.
• 02:12 And pick up the debt to be in the non-current liability section, here,
• 02:17 made absolute.
• 02:21 The opening debt is simply going to be equal to the previous periods of
• 02:23 closing bonds.
• 02:27 We just sum it or equals.
• 02:32 And I can copy that into these cells here.
• 02:36 Paste special format so we don't end up with the borders messing us about.
• 02:40 The debt drawdowns is simple going to be this line and
• 02:44 the repayment is simply gonna be minus the line below that.+ Copy it plus done.
• 02:49 What do we need to do?
• 02:51 We need to style all these Its numbers, sorted.
• 02:59 Not the world's hardest thing to do.
• 03:02 Right, so lets go do our first control account then.
• 03:06 We've got four lines in here,
• 03:08 which means we have three calculations to make it bounce.
• 03:11 Great.
• 03:13 Debt draw downs the key driver.
• 03:15 That goes the cash flow statement.
• 03:17 Debt repayments is also to the cash flow statement.
• 03:21 And then we have the actual closing balance being on the balance sheet.
• 03:25 Now watch out here.
• 03:26 According to this, we've overpaid,
• 03:29 because by the end we'll have paid more back than what we've actually borrowed.
• 03:32 But remember the opening balance is 0.
• 03:35 Now, on the actual Opening Balance Sheet, the opening balance of debt is 150 so
• 03:39 we will actually have reduced to 140.
• 03:42 So just be careful with the method we're employing here but
• 03:45 it's not that we're overpaying but we do need to do a sense check to make sure if
• 03:49 we are in any danger of overpaying when we zero out the opening balance.
• 03:53 So we've got three calculations here, nothing to do with interest.
• 03:58 So using the PNL to generate what we want to do.
• 04:02 But it's all being done in the cash flow and the balance sheet.
• 04:05 Let's go put it in.
• 04:07 So, these are the right sign already for the cash flow statement.
• 04:10 So, you can put them in.
• 04:13 So, in here that draw down is going to be equal to back to the calculation sheet
• 04:20 That we can copy it down one and across, that'll be two of the three calculations.
• 04:24 As soon as we do this, the balance sheet's not balancing.
• 04:27 But we go to the balance sheet now for the debt.
• 04:30 Here it is here, I'll put the closing balance in.
• 04:35 We go to the Calculations sheet here it is.
• 04:42 Copy that cross.
• 04:47 And it balances.
• 04:49 We've done the first part.
• 04:50 We've calculated the return on capital.
• 04:54 Next time out we'll look at the return of capital.

Lesson notes are only available for subscribers.