Interest Received Part 2

Quick reference

• 00:04 Time to model interest received.
• 00:08 We explained last time out the problem,
• 00:11 interest revenue is the average cash times the interest rate.
• 00:15 Average cash is the proportion of opening and
• 00:17 closing cash, closing cash is the opening cash plus the change in cash held, but
• 00:22 the change in cash held is your net cash receipts plus your interest revenue.
• 00:26 So you've got a circular going, which meant we have no choice but
• 00:31 to actually solve it algebraically, and
• 00:33 it's this formula we're going to use in the Excel example.
• 00:37 Talking of which, right, so it's time to calculate the interest received or
• 00:42 the interest income or the interest revenue or the interest receivable.
• 00:46 The different facets depending on whether you're looking at it from a BNL
• 00:50 balance sheet or cash flow statement perspective, doesn't matter.
• 00:53 Let's have a look at our assumptions.
• 00:55 We've got our interest receivable rate which is 1%,
• 00:58 now it's going to be the same whether it is a positive cash balance or
• 01:02 a negative cash balance, we're not going to differentiate between the two.
• 01:07 So if we're overdrawn, we'll still multiply by this same rate.
• 01:10 Now what you could do is you could have one rate for overdrafts and another for
• 01:14 surplus cash and that will be fine.
• 01:16 What you would do is you do the two calculations where you say,
• 01:19 if open back it's cash balance is less than zero,
• 01:21 do the following formula using one interest rate.
• 01:24 Otherwise use the same formula using a different interest rate, and
• 01:27 that will be it.
• 01:28 We're going to use proportion to the period of the movement of 50%, which means
• 01:31 we're going to use half the opening balance and half the closing balance.
• 01:35 Nice and simple, and we also need the tax rate, so
• 01:38 I've also put a tax rate assumption in here of 30%, and
• 01:41 that's a global assumption because it's in column I.
• 01:44 It's not over here in these cells, so we know it's 30% throughout.
• 01:48 So let's go and have a look at the calculation sheet.
• 01:51 Okay, so we brought all of these numbers in.
• 01:53 We've got to bring in the opening cash balance then.
• 01:58 So the opening cash balance is going to actually link back to the opening balance
• 02:02 sheet if it's the first period or back to the balance sheet in any other period.
• 02:07 So, we're gonna need the usual formula =if, open brackets,
• 02:13 Click on the counter, press the F4 button twice, J$9=1.
• 02:19 If it's the first period, then you to the Opening Balance Sheet, and we're
• 02:24 gonna click on the opening cash balance here in cell I14, make that absolute.
• 02:29 Otherwise, we're going to go to the balance sheet and
• 02:33 click on the corresponding cell here which is going to be I14 close brackets.
• 02:39 It's going to be I14 whichever we do, if it's the first period,
• 02:43 it's going to be the opening balance sheets on I14,
• 02:46 otherwise it's still I14 of the balance sheet.
• 02:48 You see consistency winning out again makes it easy to follow and
• 02:53 easy to spot any mistakes, copy that across in cash balance down.
• 03:03 Next up, what we've got to do is put through the non-interest cash movement.
• 03:07 Now by non-interest cash movement,
• 03:09 we mean all cash movements except the interest revenue.
• 03:12 So interest paid from the debt is fine.
• 03:15 So we go equals, go to the cash flow statement.
• 03:18 We're going to bring in everything from the net operating cash flow including
• 03:22 interest paid, that's okay.
• 03:24 Plus, problem is in the investing we can't include interest received,
• 03:28 we just bringing in purchasing on capex, so it's 26 rather than 27.
• 03:32 And then we're going to bring in the whole of the net financing cash flows, J35.
• 03:38 Lovely, copy that of course, done.
• 03:43 Now we can calculate our interest receivable formula.
• 03:48 Did everyone remember to write that horrible calculation down?
• 03:51 No, me neither.
• 03:52 Luckily here's something I prepared before.
• 03:55 I've got it over here.
• 03:57 So why don't we bring it across, I can see my formula there and
• 04:02 I can copy it down into this cell here, so I'm gonna use that o b times r, etc.
• 04:08 Now notice, on the numerator on the top of this quotient,
• 04:12 we've actually got the actual interest receivable rate in row 241 twice,
• 04:17 on the opening balance and on everything else.
• 04:20 So I'm going to multiply everything by the interest rate and so
• 04:23 I'm gonna change the formula slightly.
• 04:27 So let's start the formula in row 249, equals open brackets the opening balance
• 04:31 here, plus open brackets 1 minus
• 04:36 the proportion made absolute so that becomes $I$243.
• 04:41 Closed brackets times the non-interest cash movement,
• 04:48 which is J246(1- the tax rate which is J427).
• 04:54 And then we'll go closed brackets again,
• 04:59 multiplied by the actual rate J241, that is our numerator.
• 05:05 Then we're going to divide that by (1-
• 05:10 the proportion into the period again, made absolute.
• 05:17 Close brackets multiplied by (1-
• 05:23 the tax rate )J241 the interest
• 05:28 rate again, close brackets.
• 05:33 Lovely, and copy that, of course.
• 05:38 We've calculated the interest receivable based on this horrible formula, and
• 05:42 next time out, we can do the control account.

Lesson notes are only available for subscribers.