Question

A company with name XYZ starts a new project with life 4 years. The company will put 20.000.000 USD in the project. The estimated sales per year is 1200 units a)Selling price for first year is 45.000USD/unit b)Variable cost for first yaer is 35.000USD/unit c)Fixed cost for first year is 2.000.000 USD d)Depreciation rates per year:20% , 32%,19.20%, 11.52% e)Net working capital requirements for the project are 20% of the projected annual sales at the beggining of each yea.r f) After first year, price of product, variable and fixed costs will increase with inflation glA the end of the 4 year-project, the equipment wil have 800.000 USD market value We also know the cost of capital which is 12% and the tax rate which is 35% What is the NPV, IRR and payback of this project?

Answer 1

		ASSUME ANNUAL INFLATION =4%=0.04
	a	Selling Price per unit year1	$45,000
	b=a*1.04	Selling Price per unit year2	$46,800
	c=b*1.04	Selling Price per unit year3	$48,672
	d=c*1.04	Selling Price per unit year4	$50,619
	e	Variable cost per unit year1	$35,000
	f=e*1.0	Variable cost per unit year2	$36,400
	g=f*1.04	Variable cost per unit year3	$37,856
	h=g*1.04	Variable cost per unit year4	$39,370
	i	Fixed cost per unit year1	$2,000,000
	j=i*1.04	Fixed cost per unit year2	$2,080,000
	k=j*1.04	Fixed cost per unit year3	$2,163,200
	l=k*1.04	Fixed cost per unit year4	$2,249,728
		Present Value (PV) of Cash Flow:
		(Cash Flow)/((1+i)^N)
		i=Discount Rate=Cost of capital=12%=0.12
		N=Year of Cash Flow
	N	Year	0	1	2	3	4
	A	Unit sales		1,200	1,200	1,200	1,200
	B	Selling Price per unit		$45,000	$46,800	$48,672	$50,619
	X=A*B	Sales Revenue		$54,000,000	$56,160,000	$58,406,400	$60,742,656
	C	Variable cost per unit		$35,000	$36,400	$37,856	$39,370
	D=B-C	Unit Contribution Margin		$10,000	$10,400	$10,816	$11,249
	E=A*D	TotalContribution		$12,000,000	$12,480,000	$12,979,200	$13,498,368
	F	Fixed costs		$2,000,000	$2,080,000	$2,163,200	$2,249,728
	G=E-F	Pretax profit (Before depreciation)		$10,000,000	$10,400,000	$10,816,000	$11,248,640
	H	Depreciation Rate		20%	32%	19.20%	11.52%
	I=H*$20million	Annual depreciation		$4,000,000	$6,400,000	$3,840,000	$2,304,000
	J	Accumulated depreciation		$4,000,000	$10,400,000	$14,240,000	$16,544,000
	K	End of year book value					$3,456,000	(20000000-1654400)
	L=G-I	Pretax profit (After depreciation)		$6,000,000	$4,000,000	$6,976,000	$8,944,640
	M=0.35*L	Tax expenses		$2,100,000	$1,400,000	$2,441,600	$3,130,624
	P=L-M	After tax profit		$3,900,000	$2,600,000	$4,534,400	$5,814,016
	Q=P+I	After tax Operating Cash Flow		$7,900,000	$9,000,000	$8,374,400	$8,118,016
	R	Investment cash flow	($20,000,000)
	S	Working Capital Need(20% of next year Sales Revenue)	$10,800,000	$11,232,000	$11,681,280	$12,148,531
	T	Cash Flow due to change in working capital	($10,800,000)	($432,000)	($449,280)	($467,251)	$12,148,531
	U	Pretax Salvage Value					$800,000
	V=U-K	Gain on salvage					($2,656,000)
	W=V*0.35	Tax on gain in salvage					($929,600)
	Y=U-W	After tax cash flow on salvage					$1,729,600
	Z=Q+R+T+Y	NET CASH FLOW	($30,800,000)	$7,468,000	$8,550,720	$7,907,149	$21,996,147
		CUMULATIVE NET CASH FLOW	($30,800,000)	($23,332,000)	($14,781,280)	($6,874,131)	$15,122,016
		Payback Period=Period at which cumulative cash flow=NIL
		PAYBACK PERIOD=4+(6874131/21996147)	4.31	YEARS
								SUM
	PV=Z/(1.12^N)	PRESENT VALUE OF NET CASH FLOW	$       (30,800,000)	$    6,667,857	$    6,816,582	$ 5,628,152	$   13,978,949	$ 2,291,540
		NET PRESENT VALUE	$            2,291,540
		IRR(INTERNAL RATE OF RETURN)	14.91%	(Using IRR function of excel over the NET CASH FLOW)