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 | Depreciation MACRS |
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.
Return to After-tax rate of return and spreadsheets
Return to Income Taxes Tutorials Menu
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 | D_{t} | 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%
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 |
A 10%
B 63%
C 18.8%
D 70%