Question

Calexico Hospital plans to invest in a new MRI machine. The cost of the MRI is $1.4 million. The MRI has an economic life of 5 years, and it will be depreciated over a five-year life to a $200,000 salvage value. Additional revenues attributed to the new MRI will be in the amount of $1.5 million per year for 5 years. Additional operating expenses, excluding depreciation expense, will amount to $1 million per year for 5 years. Over the life of the machine, net working capital will increase by $30,000 per year for 5 years. Version 1 a. Assuming that the hospital is a non-profit entity, what is the project’s net present value (NPV) at a discount rate of 8%, and what is the project’s IRR? b. Assuming that the hospital is a for-profit entity and the tax rate is 30%, what is the project’s NPV at a cost of capital of 8%, and what is the project’s IRR? I need help getting the excel calculations correct.

Answer 1

	Non for Profit entity	A	B	C	D	E	F
1	Year	0	1	2	3	4	5
2	Cost of MRI	1.4
3	Revenue		1.5	1.5	1.5	1.5	1.5
4	Operating expenses		1	1	1	1	1
5	Depreciation = (Cost of MRI - Salvage Value)/5		0.24	0.24	0.24	0.24	0.24
6	EBIT= Revenue - Operating Expenses - Depeciation		0.26	0.26	0.26	0.26	0.26
7	Taxes		0	0	0	0	0
8	EAT= EBIT -Taxes		0.26	0.26	0.26	0.26	0.26
9	Add Depreciation		0.24	0.24	0.24	0.24	0.24
10	minus Working capital		0.03	0.03	0.03	0.03	0.03
11	add salvage value						0.2
12	Free Cash Flow	-1.4	0.47	0.47	0.47	0.47	0.67
13	Discount rate	8%
	NPV	$0.61
	NPV using excel formula = NPV(A13,B12:F12)+A12
	IRR	22.63%
	Profit entity	A	B	C	D	E	F
1	Year	0	1	2	3	4	5
2	Cost of MRI	1.4
3	Revenue		1.5	1.5	1.5	1.5	1.5
4	Operating expenses		1	1	1	1	1
5	Depreciation = (Cost of MRI - Salvage Value)/5		0.24	0.24	0.24	0.24	0.24
6	EBIT= Revenue - Operating Expenses - Depeciation		0.26	0.26	0.26	0.26	0.26
7	Taxes= EBIT*Tax Rate		0.078	0.078	0.078	0.078	0.078
8	EAT= EBIT -Taxes		0.182	0.182	0.182	0.182	0.182
9	Add Depreciation		0.24	0.24	0.24	0.24	0.24
10	minus Working capital		0.03	0.03	0.03	0.03	0.03
11	add after tax salvage value						0.14
12	Free Cash Flow	-1.4	0.45	0.45	0.45	0.45	0.59
13	Discount rate	8%
	NPV	$0.50
	NPV using excel formula = NPV(A13,B12:F12)+A12
	IRR	20.25%

Please discuss in case of doubt