Question

Raytheon wishes to use an automated environmental chamber in the manufacture of electronic components. The chamber is to be used for rigorous reliability testing and burn-in. It is installed for $800,000, $350,000 of which is borrowed at 11% for 5 years, and will have a salvage value of $150,000 after 8 years. Its use will create an opportunity to increase sales by $650,000 per year and will have operating expenses of $250,000 per year. Corporate income taxes are 40%. Develop tables using a spreadsheet to determine the ATCF for each year and the after-tax PW, AW, IRR, and ERR, if the chamber is kept for 8 years. After-tax MARR is 10%. Determine for each year the ATCF and the PW, FW, AW, IRR, and ERR for the investment if:

straight-line depreciation is used over 8 years with no half-year convention and the loan is paid back using Method 1 (interest only at the end of each year of the loan, plus principal at the end of the last year).

straight-line depreciation is used over 8 years with no half-year convention and the loan is paid back using Method 2 (equal annual principal payments plus interest on the unpaid loan balance).

straight-line depreciation is used over 8 years with no half-year convention and the loan is paid back using Method 3 (equal annual principal plus interest payments during each year of the loan).

MACRS-GDS depreciation is used with the appropriate property class and the loan is paid back using Method 1 (interest only at the end of each year of the loan, plus principal at the end of the last year).

MACRS-GDS depreciation is used with the appropriate property class and the loan is paid back using Method 2 (equal annual principal payments plus interest on the unpaid loan balance).

MACRS-GDS depreciation is used with the appropriate property class and the loan is paid back using Method 3 (equal annual principal plus interest payments during each year of the loan).

Answer 1

		straight-line depreciation is used over 8 years with no half-year convention and the loan is paid back using Method 1
		Increase in sales per year	$650,000
		Increase in operating expenses	$250,000
		Before Tax Operating Income	$400,000
		After tax operating income	$240,000	(400000*(1-40%)
	N	Year	0	1	2	3	4	5	6	7	8
	A	Initial Cash Flow(800000-350000)	($450,000)
	B	After tax operating income		$240,000	$240,000	$240,000	$240,000	$240,000	$240,000	$240,000	$240,000
	C	Depreciation Tax shield		$40,000	$40,000	$40,000	$40,000	$40,000	$40,000	$40,000	$40,000
	D	Interest tax shield		$15,400	$15,400	$15,400	$15,400	$15,400	$15,400	$15,400	$15,400
	E	Loan repayment		-$38,500	-$38,500	-$38,500	-$38,500	-$38,500	-$38,500	-$38,500	-$388,500
	F=A+B+C+D+E	Net Cash Flow	($450,000)	$256,900	$256,900	$256,900	$256,900	$256,900	$256,900	$256,900	($93,100)	SUM
	PV=F/(1.1^N)	Present Value of Net Cash Flow	($450,000)	$233,545	$212,314	$193,013	$175,466	$159,515	$145,013	$131,830	-$43,432	$757,265
	FV=F*(1.1^(8-N))	Future Value of Net Cash Flow	-$964,615	$500,625	$455,114	$413,740	$376,127	$341,934	$310,849	$282,590	-$93,100	$1,623,265
		Present Worth(PW)	$757,265
		Future Worth (FW)	$1,623,265
		Annual Worth (AW)	$141,945	(Using PMT function with Rate=10%, Nper=8, PV=-PW)
		Internal Rate of Return (IRR)	53.95%	(Using IRR function of excel over Net Cash Flow)
		straight-line depreciation is used over 8 years with no half-year convention and the loan is paid back using Method 2
	N	Year	0	1	2	3	4	5	6	7	8
	A	Initial Cash Flow(800000-350000)	($450,000)
	B	After tax operating income		$240,000	$240,000	$240,000	$240,000	$240,000	$240,000	$240,000	$240,000
	C	Depreciation Tax shield		$40,000	$40,000	$40,000	$40,000	$40,000	$40,000	$40,000	$40,000
	D	Interest tax shield		$15,400	$13,475	$11,550	$9,625	$7,700	$5,775	$3,850	$1,925
	E	Loan repayment		-$82,250	-$77,438	-$72,625	-$67,813	-$63,000	-$58,188	-$53,375	-$48,563
	F=A+B+C+D+E	Net Cash Flow	($450,000)	$213,150	$216,038	$218,925	$221,813	$224,700	$227,588	$230,475	$233,363	SUM
	PV=F/(1.1^N)	Present Value of Net Cash Flow	($450,000)	$193,773	$178,543	$164,482	$151,501	$139,521	$128,467	$118,270	$108,865	$733,422
	FV=F*(1.1^(8-N))	Future Value of Net Cash Flow	-$964,615	$415,369	$382,724	$352,581	$324,756	$299,076	$275,381	$253,523	$233,363	$1,572,156
		Present Worth(PW)	$733,422
		Future Worth (FW)	$1,572,156
		Annual Worth (AW)	$137,476	(Using PMT function with Rate=10%, Nper=8, PV=-PW)
		Internal Rate of Return (IRR)	46.17%	(Using IRR function of excel over Net Cash Flow)
		MACRS-GDS depreciation is used with the appropriate property class and the loan is paid back using Method 1
	N	Year	0	1	2	3	4	5	6	7	8
	A	Initial Cash Flow(800000-350000)	($450,000)
	B	After tax operating income		$240,000	$240,000	$240,000	$240,000	$240,000	$240,000	$240,000	$240,000
	C	Depreciation Tax shield		$64,000	$102,400	$61,440	$36,864	$36,864	$18,432	$0	$0
	D	Interest tax shield		$15,400	$15,400	$15,400	$15,400	$15,400	$15,400	$15,400	$15,400
	E	Loan repayment		-$82,250	-$77,438	-$72,625	-$67,813	-$63,000	-$58,188	-$53,375	-$48,563
	F=A+B+C+D+E	Net Cash Flow	($450,000)	$237,150	$280,363	$244,215	$224,452	$229,264	$215,645	$202,025	$206,838	SUM
	PV=F/(1.1^N)	Present Value of Net Cash Flow	($450,000)	$215,591	$231,705	$183,482	$153,303	$142,355	$121,726	$103,671	$96,491	$798,324
	FV=F*(1.1^(8-N))	Future Value of Net Cash Flow	-$964,615	$462,138	$496,679	$393,311	$328,619	$305,150	$260,930	$222,228	$206,838	$1,711,278
		Present Worth(PW)	$798,324
		Future Worth (FW)	$1,711,278
		Annual Worth (AW)	$149,641	(Using PMT function with Rate=10%, Nper=8, PV=-PW)
		Internal Rate of Return (IRR)	52.36%	(Using IRR function of excel over Net Cash Flow)