Question

1. Using the information in Problem 8-45 (given below) construct an Excel spreadsheet to compute the net present value and internal rate of return. Use the format from your textbook, page 370. (20 points) 8-45. Calculating NPV and IRR Using Excel. Silver Mining LLC would like to mine for silver from land the company already owns. The equipment is expected to cost $4,000,000, has a useful life of 5 years, and will be sold at the end of 5 years for $400,000. Annual costs for maintenance and other cash expenses will total $550,000. Annual net cash receipts resulting from the sale of silver are predicted to be $1,900,000. Working capital of $270,000 is required at the beginning of the project and will be returned at the end of 5 years. The equipment will require refurbishing at the end of year 3 at a cost of $300,000. Although the company's cost of capital is 15 percent, management established a required rate of return of 20 percent due to the high risk associated with this project. Required: 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 Should the company accept the proposal? Explain.

Answer 1

Calculate Company - NPV + IRR as below ..

Silver Minning Company
Equipment cost($)		40,00,000
Useful Life(Yr)		5
Sale value ( $)		4,00,000
Annual cost of Maintenance ($)(b)		5,50,000
Annual net cash receipts$(a)		19,00,000
Cost of capital		15%
	Net Inflow	Initial Investment	Discount factor (D)	Discounted value($)(C*D)
year 0	(a-b)==C	(40,00,000)	1	(40,00,000)
Year1	13,50,000	13,50,000	0.87	11,73,913	0.87=1/1.15
Year2	13,50,000	13,50,000	0.756	10,20,794	0.87/1.15==0.756
Year3	13,50,000	13,50,000	0.658	8,87,647
Year4	13,50,000	13,50,000	0.572	7,71,867
Year5	13,50,000	13,50,000	0.497	6,71,189
Year5	4,00,000		0.497	1,98,871
Net Present Value				7,24,280
IRR( Sum of Cash outflow and Net Inflow)		20%