Question

Aerotron Electronics is considering purchasing a water filtration system to assist in circuit board manufacturing. The system costs $33,000. It has an expected life of 7 years at which time its salvage value will be $9,500. Operating and maintenance expenses are estimated to be $2,000 per year. If the filtration system is not purchased, Aerotron Electronics will have to pay Bay City $10,500 per year for water purification. If the system is purchased, no water purification from Bay City will be needed. Aerotron Electronics must borrow 1/2 of the purchase price, but they cannot start repaying the loan for 2 years. The bank has agreed to 3 equal annual payments, with the 1st payment due at the end of year 2. The loan interest rate is 8.0 % compounded annually. Aerotron Electronics’ MARR is 13.0 % compounded annually.

What is the present worth of this investment?

***Please show ALL Excel functions used with detail, THANK YOU!***

Answer 1

using Excel

Year	investment	Loan payment	Annual revenue	Annual cost	Salvage value	Net cash flow
0	-16500					-16,500.00
1		0.00	10,500.00	-2,000.00		8,500.00
2		-6,914.76	10,500.00	-2,000.00		1,585.24
3		-6,914.76	10,500.00	-2,000.00		1,585.24
4		-6,914.76	10,500.00	-2,000.00		1,585.24
5			10,500.00	-2,000.00		8,500.00
6			10,500.00	-2,000.00		8,500.00
7			10,500.00	-2,000.00	9,500.00	8,500.00
					NPW	6,643.70

Showing formula in Excel

Year	investment	Loan payment	Annual revenue	Annual cost	Salvage value	Net cash flow
0	=-33000*0.5					=B2+C2+D2+E2
1		0	10500	-2000		=B3+C3+D3+E3
2		=-PMT(8%,3,-33000*0.5*1.08)	10500	-2000		=B4+C4+D4+E4
3		=-PMT(8%,3,-33000*0.5*1.08)	10500	-2000		=B5+C5+D5+E5
4		=-PMT(8%,3,-33000*0.5*1.08)	10500	-2000		=B6+C6+D6+E6
5			10500	-2000		=B7+C7+D7+E7
6			10500	-2000		=B8+C8+D8+E8
7			10500	-2000	9500	=B9+C9+D9+E9
					NPW	=NPV(13%,G3:G9)+G2