Question

(b) A company is evaluating a project for which the costs and expected cash flows are as follows: Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 -$2,200,000 $400,000 $535,000 $650,000 $700,000 $880,000 The company uses a discount rate of 10% for all projects. Given the information: i. Determine NPV of the project. (2 marks) Determine payback period of the project. (1.5 marks) Determine discounted payback period of the project. (2 marks) iv. Will the payback period change if the discount rate is changed to 8%? (0.5 mark)

Answer 1

DISCOUNTED PAYBACK PERIOD PAYBACK PERIOD Discounting Factor @ 10% CUMULATIVE CASH FLOWS CUMULATIVE CASH FLOWS 2 YEAR 30 CASH FLOWS |-2200000 400000 535000 650000 700000 880000 PRESENT VALUE =B3*C3 =B4*C4 =B5*C5 =B6*C6 =B7*07 =B8*C8 =1/(1.1)^A4 1=1/(1.1)^A5 =1/(1.1)^A6 =1/(1.1)^A7 =1/(1.1) A8 =B4 =E4+B5 -E5+B6 =D4 =F4+D5 =F5+D6 =F6+D7 =F7+D8 =E6+B7 =E7+B8 NPV of Project =SUM(D3:08) NING CASH OUTFLOW AT THE BEGINNING 2200000 2200000 DIFFERENCE WITH CASH OUTFLOW =E10-E6 =F10-F7 DIFFERENCE IN 3RD & 4TH YEAR PROPORTION =E7-E6 =E11/E12 =F8-F7 =F11/F12 PAYBACK PERIOD (IN YEARS) =3+E13 =4+F13

Ε 4 A B C D DISCOUNTED PAYBACK PAYBACK PERIOD PERIOD Discounting CUMULATIVE CUMULATIVE YEAR CASH FLOWS Factor @ 10% PRESENT VALUE CASH FLOWS CASH FLOWS ol -22,00,000.00 1.00 -22,00,000.00 1 4,00,000.00 3,63,636.36 4,00,000.00 3,63,636.36 2 5,35,000.00 0.83 4,42,148.76 9,35,000.00 8,05,785.12 3 6,50,000.00 0.75 4,88,354.62 15,85,000.00 12,94,139.74 4 7,00,000.00 4,78,109.42 22,85,000.00 17,72,249.16 5 8,80,000.00 5,46,410.76 31,65,000.00 23,18,659.93 NPV of Project 1,18,659.93 CASH OUTFLOW AT THE BEGINNING 22,00,000.00 22,00,000.00 0.68 8 6,15,000.00 4,27,750.84 DIFFERENCE WITH CASH OUTFLOW DIFFERENCE IN 3RD & 4TH YEAR PROPORTION PAYBACK PERIOD (IN YEARS) 7,00,000.00 0.88 5,46,410.76 0.78 3.88 4.78

DISCOUNTING FACTOR = 8%

N DISCOUNTED PAYBACK PERIOD PAYBACK PERIOD 2 YEAR Discounting Factor @ 8% CUMULATIVE CASH FLOWS CUMULATIVE CASH FLOWS 4 1 CASH FLOWS -2200000 400000 535000 650000 700000 880000 =1/(1.08)^14 =1/(1.08)^15 =1/(1.08)^16 =1/(1.08)^17 1=1/(1.08)^18 PRESENT VALUE =J3*K3 =J4*K4 =35*K5 =J6*K6 =J7*K7 =J8*K8 =J4 =M4+J5 =M5+J6 =M6+J7 =M7+J8 =L4 =N4+L5 =N5+L6 =N6+L7 =N7+L8 NPV of Project =SUM(L3:18) CASH OUTFLOW AT THE BEGINNING 2200000 2200000 DIFFERENCE WITH CASH OUTFLOW =M10-M6 =N10-N7 DIFFERENCE IN 3RD & 4TH YEAR PROPORTION =M7-M6 =M11/M12 =N8-N7 =N11/N12 PAYBACK PERIOD (IN YEARS) =3+M13 =4+N13

N YEAR K DISCOUNTED PAYBACK PAYBACK PERIOD PERIOD Discounting CUMULATIV CASH FLOWS Factor @8% PRESENT VALUE CUMULATIVE CASH FLOWS E CASH -22,00,000.00 1.00 -22,00,000.00 4,00,000.00 0.93 3,70,370.37 4,00,000.00 3,70,370.37 5,35,000.00 0.86 4,58,676.27 9,35,000.00 8,29,046.64 6,50,000.00 0.79 5,15,990.96 15,85,000.00 13,45,037.60 7,00,000.00 0.74 5,14,520.90 22,85,000.00 18,59,558.49 8,80,000.00 0.68 5,98,913.21 31,65,000.00 24,58,471.71 NPV of Project 2,58,471.71 CASH OUTFLOW AT THE BEGINNING 22,00,000.00 22,00,000.00 3 4 5 6,15,000.00 3,40,441.51 DIFFERENCE WITH CASH OUTFLOW DIFFERENCE IN 3RD & 4TH YEAR PROPORTION PAYBACK PERIOD (IN YEARS) 7,00,000.00 0.88 5,98,913.21 0.57 3.88 4.57

B)

A 2 PROJECT A 3 YEAR Discounting Factor @ 10% O Nm CASH FLOWS -2000000 900000 1135000 | 2000000 =1/(1.1)^A5 =1/(1.1) A6 =1/(1.1)^A7 PRESENT VALUE =B4*C4 =B5*C5 =B6*C6 =B7*C7 NPV of Project =SUM(D4:07) Discounting Factor @ 10% 10 PROJECT B 11 YEAR 120 13 1 CASH FLOWS -2000000 1400000 1135000 1000000 =1/(1.1)^A13 =1/(1.1)^A14 =1/(1.1)^A15 PRESENT VALUE =B12*C12 =B13*C13 =B14*C14 =B15*C15 NPV of Project =SUM(D12:D15)

A 2 PROJECT A 3 YEAR CASH FLOWS Discounting Factor @ 10% PRESENT VALUE 0 -20,00,000.00 1.00 -20,00,000.00 1 9,00,000.00 0.91 8,18,181.82 2 11,35,000.00 0.83 9,38,016.53 3 20,00,000.00 0.75 15,02,629.60 NPV of Project 12,58,827.95 10 PROJECT B 11 YEAR CASH FLOWS Discounting Factor @ 10% PRESENT VALUE 0 -20,00,000.00 1.00 -20,00,000.00 1 14,00,000.00 0.91 12,72,727.27 2 11,35,000.00 0.83 9,38,016.53 3 10,00,000.00 0.75 7,51,314.80 NPV of Project 9,62,058.60

CALCULATION OF PBP FOR PROJECT A & B

C 1 PROJECT A CALCULATION OF PBP Discounting Factor @ 10% CUMULATIVE CASH FLOWS 2 YEAR 30 4 1 5 2 CASH FLOWS -2000000 900000 1135000 | 2000000 =1/(1.1)^A4 =1/(1.1)^A5 =1/(1.1)^A6 PRESENT VALUE =B3*C3 =B4%C4 =B5*C5 =B6*C6 =B4 =E4+B5 =E5+B6 NPV of Project =SUM(D3:06) PAYBACK PERIOD LIES 8 BETWEEN 1ST & 2ND YEAR CASH OUTFLOW AT THE BEGINNING 2000000 9 DIFFERENCE WITH CASH OUTFLOW =E8-E4 DIFFERENCE IN 1ST & 2ND YEAR PROPORTION =E5-E4 =E9/E10 PAYBACK PERIOD (IN YEARS) =1+E11

B 1 PROJECT A CALCULATION OF PBP 2 YEAR CUMULATIVE CASH FLOWS Discounting Factor @ 10% PRESENT VALUE CASH FLOWS 0 -20,00,000.00 1.00 -20,00,000.00 1. 9,00,000.00 0.91 8,18,181.82 9,00,000.00 2 11,35,000.00 0.83 9,38,016.53 20,35,000.00 3 20,00,000.00 0.75 15,02,629.60 40,35,000.00 NPV of Project 12,58,827.95 PAYBACK PERIOD LIES BETWEEN 1ST & 2ND YEAR 8 CASH OUTFLOW AT THE BEGINNING 20,00,000.00 DIFFERENCE WITH CASH OUTFLOW 1 1,00,000.00 DIFFERENCE IN 1ST & 2ND YEAR 11,35,000.00 PROPORTION 0.97 PAYBACK PERIOD (IN YEARS) 1.97

PROJECT B CALCULATION OF PBP YEAR Discounting Factor @ 10% CUMULATIVE CASH FLOWS CASH FLOWS -2000000 1400000 1135000 1000000 =1/(1.1)^H4 =1/(1.1)^H5 =1/(1.1)^H6 PRESENT VALUE =13*J3 =14*J4 =15*35 =16*J6 =14 L4+15 =L5+16 NPV of Project =SUM(K3:66) PAYBACK PERIOD LIES BETWEEN 1ST & 2ND YEAR CASH OUTFLOW AT THE BEGINNING 2000000 DIFFERENCE WITH CASH OUTFLOW =18-L4 DIFFERENCE IN 1ST & 2ND YEAR PROPORTION =L5-14 =L9/L10 PAYBACK PERIOD (IN YEARS) =1+L11

G H PROJECT B CALCULATION OF PBP YEAR 0 CUMULATIVE CASH FLOWS Discounting Factor @ 10% PRESENT VALUE CASH FLOWS -20,00,000.00 1.00 -20,00,000.00 14,00,000.00 0.91 12,72,727.27 14,00,000.00 11,35,000.00 0.83 9,38,016.53 25,35,000.00 10,00,000.00 0.75 7,51,314.80 35,35,000.00 2 3 9,62,058.60 NPV of Project PAYBACK PERIOD LIES BETWEEN 1ST & 2ND YEAR 20,00,000.00 6,00,000.00 CASH OUTFLOW AT THE BEGINNING DIFFERENCE WITH CASH OUTFLOW DIFFERENCE IN 1ST & 2ND YEAR PROPORTION PAYBACK PERIOD (IN YEARS) 11,35,000.00 0.53 1.53

PROJECT B SHOULD BE CHOSEN AS PAYBACK PERIOD IS LESS THAN THAT OF PROJECT A.