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

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.

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

Return to After-tax rate of return and spreadsheets

Return to Income Taxes Tutorials Menu

Return to 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	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