Tax Part 5

Quick reference

• 00:04 So let's look at tax depreciation now.
• 00:08 So without further ado,
• 00:10 let's go straight to assumptions to see what's got to be constructed in this one.
• 00:14 It's tax depreciation.
• 00:16 And we're not going to use straight line depreciation this time.
• 00:19 We're going to use the declining balance basis which gives you those accelerated
• 00:23 capital allowances we were talking about in the theory.
• 00:25 Notice I'm going to use the declining balance multiplier here and
• 00:29 sell I109 of two times.
• 00:30 That's the double declining balance method so
• 00:33 favored by countries like the US for example.
• 00:36 I've got the life of the tax asset which is not the economic life,
• 00:39 it's just the stipulated life of five years and four years respectively.
• 00:43 So what you actually calculate is therefore 1 over 5 is 20%,
• 00:47 multiply by 2 is 40%, and we just do a division by zero check.
• 00:51 Similarly for the tax asset life for of new capex, it's 4 years, 1 over 4 is 25%,
• 00:56 times 2 is 50%.
• 00:57 And we do these calculations here so that we're not having to do these
• 01:02 error checks every time we're doing a calculation.
• 01:05 We just multiply by this percentage, and we don't have to keep doing it.
• 01:09 Let's go to the calculations, then.
• 01:12 So I've brought all of this stuff in here.
• 01:14 We've got the numbers there.
• 01:15 But I've got to bring in the existing assets in the capital expenditure.
• 01:19 Well, I already did this in the accounting, or
• 01:21 the book depreciation section, back in rows 175 and 176.
• 01:26 So if I scroll up, I can actually find my existing assets here.
• 01:31 And I can simply go here, click copy Ctrl+C,
• 01:35 highlight it into these cells here, Ctrl+V, I've got them all.
• 01:39 And I think because it's far enough apart it's worth
• 01:42 one of these row markers to actually say okay
• 01:46 let me tell you where I got this from if you're reading it on a piece of paper.
• 01:49 So let's go Ctrl+V, that's 175.
• 01:55 And the reason we put a formula in is if someone inserts rows,
• 01:58 it will update automatically.
• 02:00 So far, so good, so what?
• 02:03 The next thing I need to do then is actually get these calculations
• 02:07 into my depreciation grid.
• 02:09 Now I can't use the same offset approach that I used for the straight line method,
• 02:12 because that's for the straight line method.
• 02:15 What I've got to do is the grid in this case.
• 02:18 So the depreciation for the existing assets is simply going to equal that
• 02:21 number, that's easy enough to follow.
• 02:23 But for the new capex, I want that number here to go in there,
• 02:28 I want that number here to go in there and so on.
• 02:32 So in other words,
• 02:33 I want to transpose these numbers here and put them down there.
• 02:37 Well, if I look at these labels here and
• 02:39 I go Format Cells, you will see that it's actually text with a number.
• 02:44 In other words, this is really just the numbers 1, 2, 3, 4,
• 02:47 5 made to look like something else.
• 02:49 So I can use the offset function with it.
• 02:51 So, let's transpose in the usual way using offset.
• 02:56 Click on this cell.
• 02:57 Make it absolute.
• 02:58 I don't want to move any rows down but I do want to go this many columns across.
• 03:03 So I'll just make sure it's all anchored on $E292.
• 03:07 Press enter and copy it across and we have now brought the numbers in.
• 03:11 Lovely, all looking good.
• 03:14 Now, I need to keep a total of these, and this particular formula as well.
• 03:21 I can use, if I copy paste special formulas here, Alt+E, S, F, Enter,
• 03:25 see these little tricks we apply.
• 03:27 I can sum what the depreciation is over here.
• 03:30 And if I sum all these numbers to the right here,
• 03:34 I can actually see if I've over depreciated.
• 03:36 So in other words, if this cell here is larger than that one here,
• 03:40 I've over depreciated because I've actually taken more depreciation
• 03:43 than there is actual value.
• 03:46 Okay, so we've actually got two calculations to do in this grid.
• 03:49 The first one is the depreciation for the existing assets.
• 03:51 And then the other one is for the new capex.
• 03:54 For the existing assets, it's going to be the previous period's closing
• 03:59 balance after depreciation, that is the tax written down value,
• 04:02 multiplied by 40% in every period other than period five.
• 04:06 Yes, even periods six, seven, and eight, because after that, there's no amount
• 04:10 left, and it doesn't matter what rate you actually depreciate by.
• 04:14 So I've gotta calculate the tax written down by the first.
• 04:17 So that's equals open brackets this value here.
• 04:20 And I'm going to anchor it on the column minus the sum.
• 04:23 And this is why we have this column in here.
• 04:26 So blank column so that we can sum previous periods.
• 04:30 I'm pressed the full stop so that I'll get I291 to I291, and
• 04:33 I put a dollar sign in here.
• 04:35 And that will give me the tax written down value for the previous period.
• 04:39 And all I've got to do is multiply that by if the period happens to
• 04:44 be the actual remaining life of the assets,
• 04:47 that period 5, then we're going to actually take 100%.
• 04:52 Otherwise we're going to take 40%.
• 04:55 Press Enter, copy it across, no idea what's going on, because it's all zeros.
• 05:01 So to show you how that's working I'm gonna go back in here and
• 05:03 type in 100 and you can see it's working.
• 05:07 Because 100 minus nothing here is 100, times 40% is 40.
• 05:11 That works.
• 05:12 In the second period, it's 100 minus 40 is 60.
• 05:15 60 times 40% is 24, and so on.
• 05:19 It's just in the final period, period five here, it says this is period five, so
• 05:24 just knock off the rest.
• 05:25 And you can see that it's the rest because if you sum them up,
• 05:28 it comes to 100 which is that total there.
• 05:30 Easy, now the problem for the new capex here in periods one to five
• 05:35 is the fact that we are actually starting in different periods.
• 05:40 Period one capex starts here in period one, period two capex starts here in
• 05:44 period two, period three capex starts here in period three.
• 05:48 Therefore the final period where we actually use the 100% of what's
• 05:53 left rather than the 50% is going to be, well,
• 05:56 if it's 4 year life it's going to be period 4.
• 05:59 This one's gonna be period 5, that one's gonna be period 6,
• 06:02 how can we work that out?
• 06:04 Well notice 4 plus 1 minus 1 is 4.
• 06:07 4 plus 2 minus 1 is 5 and that's the right period.
• 06:11 4 plus 4 minus 1 is period seven.
• 06:14 And periods four, five, six and seven that's four periods, that's how we do it.
• 06:18 So we've got to come up with a slightly different way of checking when the period
• 06:23 is we take 100%.
• 06:24 Also we need to make sure that this counter here is greater than or
• 06:27 equal to the period number there.
• 06:29 Because we don't want to start depreciating it before we've
• 06:32 actually acquired the asset, confused?
• 06:35 Well, you will be.
• 06:36 Let's put the formula in then.
• 06:37 So if, open brackets, this counter here is greater than or equal to the period
• 06:44 number of the capex, make sure you get the dollar signs in the right place.
• 06:47 Very important that you get them in the right place.
• 06:49 Then we need to calculate the tax written down value in the usual way.
• 06:52 So that's this number here.
• 06:53 So that's $H292 minus the sum.
• 06:58 Again, click on this cell and
• 06:59 press the full stop to get I292 to I292 and put a dollar sign here.
• 07:04 And then another closed bracket, and
• 07:06 that's giving us the tax written down value for the period before.
• 07:09 And then we multiply that by if open brackets, this counter here, J$9 equals.
• 07:17 Now remember what we have to do?
• 07:19 It's this economic life, also tax life here
• 07:24 plus the actual period capex number minus one.
• 07:29 If it's that period, we take one.
• 07:31 Otherwise, we take 50% in this case.
• 07:35 Close brackets, and we only calculate this when J9 is greater than or equal to E292.
• 07:40 So otherwise, it's 0, close brackets.
• 07:43 Lovely formula, don't you think?
• 07:45 You see, copy that across.
• 07:47 It's actually add those up, that comes to 150.
• 07:51 Take it down one row, you'll see it doesn't start till period two,
• 07:54 but if we add those up it comes to 180 which is that number.
• 07:57 See how it works?
• 07:59 So I copy this, Ctrl+C, I Paste Special those formulas,O-E-S-F Enter.
• 08:05 I have now calculated my tax depreciation.
• 08:08 Next time out we'll look at accounting depreciation and compare the two.

Lesson notes are only available for subscribers.