Chapter 12 Video: Taxes

In this Excel tutorial I'm going to be talking to you about after tax cash flows. After tax cash flows are very important because income taxes are a major disbursement that can't be ignored. Up to this point we've been doing everything with for tax. Now we're going to look at how depreciation and taxes can play into your calculations. In this we have information, we had a $10,000 first cost, $3,000 annual benefits, our recovery period is five years, our salvage value on our item is $1250, and our tax rate is 34%. In order to do an after-tax cash flow, the thing about it is is some things are taxed and some things aren't. So you need to realize what those are. In this case, we have year 0 through 5, the untaxed before tax cash flow is the $10,000 first cost, which you can see in C9. And then we have the salvage value at year five of $1,250 that you can see in C14.

These are untaxed before tax cash flows. Then we have taxed before tax cash flow and those are $3,000 annual benefits that you can see here. The first thing we're going to take into account is Makers on this particular item. It's a five-year recovery period and so I'm going to click here so you can see the formula. We you..used Excel to do the makers formula here. If you are unfamiliar with how to do this calculation, there is a tutorial that will explain maker’s depreciation and how to calculate it in Excel. So you might want to review that if you don't remember this. Notice that in year 5 we had to go with half the value because we sold it during in that year. So our accumulated depreciation for that time period was just the sum of E10 to E14 which gave us a little over $8,800. Now we have some recaptured depreciation here.

Notice that we took the salvage value and subtracted off the difference between our first cost and our cumulative depreciation to get our recaptured depreciation. Now, let's take a look at taxes. Our $10,000 does not get taxed, so it remains at negative $10,000 as an after-tax cash flow. In year 1 our income..our taxable income equaled D10, which was our $3,000 minus our makers plus any recaptured depreciation. We didn't have any so it's a $1,000. Our next Formula is simply what is our taxes on that. That is simply the $1,000 in G10 times our tax rate which is in B6. Notice I've locked that cell because I'm going to be using that the whole time so I can do the pull down easy calculation move.

That means our after tax cash flow is D10 minus our taxes in H10 which gives you a value of $2660. Now the way I have this set up I can simply highlight across the three, drag down and I can get my calculations. The only time this calculation will change is since I had an..a negative tax year there, this actually becomes a plus to make my formula work. And as you can see the only difference between these initial ones and the last one is that we have taken to account this recaptured depreciation. Now what you can do once you've done the after-tax cash flow is you can f..analyze your information anyway you want. You can use present worth.

I'm going to use IRR so this equals IRR, simply high my..my values. And you can see my IRR after tax is 9%. If you did your IRR doing before tax cash flows you probably got a higher value. This one is more realistic taking into account depreciation and income taxes. Now, you can evaluate this project against whatever criteria you use to judge with your 9% IRR. In this Excel tutorial, I've talked to you about how to use taxes in calculating after tax cash flows and how to determine whether that’s a good project or not.