Question

You are investing in a project that requires an initial investment of $500,000. The project will last for 10 years with the following additional cash flows (CF) at the end of each year:

If the discount rate is 10%, calculate the payback periods in Excel for both the methods below;

Years	1	2	3	4	5	6	7	8	9	10
CF	-500,000	80,000	100,000	100,000	120,000	140,000	150,000	150,000	150,000	150,000

a) Payback Method [10]

b) Discounted Payback Method [10]

Answer 1

(a) Payback Method

Year	Cash Flows	Cumulative net Cash flow
0	(500,000)	(500,000)
1	(500,000)	(1,000,000)
2	80,000	(920,000)
3	100,000	(820,000)
4	100,000	(720,000)
5	120,000	(600,000)
6	140,000	(460,000)
7	150,000	(310,000)
8	150,000	(160,000)
9	150,000	(10,000)
10	150,000	140,000
	Payback Period =	9.067 years

Payback Period=Years before full recover + (Unrecovered cash inflow at start of the year/cash flow during the year )

= 9 Year + [ $10,000 / 150,000 ]

= 9 Year + 0.067 Years

= 9.067 Years

(b) Discounted Payback Method

Year	Cash Flows	PVF at 10%	Discounted Cash Flow	Cumulative net discounted Cash flow
0	(500,000)	1.0000	(500,000.00)	(500,000)
1	(500,000)	0.90909	(454,545.00)	(954,545)
2	80,000	0.82645	66,116.00	(888,429)
3	100,000	0.75132	75,132.00	(813,297)
4	100,000	0.68302	68,302.00	(744,995)
5	120,000	0.62093	74,511.60	(670,483)
6	140,000	0.56448	79,027.20	(591,456)
7	150,000	0.51316	76,974.00	(514,482)
8	150,000	0.46651	69,976.50	(444,506)
9	150,000	0.4241	63,615.00	(380,891)
10	150,000	0.38555	57,832.50	(323,058)

With the given discount rate of 10%, the Investment will not pay back in the 10 years period. Average discounted payback is $17,693.59/year in the first 10 years. If the same situation continues for a long period, the discounted payback period will be 28.259 years. It means the company will be able to recover the entire investment of $500,000 at the end of 28.259 years.