We use cookies to enhance your experience on our website. By continuing to use our website, you are agreeing to our use of cookies. You can change your cookie settings at any time. Find out more  ## After-tax rate of return and spreadsheets

### Income Taxes

#### After-tax rate of return and spreadsheets

Go to questions covering topic below

We calculated the AT IRR (or AT-ROR) earlier for the special case of straight-line depreciation. Here we will look at another special case as well as using a spreadsheet to hand more typical, and complicated, AT IRRs.

The special case is when using a non-depreciable asset. In that case,

Estimated AT IRR = (1 - tax rate) (BT IRR)

Using the earlier example but with no depreciation  EOY BTCF Depreciation TI Income tax ATCF 0 -\$3000 (\$3,000) 1 \$800 800 (\$272) \$528 2 \$800 800 (\$272) \$528 3 \$800 800 (\$272) \$528 4 \$800 800 (\$272) \$528 5 \$800 800 (\$272) \$528 5A \$3000 3000

The AT IRR is calculated from

3000 = 800 (P/A, i, 5) + 3000 (P/F, i, 5).

Determine i from the tables using interpolation. This gives 26.7%.

Using the above estimation for AT IRR, we get 1 – 0.34) * 26.7% = 17.6%

Alternatively, we can work with the data in the last column and use

3000 = 528 (P/A, i, 5) + 3000 (P/F, i, 5).

From this, we get the AT IRR to also be 17.6%.

The general case that we look at is where the depreciation is based on MACRS. Here is the same data but where the asset is depreciated with a 5-year tax life. Here is the 5-year table.

 Year t MACRS %Rate 1 20.00 2 32.00 3 19.20 4 11.52 5 11.52 6 5.76

Recall that this has six years of values. So for this case we will look at a six year period.  EOY BTCF BTCF w/ 6 +6A DepreciationMACRS TI Income tax ATCF ATCF w/ 6 +6A 0 (\$3,000) (\$3,000) (\$3,000) (\$3,000) 1 800 800 \$600 \$200 (\$68) 732 732 2 800 800 960 (160) 54 854 854 3 800 800 576 224 (76) 724 724 4 800 800 346 454 (154) 646 646 5 800 800 346 454 (154) 646 646 6 800 1550 173 627 (213) 587 1,336.75 6A 750 750

There are two additional columns (BTCF w/ 6 +6A and ATCF w/ 6 +6A ) where the values for EOY 6 and 6A have been added. Notice that in the ATCF columns there is a series of values that are not at all regular. The equation for the IRR would involve seven terms of the P/F factor. Finding i from this data is tedious. Fortunately, the spreadsheet can make this very simple.

Now copy column three (BTCF w/ 6 +6A) to a blank spreadsheet starting in cell A1. In the cell directly below the column (cell A9) type or copy and paste “=IRR(A2:A8)” without the quotes. This will calculate the IRR (or ROR) for the column and the cell will show 18.9%.

Similarly, copy column eight (ATCF w/ 6 +6A) to the next column, starting in cell B1. In cell B9 type “=IRR(B2:B8)” (Be sure to include the “=” sign and no extra spaces.) This will calculate the IRR (or ROR) for the column and the cell will show 15.3%.

The estimate used above for non-depreciable assets, AT IRR = (1 - tax rate) (BT IRR), may be tested here:

AT IRR (estimate) =? (1 – 0.34) * 18.9% = 12.5.

This is not close to the value from the spreadsheet, 15.3%. On the other hand, the two values, BT IRR and AT IRR (estimate), are likely bounds for the actual IRR and may be used as starting points.

### Income Taxes

#### After-tax rate of return and spreadsheets

Question 1

Question 2

Question E1.

Calculate the IRR after taxes for the investment shown below.

 A B C = A - B D = - 0.40 C E = A + D EOY BTCF Dt TI Tax ATCF 0 -40,000 0 0 0 -40,000 1 15,000 0 15,000 -6,000 9,000 2 15,000 0 15,000 -6,000 9,000 3 20,000 0 20,000 -8,000 12,000 4 21,000 0 21,000 -8,400 12,600 5 22,000 0 22,000 -8,800 13,200 5 (S) 35,000 --- -5,000 2,000 37,000

Choose an answer by clicking on one of the letters below, or click on "Review topic" if needed.

A 11.3%

B 25.6%

C 33.3%

D 42.0%

Review topic

Question 2.

Calculate the IRR after taxes for the investment shown below. Note that a \$50k loan at 10% interest was received to finance the investment, with the loan repaid by three, uniform, end-of-year payments beginning one year after the loan is received.

The applicable combined tax rate is 40%.

Below is the partially completed net cash flow table.

 EOY Pre-loan CF Loan CF Dep Interest TI Tax ATCF 0 -50,000 +50,000 0 0 0 0 0 1 20,000 -20,105 9,000 5,000 6,000 -2,400 -2,505 2 20,000 -20,105 9,000 3,490 510 -3,004 -3,109 3 20,000 -20,105 9,000 4 20,000 0 9,000 5 20,000 0 9,000 5 (s.v.) 5,000 0 0 0 0 0 5,000

Below is the partially completed loan amortization table (annual payment = \$20,105).

 Year Amount owed start of year Interest Amount paid on principal Amount owed end of year 1 50,000 5,000 15,105 34,895 2 34,895 3,490 3
Choose an answer by clicking on one of the letters below, or click on "Review topic" if needed.

A 10%

B 63%

C 18.8%

D 70%

Review topic 