Question

please do this in excel and show all the functions.

A new grape press was purchased for $150,000. The annual operating and maintenance costs for the press are estimated to be $4,000 the first year. These costs are expected to increase by $5,000 each year after the first year. The market value is expected to decrease by $25,000 each year to a value of zero. (Hint: treat the market value as a salvage value.) Installation and removal of a press each cost $3,500. Using an interest rate of 9%, determine the economic life of the press. (13-13)

Answer 1

	Present Value (PV) of Cash Flow:
	(Cash Flow)/((1+i)^N)
	i=Discount Rate=Interest Rate=9%	0.09
	N=Year of Cash Flow
	Initial Cash Flow=Cost+Installation expense
	Initial Cash Flow=150000+3500	$153,500
	Terminal Cash inflow=Salvage Value-Removal Expense
	Terminal Cash inflow=Salvage Value-$3500
	Terminal Cash Flow Year1=150000-2500-3500		121500
	Terminal Cash Flow Year1=121500-25000		96500
		N	A	B=A/(1.09^N)	C=-(153500-B)	D	E=D/(1.09^N)	F	G=C+F	H(Using PMT function with Rate=9%,Nper=N,PV=-G)
		End of year	Terminal Cash Flow=Market Value-3500	PV of Salvage Value	PV of net capital cost	Annual maintenance and operating cost	PV of annual maintenance cost	Cumulative PV of annual Cashflow	PV of total Cash Flow	Equivalent annualcost
		0
		1	$121,500	$111,468	($42,032)	($4,000)	$       (3,670)	$     (3,670)	($45,702)	($49,815)	(Using PMT function with Rate=9%,Nper=1,PV=45702)
		2	$96,500	$81,222	($72,278)	($9,000)	$       (7,575)	$   (11,245)	($83,523)	($47,480)	(Using PMT function with Rate=9%,Nper=2,PV=83523)
		3	$71,500	$55,211	($98,289)	($14,000)	$     (10,811)	$   (22,055)	($120,344)	($47,543)	(Using PMT function with Rate=9%,Nper=3,PV=120344)
		4	$46,500	$32,942	($120,558)	($19,000)	$     (13,460)	$   (35,515)	($156,074)	($48,175)	(Using PMT function with Rate=9%,Nper=4,PV=156074)
		5	$21,500	$13,974	($139,526)	($24,000)	$     (15,598)	$   (51,114)	($190,640)	($49,012)	(Using PMT function with Rate=9%,Nper=5,PV=-190640)
		6	($3,500)	($2,087)	($155,587)	($29,000)	$     (17,292)	$   (68,406)	($223,993)	($49,932)	(Using PMT function with Rate=9%,Nper=6,PV=-223993)
	Annual Equivalent COST is LOWEST   for 2 years		($47,543)													015 - x fc =PMT(9%,G15,-N15) K L M N O P Q R S G H N=Year of Cash Flow Initial Cash Flow=Cost+Installation expense Initial Cash Flow=150000+3500 $153,500 Terminal Cash inflow=Salvage Value-Removal Expense Terminal Cash inflow=Salvage Value-$3500 Terminal Cash Flow Year1=150000-2500-3500 121500 Terminal Cash Flow Year1=121500-25000 96500 N B=A/(1.09^N) C=-(153500-B) D G=C+F H(Using PMT function with Rate=9%,Nper=N,PV= Terminal Cash PV of Flow=Marke Salvage End of year t Value-3500 Value E =D/(1.09^N F Annual maintena PV of Cumulativ nce and annuale PV of operatin maintenanc annual g cost e cost Cashflow PV of net capital cost PV of total Equivalent Cash Flow annualcost 1 $121,500 96 500 $111,468 $91 222 ($42,032) ($4,000) $ 16722791 Iso Onni (3,670) $ (3,670) ($45,702) 17 575) (11 205) $925221 ($49,815)| 547 901 (Using PMT function with Rate=9%, Irina MT function with Pato-0% 2
	The Truck should be operated for OPTIMUM 2 years