Question

Dave Hirsh publishes his own manuscripts and is unsure which of two new printers he should purchase. He is a novelist living in Parkman, Illinois. Having slept through most of his Finance 300 course in college, he is unfamiliar with cash flow analysis. He enlists the help of the finance professor at the local university, Dr. Gwen French, to assist him. Together they estimate the following expected initial investment (a negative cash flow) and net positive cash flows for years 1 through 3 for each machine. Dave only needs one printer and estimates it will be worthless after three years of heavy use. Dave’s required rate of return for this project is 8 percent.

a. Calculate the payback period for each printer.

Expected Net Cash Flow Printer1 Printer 2 $(2,500) 1,500 1,300 800 Year $(2,000) (0 900 1,100 1,300 2

b. Calculate the net present value for each printer.

c. Calculate the internal rate of return for each printer.

d. Which printer do you think Dr. French will recommend? Why?

e. Suppose Dave’s required rate of return were 16 percent. Does the decision about which printer to purchase change?

Please show your work in excel so I can learn from it

Answer 1

8% 26 27 28 29 30 31 32 Rate Cash flows Present Value Commulative Cashflows Printer 2 Printer 2 -2500 1500 1300 800 PV factor 1.000 0.926 0.857 0.794 Printer 2 2500 1388.89 1114.54 635.07 638.50 Printer 1 Year 0 1 Printer 1 2000 900 1100 1300 Printer 1 2000 833.33 943.07 1031.98 808.39 900 2000 3300 1500 2800 3600 34 35 36 Net Present Value 1.8 Payback period z years

Since, Printer 1 would cover the exact amount till year 2, its payback period would be 2 years

For printer 2, it would earn 300 more than initial cost of the printer in 2nd year, hence payback period would be 1 + 300/1300 = 1.8 year

37 38 39 40 27% 28% Rate Internal rate of return PV factor @ 27% 1.000 0.787 0.620 0.488 PV factor @ 28% 1.000 0.781 0.610 0.477 Present Value@ 27% -2000.00 708.66 682.00 634.65 25.31 Present Value@ 28% -2000.00 703.13 671.39 619.89 5.60 Year Printer 1 -2000 900 1100 1300 0 42 43 45 46 47 48 49 50 51 52 53 54 Net Present Value (Interpolation) IRR 0.008 27.8% 23% 24% Rate Internal rate of return PV factor @ 23% 1.000 0.813 0.661 0.537 PV factor @ 24% 1.000 0.806 0.650 0.524 Present Value@ 23% -2500.00 1219.51 859.28 429.91 8.70 Present Value@ 24% -2500.00 1209.68 845.47 419.59 25.26 Year Printer 2 -2500 1500 1300 800 0 56 57 58 59 Net Present Value (Interpolation) IRR 0.003 23.3%

d. Printer 1 would be recommeded as both NPV and IRR of Printer 1 are higher than Printer 2

e. NPV @ 16% required rate of return

26 27 28 29 30 31 32 16% Rate Cash flows Present Value Printer2 2500 1500 1300 800 PV factor 1.000 0.862 0.743 0.641 Printer 1 2000 775.86 817.48 832.85 426.20 Printer 2 2500 1293.10 966.11 512.53 271.74 Year Printer 1 2000 900 1100 1300 0 34 Net Present Value

Since, the NPV of Printer 1 at 16% is higher than Printer 2, Printer 1 would be preferred.

Calculation

a and b

26 27 28 29 30 0 31 1 32 2 Rate 0.08 Cash flows Present Value Commulative Cashflows Printer 1 Printer 2 Printer 2 Printer 1 PV factor Printer 1 Printer 2 Year -1/(1+SD$26) (C30) -1/1+SD$26)A(C31) 1/(1+SD$26) (C32) -1/(1+SD$26)n(C33) 2000 900 1100 1300 2500 1500 1300 2000 -D31 SF31 -D32 SF32 D33 SF33 SUM(G30:G33) 2500 -E31 $F31 -E32 $F32 -E33 SF33 SUM(H30:H33) -D31 -131+D32 132+D33 -E31 -J31+E32 J32+E33 34 35 36 Net Present Value -1+(1000/1300) Payback period -C32

c.

37 38 39 40 41 0 42 1 43 2 44 3 45 46 47 48 49 50 51 52 53 0 54 1 55 2 56.3 57 58 59 0.27 0.28 Internal rate of return PV factor @ 27% -1/(1+5D$37)^(C41) -1/(1+5E$37)^(C41) -1/(1+5D$37)"(C42) -1/(1+$E$37)^(C42) -1/(1+SDS37)M(C43) 1/(1+SES37)(C43) -1/(1+SD$37)A(C44) 1/1+SE$37)A(C44) PV factor @ 28% Present Value@ 27% -SD41 E4:1 -SD42*E42 SD43*E43 -SD44*E44 SUM(G41:G44) -((G45/(G45-H45))/100) -27%+G46 Present Value@ 28% Year Printer 1 -2000 900 1100 1300 -$D41*F41 ŞD42 F42 -$D43 F43 -$D44*F44 SUM(H41:H44) Net Present Value (Interpolation) IRR Rate 0.23 0.24 Internal rate of return PV factor @ 23% -1/(1+5D$49)^(C53) -1/(1+SE$49)^(C53) -1/(1+SD$49)A(C54) 1/+SE$49)A(C54) -1/(1+SD$49)A(C55) 1/(1+SE$49)A(C55) -1/(1+SD$49)A(C56) 1/1+SE$49)A(C56) PV factor @ 24% Present Value@ 23% -SD53*E53 -SD54*E54 -SD55 E55 -$D56 E56 -SUM(G53:G56) -((G57/(G57-H57))/100) -23%+658 Present Value@ 24% Year Printer 2 -$D53 F53 -ŞD54 F54 -$D55*F55 -$D56 F56 -SUM(H53:H56) -2500 1500 1300 800 Net Present Value (Interpolation) IRR

e.

26 27 28 29 30 0 31 1 32 2 Rate 0.16 Cash flows Present Value Printer 1 Printer 1 Printer 2 PV factor Printer 2 Year -1/(1+SD$26)A(C30) -1/(1+SDS26)A(C31) -1/(1+$D$26)^(C32) -1/(1+SD$26)A(C33) 2000 900 1100 1300 2500 1500 1300 800 2000 -D31*$F31 -D32*ŚF32 -D33* $F33 SUM(G30:G33) 2500 -E31 $F31 -E32 SF32 -E33 $F33 SUM(H30:H33) 34 Net Present Value