Question

aaye e5en as kfh6t 6 1c 3 d , g

Answer 1

Formula sheet

A1	B	C	D	E	F	G	H	I	J	K
2
3		First WACC Needs to be calculated to value the project.
4
5		Formula for WACC is given as:
6		WACC = r(E) × w(E) + r(P) × w(P)+r(D) × (1 – t) × w(D)
7		Where, r(E), r(P) and r(D) are cost of equity, preferred stock and cost of debt, w(E), w(P) and W(D)
8		are weight of equity, preferred stock and debt and t is the tax rate
9
10		Calculation of cost of debt of coupon bonds:
11		Cost of debt will be the yield to maturity of the bond can be calculated as follows:
12		Time to maturity	20	years
13		Annual coupon rate	0.09
14		Par value	1000
15		Market Price	1085
16		Annual coupon	=D14*D13
17		Annual Period	=D12
18
19		Rate(nper,pmt,PV, [fv],type) function of excel can be used to find the yield to maturity as follows:
20		NPER	=D17
21		PMT	=D16
22		PV	=-D15
23		FV	=D14
24
25		Yield to maturity of the coupon bond is	=RATE(D20,D21,D22,D23)	=RATE(D20,D21,D22,D23)
26
27		Hence Cost of Debt is	=D25
28
29
30		Calculation of Cost of Equity:
31
32		Beta of stock	2.45
33		Market Return (rm)	0.12
34		Risk free rate	0.05
35		As Per CAPM
36		r(E) = rf + ?*(rm-rf)
37		Cost of common equity=	=D34+D32*D33	=D34+D32*(D33-D34)
38
39		Hence cost of Equity is	=D37
40
41
42		Calculation of cost of preferred stock:
43		Cost of preferred stock can be calculated as follows:
44		Annual Dividend of preferred stock	=50*5.5%	(Assuming $50 as par value)
45		Current Price	40
46		Floatation cost (F)	0
47		Cost of preferred stock	=Dividend/Current Price*(1-F)
48		Cost of preferred stock	=D44/(D45*(1-D46))	=D44/(D45*(1-D46))
49
50		Hence Cost of Preferred Stock is	=D48
51
52
53		Calculation of WACC:
54
55		Source of capital	Number of securities	Price	MV	Capital Structure	Cost
56		Debt	2000	=D15	=D56*E56	=F56/$F$59	=D27
57		Common Stock	60000	45	=D57*E57	=F57/$F$59	=D39
58		Preferred Stock	36000	=D45	=D58*E58	=F58/$F$59	=D50
59		Total			=SUM(F56:F58)	=SUM(G56:G58)
60		Tax Rate				0.34
61
62		WACC of the firm is	= r(E) × w(E) + r(P) × w(P)+r(D) × (1 – t) × w(D)
63			=G57*H57+G58*H58+G56*H56*(1-G60)	=G57*H57+G58*H58+G56*H56*(1-G60)
64
65		Hence WACC of the firm is	=D63
66
67		Calculation of NPV:
68		To calculate NPV of the project, free cash flow needs to be calculated as follows:
69		Free Cash Flow = Operating Cash Flow - Capital Expenditures - Change in working capital
70		Operating Cash Flow = EBIT*(1-Tax Rate)+Depreciation
71		Tax Rate	0.34
72		Initial investment calculation:
73		Capital cost required	200000
74
75
76		Depreciation each year can be calculated as follows:
77		Capital cost (B)	=D73
78		Depreciation follows MACRS 5 year-half year convention.
79
80		Hence depreciation each year can be calculated as follows:
81				Year 1	Year 2	Year 3	Year 4	Year 5	Year 6
82		MACRS 5 Year depreciation rate (rt)		0.2	0.32	0.192	0.1152	0.1152	0.0576
83		Depreciation (B*rt)		=$D77*E82	=$D77*F82	=$D77*G82	=$D77*H82	=$D77*I82	=$D77*J82
84		Book Value	=D77	=D84-E83	=E84-F83	=F84-G83	=G84-H83	=H84-I83	=I84-J83
85
86		Net Proceed from sale of machine calculation:
87
88		Proceed from sale of machine at the end of 6th year	50000
89		Book Value of Machine at the end of 6th year	=J84
90		Gain or Loss on sale of Systems and Softwares	=Proceed From Sale - Book value at the end of sale
91			=D88-D89
92
93		Gain or Loss on sale of Machine	=D91
94		Tax on Gain & Loss	=D93*D71
95		Net Proceed from Sale of System & Softwares	=Proceed from Sale - Tax Expense on gain or loss
96			=D88-D94
97
98		Free cash flow can be calculated as followed:
99		Year	0	1	2	3	4	5	6
100		Investment	=-D73
101		Savings		50000	=E101	=F101	=G101	=H101	=I101
102		Depreciation		=-E83	=-F83	=-G83	=-H83	=-I83	=-J83
103		Operating Income Before Tax (EBIT)		=SUM(E101:E102)	=SUM(F101:F102)	=SUM(G101:G102)	=SUM(H101:H102)	=SUM(I101:I102)	=SUM(J101:J102)
104		Tax expense		=-E103*$D$71	=-F103*$D$71	=-G103*$D$71	=-H103*$D$71	=-I103*$D$71	=-J103*$D$71
105		After Tax operating income (EBIT*(1-T))		=E103+E104	=F103+F104	=G103+G104	=H103+H104	=I103+I104	=J103+J104
106		Add Depreciation		=-E102	=-F102	=-G102	=-H102	=-I102	=-J102
107		Operating Cash Flow		=E105+E106	=F105+F106	=G105+G106	=H105+H106	=I105+I106	=J105+J106
108		Net Proceed from Sale							=D96
109		Free Cash Flow	=SUM(D100:D108)	=SUM(E107:E108)	=SUM(F107:F108)	=SUM(G107:G108)	=SUM(H107:H108)	=SUM(I107:I108)	=SUM(J107:J108)
110
111		NPV calculation:
112		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
113		Given the following cash flow and WACC, NPV for the project can be calculated as follows:
114		Year	0	1	2	3	4	5	6
115		Free Cash Flow (FCF)	=D109	=E109	=F109	=G109	=H109	=I109	=J109
116		WACC (i)	=D65
117		(P/F,i,n) for each year		=1/((1+$D116)^E114)	=1/((1+$D116)^F114)	=1/((1+$D116)^G114)	=1/((1+$D116)^H114)	=1/((1+$D116)^I114)	=1/((1+$D116)^J114)
118		Present Value of cash flows = FCF*(P/F,i,n)		=E115*E117	=F115*F117	=G115*G117	=H115*H117	=I115*I117	=J115*J117
119		Present value if future cash flows	=SUM(E118:J118)	=SUM(E118:J118)
120
121		NPV for Project	=Present value fo future cash flows - Initial investment
122			=D119+D115	=D119+D115
123
124		Hence NPV of the Project is	=D122
125		(Assuming par value of preferred stock is $50.)
126