Question

Present value of tax savings from depreciation of robot in 10 years

Rate of return 12%

Income tax 30%

Cost of robot 40000

Disposal value of robot 1000

years 10

Straight line depreciation

How to do it on excel

Answer 1

I will give you the answer first, then the excel formula version

Year	Depreciation	Tax on Depreciation	PV Factor	Present Value
1	3900	1170	0.892857143	$      1,044.64
2	3900	1170	0.797193878	$         932.72
3	3900	1170	0.711780248	$         832.78
4	3900	1170	0.635518078	$         743.56
5	3900	1170	0.567426856	$         663.89
6	3900	1170	0.506631121	$         592.76
7	3900	1170	0.452349215	$         529.25
8	3900	1170	0.403883228	$         472.54
9	3900	1170	0.360610025	$         421.91
10	3900	1170	0.321973237	$         376.71
	$ 39,000.00	$                11,700.00		$      6,610.76

Year	Depreciation	Tax on Depreciation	PV Factor	Present Value
1	=(40000-1000)/10	=D4*30%	=1/(1+12%)^C4	=E4*F4
=C4+1	=(40000-1000)/10	=D5*30%	=1/(1+12%)^C5	=E5*F5
=C5+1	=(40000-1000)/10	=D6*30%	=1/(1+12%)^C6	=E6*F6
=C6+1	=(40000-1000)/10	=D7*30%	=1/(1+12%)^C7	=E7*F7
=C7+1	=(40000-1000)/10	=D8*30%	=1/(1+12%)^C8	=E8*F8
=C8+1	=(40000-1000)/10	=D9*30%	=1/(1+12%)^C9	=E9*F9
=C9+1	=(40000-1000)/10	=D10*30%	=1/(1+12%)^C10	=E10*F10
=C10+1	=(40000-1000)/10	=D11*30%	=1/(1+12%)^C11	=E11*F11
=C11+1	=(40000-1000)/10	=D12*30%	=1/(1+12%)^C12	=E12*F12
=C12+1	=(40000-1000)/10	=D13*30%	=1/(1+12%)^C13	=E13*F13
	=SUM(D4:D14)	=SUM(E4:E14)		=SUM(G4:G14)