Debt Part 3

Quick reference

• 00:04 So back to debt and interest expense.
• 00:07 We're modeling up the interest expense.
• 00:12 And we've done the first of the two calculations.
• 00:15 We've actually done the calculation and the control account for
• 00:19 the debt draw downs and the debt repayments.
• 00:21 And made it balance in the financial statements.
• 00:24 Had nothing to do with the income statement.
• 00:25 We could calculate it now, the return of capital.
• 00:30 Now, we've got to calculate the return on capital.
• 00:33 So let's go back and do the second part.
• 00:37 Last time out, I calculated the control account for the return of capital.
• 00:43 The return of debt, the net debt draw down stroke repayment.
• 00:47 And notice that everything is either balance sheet or cash flow statement.
• 00:50 Even though we've been driven by the P&L.
• 00:52 There was nothing to do with the P&L here.
• 00:54 We got it to balance.
• 00:56 So now we have to calculate the interest, the return on capital, the return on debt.
• 01:01 The comments actually said that movements are assumed to occur at the end of
• 01:05 each period.
• 01:06 That means that all interest will be calculated on the opening balance.
• 01:10 So it's easy.
• 01:11 We're going to use this as our interest rate, row 87.
• 01:14 And lo and behold, I've already put that in here in row 221.
• 01:20 I need to bring in the opening debt.
• 01:22 Look, I've been nice, I've already told you it comes from row 214.
• 01:25 So equals, back up here to row 214, and copy that across.
• 01:30 And it would just simply be interest rate times opening balance if it was annual,
• 01:33 which it is here.
• 01:35 But sometimes you have a proportion of a year.
• 01:37 In this case, I'm going to decide that it's calculated on a simple basis, so
• 01:41 it will just be linear.
• 01:42 We'll just do a proportion of the year.
• 01:44 So let's take the days in the period, that equals this.
• 01:49 J$8, in case we copy it somewhere else.
• 01:52 And to actually calculate the number of days in a year, standard year.
• 01:55 Because we want it be a standard year, so we calculate the daily interest rate.
• 02:00 I've actually got in the model parameters, days in year, 365.
• 02:03 So we'll bring that back into calculations, equals and
• 02:08 we will go days in year.
• 02:11 And we copy that across.
• 02:15 And therefore the proportion is going to be days in period divided by days in
• 02:20 standard year restricted to equal one.
• 02:22 So equal min open brackets of this divided by that and 1.
• 02:28 And even though 1 is hard code,
• 02:29 that's acceptable here because it will never be any other value.
• 02:34 So the interest then is simply going to be, Equal to the actual opening balance.
• 02:43 Multiplied by the interest rate,
• 02:45 multiplied by the proportion of the year, easy.
• 02:50 Now we can do our control account Here's our control account here.
• 02:57 Now, I need my opening balance from the opening balance sheet,
• 03:01 funnily enough for the interest payable.
• 03:04 So let's go take that.
• 03:05 Make sure you pick up column I, not column K.
• 03:07 Then the opening interest payable will be the previous period's closing balance.
• 03:12 Copy it across,
• 03:14 the actual closing interest payable with the sum of the above.
• 03:18 So I take the trick of not taking the first period but
• 03:21 I'm using column K deliberately.
• 03:22 Because then I can copy that and paste specialist formulas
• 03:27 into all of these so that it doesn't upset the borders.
• 03:32 Interest expense is then just this number here, copy it across.
• 03:36 And then I need to calculate interest paid.
• 03:38 And it's supposed to be a one period delay.
• 03:41 Now don't do this.
• 03:42 This is one week will do.
• 03:43 So it's equal minus if open brackets.
• 03:46 The period J$9 equals 1 then we'll take the closing interest
• 03:51 payable from the opening balance sheet made absolute.
• 03:55 Otherwise take the previous period's interest expense.
• 03:58 Now that does work when I copy this across.
• 04:02 But, there's a simpler formula, would you believe it.
• 04:04 Watch this, equals minus the opening balance.
• 04:10 You see the opening balance, is before the interest expense for that period.
• 04:15 And so therefore, the interest expense will be picked up in the following period.
• 04:19 So there is automatically a one period delay,
• 04:21 and that's a nice neat trick to do a one period delay.
• 04:25 So we are done, we've got our return on capital, our return on debt,
• 04:29 the interest now.
• 04:30 We just need to put them into the financial statement and
• 04:32 we've got again four lines.
• 04:34 Let me get rid of the other line clicking on here to get rid of that.
• 04:38 You see balance sheet, income statement, cash flows, statement balance sheet.
• 04:42 We've got here then four lines.
• 04:45 So it's three calculations we need to do,
• 04:47 interest expense is indicative number into the P&L interest paid.
• 04:51 It's fine as it is into the cash flow statement and then,
• 04:54 this goes into the balance sheet.
• 04:56 So let's do them.
• 04:57 The income statement interest expense equals minus that number, copy it across.
• 05:03 And as soon as we do this the balance sheet no longer balances.
• 05:08 Error, that's what we expect.
• 05:10 The cash flow statement Interest paid comes up here, equals.
• 05:16 It's already negative so we don't need to make any adjustment.
• 05:19 This number here, copy that across, still doesn't balance.
• 05:24 But when we go to the balance sheet and put in interest payable equals.
• 05:28 Back to the value here we've got closing interest payable.
• 05:32 Copy that across, and we're done.
• 05:36 It balances, yay.
• 05:37 Another one finished, another trick for the good guys.
• 05:40 We're on to the next part, interest and debt done.
• 05:44 Let's move on.

Lesson notes are only available for subscribers.