Question

Here are the expected cash flows for three projects: Cash Flows (dollars) Project Year: 1 2 4 -5,300 -1,300 -5,300 +1,075 +1,075 +1,300 +1,075 +3,150 +2,150 +3,150 0 +3,150 +5,150 +1,075 a. What is the payback period on each of the projects? Project Payback Period years A В years C years AB C

Answer 1

a]

Payback period is the time taken for the cumulative cash flows to equal zero

C3 А - fx =C2+B3 в D E F G Cumulative Cumulative Cumulative Cash Flow - A CF-A Cash Flow-BCF-B Cash Flow-CCF-C (5,300) (5,300) (1,300) (1,300) (5,300) (5,300) 1,075 (4,225)! (1,300) 1,075 (4,225) 1,075 (3,150) 1,300 1,075 (3,150) 3,150 2,150 2,150 3,150 3,150 5,300 5,150 5,150

=C2+B3 fc В А Cash Flow - B 20 1-1300 Cash Flow - A -5300 [1075 1075 3150 Cumulative CF-A [=B2 |=C2+B3 =C3+B4 =C4+B5 =C5+B6 Cumulative CF-B =D2 =E2+D3 =E3+D4 =E4+D5 =E5+D6 Cash Flow-C -5300 1075 1075 3150 5150 Cumulative CF-C =F2 =G2+F3 =G3+F4 =G4+F5 =G5+F6 N 1300 2150 3150 w

Payback period of A = 3 years

Payback period of B = 2 years

Payback period of C = 3 years

b]

With a cutoff of period of 2 years, only Project B would be accepted as the payback periods of Projects A and C is more than 2 years

c]

With a cutoff period of 3 years Projects A, B and C would be accepted as they all have a payback period of 3 or less

d]

NPV is calculated using NPV function in Excel :

A = $1,241.09

B = $3,268.56

C = $2,031.83

B7 А - fx =NPV(12%,B3:B6) +B2 в с D | Ε F Cumulative Cumulative Cumulative Cash Flow - ACF-A Cash Flow-BCF-B Cash Flow-CCF-C (5,300) (5,300) (1,300) (1,300) (5,300) (5,300) 1,075 (4,225) (1,300) 1,075 (4,225) 1,075 (3,150) 1,300 1,075 (3,150) 3,150 2,150 2,150 3,150 3,150 5,300 5,150 5,150 (1,241.09) 3,268.56 2,031.83 7 NPV

fx =NPV(12%,B3:36) +B2 Cash Flow - A Cash Flow-B - 1300 20 -5300 31 1075 1075 3150 Cumulative CF-A =B2 =C2+B3 =C3+B4 =C4+B5 =C5+B6 Cumulative CF - B =D2 =E2+D3 =E3+D4 =E4+D5 =E5+D6 Cumulative CF-C =F2 =G2+F3 =G3+F4 =G4+F5 =G5+F6 42 5 3 64 7 NPV Cash Flow-C -5300 1075 1075 3150 5150 =NPV(12%,F3:F6)+F2 1300 2150 3150 =NPV(12%,D3:06)+D2 =NPV(12%,B3:36)+B2