11. Adding Taxes to Our Model

Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial


Our second model iteration will calculate the tax shield provided by plant depreciation and subtract the final taxes paid from operating cashflows.

Lesson Notes

Adding taxes to our model

- Calculating taxes requires a little accounting knowledge
- Before calculate taxes, we must first calculate the depreciation of the plant
- Depreciation is a non-cash expense, reducing our taxable income but not our cash
- After calculating depreciation, we subtract from profits to find our taxable income
- We multiple taxable income by the tax rate to find taxes paid

Keyboard shortcuts

SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
F2: Jump back inside a formula
CTRL + Pg Up: Move to sheet on the left


It's now time to add some complexity to our model by including taxes in the operating cash flows. Taxes are paid on taxable income which are revenues minus expenses. In this case, expenses are our production costs, but also depreciation. When we subtract expenses from revenues, we're left with taxable income, which when multiplied by the tax rate equals taxes paid. Depreciation is an accounting method of allocating the cost of an asset over its useful life. 6 00:00:38.16 --> 00:00:45.09 In the same way, your car depreciates every year and reduces in value, so too will our commercial plant.

Depreciation is categorized as a non-cash expense whereby it reduces the taxable income but not our yearly cash flows. Therefore, it reduces our taxes paid and saves us money. Our cash flows in the model will only be altered by taxes paid. But we need depreciation to calculate our taxes paid total. In our model, we first calculate depreciation for each year. Then we calculate taxable income and taxes paid before readjusting our operating cash flows so as to include this number. Depreciation will begin in the first year of operation and run for a number of years specified in the model. For some renewable projects, the government will allow companies such as Supra to depreciate the value of an asset over a very short time period, in this case, five years. After five years of operation, the company will no longer incur a depreciation expense. To calculate depreciation in our model we'll need to satisfy two criteria. The current year must be after the plant is in operation but also within the first five years of operation. To write this formula, I'll combine an IF function with an AND function. So I'll write, equals IF and then AND to have multiple logical tests. And the first logical test will be, if the current year is greater than or equal to the operating start year. And the second criteria will be if the current year is less than the operation start year plus the depreciation period, which is five years. If both these conditions hold, then I'll return the commercial plant cost divided by the depreciation period. And if they don't hold, I'll simply return zero. I'll then paste this formula for the remaining cells. And as you can see, returns the depreciation value for the first five years of operation, exactly what we want. Our formula where we divide our commercial plant cost by the depreciation period ensures that the plant is depreciated by the same amount every year. This is called Straight Line Depreciation which is probably the most common form of depreciation, although other methods can also be used. Once we've calculated depreciation, we can now calculate taxable income which is simply equal to the revenue plus the production costs plus the depreciation. Again, I'll copy across for the remaining cells. Once we have the taxable income, we can calculate taxes paid, which will equal to minus, because it will be a negative, the taxable income multiplied by the tax rate. Again I'll copy and paste for all of the years. And with our taxes paid, we can now adjust our annual cash flows. Examining the taxes paid line, you can see that in the years that we have depreciation, our taxes paid are $53.9 million. However, when depreciation runs out in 2022, our taxes paid increases to $70 million because the depreciation is no longer in place to reduce taxable income and act as a tax shield. To add taxes paid to our annual cash flows calculation, I'll jump back inside the formula and add the taxes paid cell which will be B13. So I'll write plus B13 and again copy for the remaining cells. When we now return to our control panel, you'll see that a return on investment has reduced from 10 down to 6.6. And this is unsurprising because adding taxes to the model will obviously reduce our cash flows.