Question

Your company is contemplating the purchase of a large stamping machine. The machine will cost $161,000. With additional transportation and installation costs of $5,000 and $12,000​, ​respectively, the cost basis for depreciation purposes is $178,000. Its MV at the end of five years is estimated as $39,000. The IRS has assured you that this machine will fall under a three year MACRS class life category. The justifications for this machine include $39,000 savings per year in labor and $26,000 savings per year in reduced materials. The​ before-tax MARR is 25​% per​ year, and the effective income tax rate is 50​%. Assume the stamping machine will be used for only three​ years, owing to the​ company's losing several government contracts. The MV at the end of year three is $50,000. What is the income tax owed at the end of year three owing to depreciation recapture​ (capital gain)?

Choose the correct answer below.

A. The income tax owed at the end of year three is $11,815.

B. The income tax owed at the end of year three is $24,000.

C. The income tax owed at the end of year three is ​$36,810.

D. The income tax owed at the end of year three is ​$23,629.

E. The income tax owed at the end of year three is ​$18,405.

The answer is A. Can you please show the steps to solve this in Excel?

Answer 1

Formula	Year (n)	0	1	2	3
	Depreciation rate ('r)		33.33%	44.45%	7.41%
BV0*r	Depreciation (D)		59,327	79,121	13,181
BVn-1 - Dn	Book value (BV)	1,78,000	1,18,673	39,552	26,371
	Market value (MV)				50,000
MV - BV3	Gain or dep. recapture (G)				23,629
Tax rate*G	Income tax on gain				11,815

The only point to note here is that under MACRS depreciation, if an asset is sold off before it is fully depreciated then in the year of sale, only half the depreciation expense is charged. So, in this case, under 3-year MACRS depreciation, the depreciation rate for Year 3 should be 14.81% but since asset is being sold off before it is fully depreciated, the depreciation rate is halved to 7.41% and then book value is calculated.