Question

Calculate payback periods. Please show calculations so I can duplicate it in excel.

7 Your division is considering two projects. Its wACC is 10%, and the projects, after-tax cash flows (in millions 8 of dollars) would be as follows: Expected Cash Flows Project A Project B 10 Time (S30) S5 s10 S15 S20 (S30) S20 S10 S8 S6 12 13 14 15 16 17 18 a. Calculate the projects' NPVs, IRRs, MIRRs, regular paybacks, and discounted payback Use Excel's NPV function as explained in 11model.xlsx. Note that the range does not include the initial costs, which are added separately. 10% $7.74 S6.55 19 WACC = 20 NPVA- 21 NPVB 23 We find the Internal rath of return with Exel's IRR functions 24 IRRA 19.19% 22.52% 25 IRR 26 7 we find the Inodified internal rate of return with Excel's MIRR function using the 10% WACC 28 MIRR 29 MIRR 30 31 Project A Payback Period 32 16.50% 15.57% Time period: 31 Project A Parback Period Time period: Cash flow: 0 34 Cumulative cash flow 35 36 Paybacka: 37 38 Project B Payback Period: Time period: 0 39 40 41 Cumulative cash flow: 42 43 Paybackg: Cash flow: 45 Project A Discounted Payback Period 46 47 48 49 Disc. cum. cash flow 50 51 Discounted PaybackA Time period: 0 Cash flow: Disc. cash flow Project B Discounted Payback Period: 54 Time period: 0 Cash flow: Disc. cash flow: 57 Disc. cum. cash flow: 59 Discounted Paybackg 61-b.lrthetw。proyects areindependent,whichprotect(s) shouldbechosen?

Answer 1

Payback Period = A +B/C

Where,

A = Last period with a negative cumulative cash flow

B = Absolute value of cumulative cash flow at the end of the period A

C = Total cash flow during the period after A

Computation of payback period for project A:

Period	Cash flow	‘Cumulative cash flow
0	($30)	($30)
1	$5	($25)
2	$10	($15)
3	$15	$0
4	$20	$20

Payback period for Project A = 2 + $ 15/$15 = 2 + 1 = 3 years

Computation of payback period for project B:

Period	Cash flow	Cumulative cash flow
0	($30)	($30)
1	$20	($10)
2	$10	$0
3	$8	$8
4	$6	$14

Payback period for Project B = 1 + $ 10/$10 = 1 + 1 = 2 years

---------------------------------------------------

Discounted Payback Period = A +B/C

Where,

A = Last period with a discounted negative cumulative cash flow

B = Absolute value of discounted cumulative cash flow at the end of the period A

C = Total discounted cash flow during the period after A

Computation of discounted payback period for Project A:

Period	Cash flow(CA)	Computation of PV Factor	PV Factor @ 10 % (F)	Discounted cash Flow(CA x F)	Discounted ‘Cumulative cash flow
0	($30)	1/(1+0.1)^0	1	($30)	($30)
1	$5	1/(1+0.1)^1	0.909090909090909	$4.55	($25.45)
2	$10	1/(1+0.1)^2	0.826446280991735	$8.26	($17.19)
3	$15	1/(1+0.1)^3	0.751314800901578	$11.27	($5.92)
4	$20	1/(1+0.1)^4	0.683013455365071	$13.66	$7.74

Discounted Payback period for Project A = 3 + $ 5.92/$ 13.66 = 3 + 0.433382 = 3.433382 or 3.43 years

Computation of discounted payback period for Project B:

Period	Cash flow (CB)	Computation of PV Factor	PV Factor @ 10 % (F)	Discounted cash Flow (CB x F)	Discounted ‘Cumulative cash flow
0	($30)	1/(1+0.1)^0	1	($30)	($30)
1	$20	1/(1+0.1)^1	0.909090909090909	$18.18	($11.82)
2	$10	1/(1+0.1)^2	0.826446280991735	$8.26	($3.55)
3	$8	1/(1+0.1)^3	0.751314800901578	$6.01	$2.46
4	$6	1/(1+0.1)^4	0.683013455365071	$4.10	$6.55

Discounted Payback period for Project B = 2 + $ 3.55/$ 6.01 = 2 + 0.590682 = 2.590682 or 2.59 years

---------------------------------------------------------------------------------

PV factor for period n = 1/ (1+r) ⁿ

Where r is rate of interest and n is the number of period

Multiply PV factor with the cash flow of respective year to get discounted cash flow. Discounted Cumulative cash flow in year 0 is the discounted cash flow or initial layout of the project. Add the discounted cash flow of year 1 with discounted cash flow year 0 to get discounted cumulative cash flow in year 1. With this discounted cumulative cash flow of year 1, add discounted cash flow of year 2 and get the discounted cumulative cash flow of year 2 and so on.

Project A Discounted Payback Period:                                          

If excel sheet is like below table, insert formulas as:

	A	B	C	D	E	F
1	Time period	0	1	2	3	4
2	Cash Flow	($30)	$5	$10	$15	$20
3	Disc. Cash Flow	=B2*(1/(1.1)^0)	=C2*(1/(1.1)^1)	=D2*(1/(1.1)^2)	=E2*(1/(1.1)^3)	=F2*(1/(1.1)^4)
4	Disc. Cum. Cash Flow	=B3	=B4+C3	=C4+D3	=D4+E3	=E4+F3
5
6	Discounted payback period A	=E1 + (-E4/F3)

This will display as:

	A	B	C	D	E	F
1	Time period	0	1	2	3	4
2	Cash Flow	($30)	$5	$10	$15	$20
3	Disc. Cash Flow	-30	$4.55	$8.26	$11.27	$13.66
4	Disc. Cum. Cash Flow	-30	$(25.45)	$(17.19)	$(5.92)	$7.74
5
6	Discounted payback period A	3.43