Question

Seneca Street Brewing wants to expand its brewing operations and is considering equipment from two different suppliers. Costs and expected cash flows are given below. Year Supplier 1 Supplier 2 0 -1,700,000 -1,700,000 - 850,000 1,100,000 374,000 800,000 N W 900,000 430,000 A 1,300,000 150,000 Their discount rate is 12% Calculate the payback, discounted payback, internal rate of return (IRR), and net present value (NPV) for the two options. Copyright © by Jarrod Johnston All Rights Reserved. Supplier 1 Supplier 2 Payback Discounted Payback IRR

Answer 1

Using excel formula to calculate

Year	0	1	2	3	4
Supplier 1	-1700000.00	850000	374000	900000	1300000
Cumulative Cash flow	-1700000	-850000	-476000	424000	1724000
Payback Period	2.53	( Payback Period =2+476000/900000)		3	1400
Year	0	1	2	3	4
Supplier 1	$                            (1,700,000.00)	1100000	800000	430000	150000
Cumulative Cash flow	-1700000	-600000	200000	630000	780000
Payback Period	1.75	( Payback Period =1+600000/800000)		3	1400

Year	0	1	2	3	4
Project A	-1700000.00	850000	374000	900000	1300000
Discounted Cash Flow	-1700000.00	758928.57	298150.51	640602.22	826173.50
Cumulative Cash flow	-1700000.00	-941071.43	-642920.92	-2318.70	823854.81
Discounted Payback Period	3.00
Excel formula	(=3+2318.70/826173.50)
Year	0	1	2	3	4.00
Project B	$                            (1,700,000.00)	1100000	800000	430000	150000
Discounted Cash Flow	-1700000.00	982142.86	637755.10	306065.51	95327.71
Cumulative Cash flow	-1700000.00	-717857.14	-80102.04	225963.47	321291.18
Discounted Payback Period	2.26
Excel formula	(=2+80102.04/306065.51)

		A	B
	Year	Supplier 1	Supplier 2
1	0	(1,700,000.00)	(1,700,000.00)
2	1	850000	1100000
3	2	374000	800000
4	3	900000	430000
5	4	1300000	150000
	NPV	$823855	$321291
	Using excel formula	NPV(0.12,A2:A6)+A1	NPV(0.12,B2:B6)+B1
	IRR	31.28%	23.84%
	Using excel formula	IRR(A1:A6)	IRR(B1:B6)