Question

(USING EXCEL PLEASE)

Caspian Sea Drinks is considering the purchase of a new water filtration system produced by Rube Goldberg Machines. This new equipment, the RGM-7000, will allow Caspian Sea Drinks to expand production. It will cost $15.00 million fully installed and will be fully depreciated over a 15 year life, then removed for no cost. The RGM-7000 will result in additional revenues of $2.79 million per year and increased operating costs of $766,073.00 per year. Caspian Sea Drinks' marginal tax rate is 26.00%. The internal rate of return for the RGM-7000 is _____.

Answer 1

Annual depreciation = $15 million/15 years = $1 million per year

We will now compute operating cash flow and total cash flow for each year and then determine IRR in excel.

	a	b	c	d	e = b-c-d	f = 26% of e	g = e+d-f
Year	Capital spending	Revenues	Operating costs	Depreciation	EBIT	Tax @ 26%	Operating cash flow = EBIT + depreciation - tax
-	-       15,000,000
1		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
2		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
3		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
4		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
5		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
6		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
7		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
8		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
9		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
10		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
11		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
12		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
13		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
14		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706
15		2,790,000	-                  766,073	-         1,000,000	1,023,927	-         266,221	1,757,706

We can now compute IRR using th trial and error method (i.e. using different rates till we get NPV as nil)

Year (n)	Cash flows	1+r	Cash flow/(1+r)^n
-	-       15,000,000	1.08049	-           15,000,000.00
1	1,757,706		1,626,765.20
2	1,757,706		1,505,578.89
3	1,757,706		1,393,420.39
4	1,757,706		1,289,617.16
5	1,757,706		1,193,546.79
6	1,757,706		1,104,633.20
7	1,757,706		1,022,343.25
8	1,757,706		946,183.52
9	1,757,706		875,697.32
10	1,757,706		810,462.02
11	1,757,706		750,086.43
12	1,757,706		694,208.54
13	1,757,706		642,493.29
14	1,757,706		594,630.58
15	1,757,706		550,333.42
		Total	0

Thus IRR = 8.049%. This can be rounded to 8.05%