Question

Can I get the excel workings of this question?

WBC is currently financed using debt and equity with a targeted debt to equity ratio of one (D/E = 1). Its debt financing is from three sources, overdraft, bank bills and debentures, with the ratio of overdraft to bank bills to debentures of 1:2:3. Its equity is ordinary shares. These ratios represent the long-term capital structure target for WBC. The debenture pays an annual coupon of 12 per cent per annum on its $1,000 face value. The remaining term of the debenture is six years. The debenture is currently priced $922.23. The bank bills issued by WBC are ninety-day bills, with a face value of $100,000 and are currently priced at $97,593.58. The bank overdraft rate is 1 per cent per annum above the bank bill rate. The ordinary shares sell for $8.00. The projected dividend for year one is $1.10. Dividends are expected to grow at 6 per cent per annum indefinitely. Required Calculate the Weighted Average Cost of Capital (WACC) for WBC, assuming a tax rate of 30 per cent. Hint: this requires a calculation of the effective annual cost for each source of finance. Discuss whether the WACC could be used in the above project evaluation, and if so how. Include a discussion of any restrictions that apply to the use of WACC?

Answer 1

cost of common stock	(expected dividend/market price)+growth rate	(1.1/8)+6%	19.75%
cost of debentures = Using rate function in M S excel	rate(nper,pmt,pv,fv,type) nper = 6 pmt =1000*12% pv = -922.23 fv =1000 type = 0	RATE(6,120,-922.33,1000,0)	14.00%
after tax cost of debenture	14*(1-.3)	14*(1-.3)	9.8
cost of capital of bank bill = (face value/current market price)^(365/90) -1	(100000/97593.58)^(1*365/90)-1	10.38%
after tax cost on bank bill	10.38*(1-.3)	7.27
cost of capital overdraft rate	effective rate on bank bills + 1%	10.38%+1%	11.38%
after tax cost on overdraft	11.38*(1-.3)	7.97
WACC	weight	component cost	weight*component cost
common stock	0.5	19.75%	0.09875
Debt-50%
overdraft	0.1	9.80%	0.0098
bank bill	0.2	7.27%	0.01454
debentures	0.3	7.97%	0.02391
WACC = sum of weight*component cost			14.70%