Question

1. NPV and IRR and Cashflows of Finite Projects

Company XYZ decides to invest in a $25,000,000 project. The company will finance the project with 50% debt and 50% equity. The term of the loan is interest only, compounded annually, 5%, and over 5 years. The project will allow the company to produce and sell an additional 100,000 widgets at $130 a widget. The cost of producing each widget is 50% of revenue. Furthermore, the project will fully depreciate in 5 years on a straight-line basis and the project will end. The tax rate is 21%

1. What is 1^st year CF to equity holders?
2. What is the 5^th year CF to equity holders?
3. What is the IRR?
4. If the company’s required rate on this project is 10%, what is the NPV?
5. Would you accept it?

Please show the works in detail on Excel Sheet ( with Formulas ). Thank you so much!!

Answer 1

Year	0	1	2	3	4	5
1.Initial Investment	-25000000
2.Incremental units sold		100000	100000	100000	100000	100000
3.Revenues at $ 130/unit		13000000	13000000	13000000	13000000	13000000
4.Costs at (130*50%)/unit		-6500000	-6500000	-6500000	-6500000	-6500000
5.Depn.(st.line)		-5000000	-5000000	-5000000	-5000000	-5000000
6.EBIT( sum 3 to 5)		1500000	1500000	1500000	1500000	1500000
7.Tax at 21%		-315000	-315000	-315000	-315000	-315000
8.NOPAT(6+7)		1185000	1185000	1185000	1185000	1185000
9.Add Back: depn.(line 5)		5000000	5000000	5000000	5000000	5000000
10.Operating Cash flows(8+9)		6185000	6185000	6185000	6185000	6185000
11.Total annual FCFFs(1+10)	-25000000	6185000	6185000	6185000	6185000	6185000
12.After-tax interest on debt(12500000*5%*(1-21%))		-493750	-493750	-493750	-493750	-493750
13.New debt/Debt repayment	12500000					-12500000
14.Cash flow to Equityholders(FCFE)(sum 11 to 13)	-12500000	5691250	5691250	5691250	5691250	-6808750
15. PV F at 10%(1/1.1^ Yr.n)	1	0.90909	0.82645	0.75131	0.68301	0.62092
16.PV of FCFs at 10%(11*15)	-25000000	5622727	5111570	4646882	4224438	3840398.4
17. NPV of FCFs at 10%(sum of line 16)	-1553984
(value of the company)
17.a.IRR of FCFFs (line 11)	7.54%
The project cannot be accepted, as NPV of the FCFFs is NEGATIVE & IRR , 7.54% < reqd. ROR, 10%
18. PV of FCFEs at 10%(14*15)	-12500000	5173864	4703512	4275920	3887200	-4227698
19.NPV of FCFEs(sum of line 18)	1312799
(Value of Equity)
19.a.IRR oF FCFEs (line 14)	17.04%
Answers:
1st year CF to equity holders		5691250
5th year CF to equity holders		-6808750
What is the IRR (for the co. as a whole)		7.54%
NPV at the company's required rate of return,10%		-1553984
Would you accept it		NO
Note: Lines, 12, 13, 14 18, 19 & 19.a. done to show cash flow to equity holders , ie. FCFE

0	1	2	3	4	5
-25000000
	100000	100000	100000	100000	100000
	=D5*130	=E5*130	=F5*130	=G5*130	=H5*130
	=D5*-130*50%	=E5*-130*50%	=F5*-130*50%	=G5*-130*50%	=H5*-130*50%
	=C3/5	=D8	=E8	=F8	=G8
	=SUM(D6:D8)	=SUM(E6:E8)	=SUM(F6:F8)	=SUM(G6:G8)	=SUM(H6:H8)
	=D9*-21%	=E9*-21%	=F9*-21%	=G9*-21%	=H9*-21%
	=D9+D10	=E9+E10	=F9+F10	=G9+G10	=H9+H10
	=-D8	=-E8	=-F8	=-G8	=-H8
	=D11+D12	=E11+E12	=F11+F12	=G11+G12	=H11+H12
=C3+C13	=D3+D13	=E3+E13	=F3+F13	=G3+G13	=H3+H13
	=-(12500000*5%*(1-21%))	=-(12500000*5%*(1-21%))	=-(12500000*5%*(1-21%))	=-(12500000*5%*(1-21%))	=-(12500000*5%*(1-21%))
12500000					-12500000
=SUM(C15:C17)	=SUM(D15:D17)	=SUM(E15:E17)	=SUM(F15:F17)	=SUM(G15:G17)	=SUM(H15:H17)
=1/1.1^ C2	=1/1.1^ D2	=1/1.1^ E2	=1/1.1^ F2	=1/1.1^ G2	=1/1.1^ H2
=C15*C20	=D15*D20	=E15*E20	=F15*F20	=G15*G20	=H15*H20
=SUM(C21:H21)
=IRR(C15:H15)
=C18*C20	=D18*D20	=E18*E20	=F18*F20	=G18*G20	=H18*H20
=SUM(C28:H28)
=IRR(C18:H18)