Question

3. Construct an Excel spreadsheet to answer the questions in Problem 8-39 as given below: 8-39 Calculating NPV and IRR Using Excel. Mountain Hardware would like to expand into a new warehouse at a cost of $500,000. The warehouse is expected to have a life of 20 years, and a salvage value of $100,000. Annual costs for maintenance, insurance, and other cash expenses will total $60,000. Annual net cash receipts resulting from this expansion are predicted to be $115,000. The company's required rate of return is 12 percent Required: a. Use Excel to calculate the net present value and internal rate of return in a format similar to the Computer Application spreadsheet shown in the chapter. b. Should the company purchase the warehouse? Explain.

Answer 1

Calculatoiin Of NPV
	Year	Particular	Discounding Factor@12%	Cash Flow	Dscounted Cash Flow		Discounting At 8%	Cash Flow	Discounted Cash Flow
	0	Initial Cash Flow	1	-500000	-500000		1	-500000	-500000
	1	Annual receipt less maintenance	0.893	55000	49107		0.926	55000	50926
	2	Annual receipt less maintenance	0.797	55000	43846		0.857	55000	47154
	3	Annual receipt less maintenance	0.712	55000	39148		0.794	55000	43661
	4	Annual receipt less maintenance	0.636	55000	34953		0.735	55000	40427
	5	Annual receipt less maintenance	0.567	55000	31208		0.681	55000	37432
	6	Annual receipt less maintenance	0.507	55000	27865		0.630	55000	34659
	7	Annual receipt less maintenance	0.452	55000	24879		0.583	55000	32092
	8	Annual receipt less maintenance	0.404	55000	22214		0.540	55000	29715
	9	Annual receipt less maintenance	0.361	55000	19834		0.500	55000	27514
	10	Annual receipt less maintenance	0.322	55000	17709		0.463	55000	25476
	11	Annual receipt less maintenance	0.287	55000	15811		0.429	55000	23589
	12	Annual receipt less maintenance	0.257	55000	14117		0.397	55000	21841
	13	Annual receipt less maintenance	0.229	55000	12605		0.368	55000	20223
	14	Annual receipt less maintenance	0.205	55000	11254		0.340	55000	18725
	15	Annual receipt less maintenance	0.183	55000	10048		0.315	55000	17338
	16	Annual receipt less maintenance	0.163	55000	8972		0.292	55000	16054
	17	Annual receipt less maintenance	0.146	55000	8010		0.270	55000	14865
	18	Annual receipt less maintenance	0.130	55000	7152		0.250	55000	13764
	19	Annual receipt less maintenance	0.116	55000	6386		0.232	55000	12744
	20	Annual receipt less maintenance	0.104	55000	5702		0.215	55000	11800
	20	Salvage Value	0.104	100000	10367		0.215	100000	21455
		NPV			-78814		NPV		61453
	NPV = -78814
	Calculation Of IRR
		NPV at 12%	=-78814
		NPVat 8%	=61453
	We can calculate IRR by interpolation method
		= 8% + 61453/(78814+61453) * 4
		=9.526% (approx)