Chapter 14: Inflation

In this Excel tutorial, I'm going to be talking to you about how taxes and inflation can influence your rate of return. In our information block you can see that we have a $15,000 investment, it has a $4,000 annual benefit, it has no salvage, the income tax bracket is 46%, inflation is 4.2% per year, and the project is to be val..evaluated over eight years. So the first thing we're talking about is just what is your rate of return just on the straight numbers? No inflation, no taxes, nothing. This has been covered in an earlier video where it discusses how to do internal rate of return or rate.

So if you're not familiar with this, please go back and see that video. But to calculate the rate you simply type equals rate, Then it asks me for the number of years, which is B7. Then it asks me for my payment which is B4. And then it asks me for my present value which is minus B3. And that's all the information I have. And turns out the rate before taxes or without any inflation is 20.78%. Now, let's see how adding in just depreciation and taxes can really change this. So here we go. In year 0 our before tax cash flow is just the $15,000 that it cost us and that's still our after tax cash flow. In years 1 to a..1 to 8 our before tax cash flow is $4,000. Our depreciation that we can take is straight line. This information is covered in a video about the different types of depreciation If you haven't seen it.

But straight line is equals SLN then it asks you for the cost, the salvage and the life. You can see that our depreciation, allowable depreciation is $1,875 per year. Taxable income simply equals for tax cash flow minus our depreciation. Then we can do the income taxes on the taxable income, which is simply equals our taxable income times our income tax which is in B6 and then our after tax cash flow is simply equals our before tax cash flow minus our income taxes. And you can see that it's $3,023. Now with this information let's do our after tax rate of return. It's got depreciation in there but not inflation. For this one this will equal rate, remember our year is still 8.

Then it will ask for our payment which is in H15 and then it'll ask for my present value which is in H14. And that's all the information I have. And you can see that our after tax cash flow is down dramatically to 12, just a little over 12% by adding in depreciation and adding in income taxes. Now, let's jump that up even another level. Let's look at what the after tax rate of return is with all that information plus inflation added in. Well, the year 0 should look familiar. The only change to the formula that we have is in year 1, I've Incorporated the inflation, so it is B4 which is our $4,000 times our inflation rate of 4.20% percent. I've locked the B2 cell to allow for me to just do a pull down of this column and it let the increment, the..the B column, but locked that inflation amount.

Everything going across to here is the same as the just the the after tax rate of return, no inflation. The conversion factor is converting our dollars back to year 0 dollars and that'll give you years of your dollars here in this final column. It is this final column that we’ll use to calculate our rate of return. So now that I have all these things set up, these should look familiar. Let me just show you real quick what the conversion factor is. I'm basically doing my 1 plus my interest rate raised to the minus year value.

That's what the formula does. But the way I've got these formulas set up allows me to do this, grab the corner and I can fill in all 8 years very quickly. Now to figure out the rate of return with all this good stuff in here, this is simply IRR, and highlight my after-tax cash flow here and hit return. And notice you get 10.82%. What's important about this is to show you that as you have more information about a project such as depreciation, income taxes and inflation, you can get a better or more solid idea about what your rate of return is.