In: Accounting
You are the CFO of ABC, LLC. You have been given data for two different projects. You can only choose one. Calculate the Net Present Value, Internal Rate of Return, Profitability Index and Pay Back Period for each of the projects. Make a recommendation based on your findings. You must do all calculations in Excel and use formulas. You must also explain why you chose the project. All of this should be on one tab of an excel spreadsheet.
Here is the project data. All values are in 000’s and should be presented as such in your work.
Project A Discount rate is 10%. The initial investment is $600. Cash flows for the following five years are; $100, $200, $250, $100, and $300. At the end of the fifth year, the project is over, and all related assets are liquidated for $200. All inflows and outflows occur at the end of the period.
Project A Discount rate is 7%. The initial investment is $350. Cash flows for the following five years are; $150, $250, $100, $100, and $200. At the end of the fifth year, the project is over, and all related assets are liquidated for $100. All inflows and outflows occur at the end of the period.
Project A.
Year cash flow pvfactor Npv cumulative cf
0 (600) 1 (600) (600)
1 100 0.909 90.9 (509.1)
2 200 0.826 165.2 (343.9)
3 250 0.751 187.75 (156.15)
4 100 0.683 68.3 (87.85)
5 300 0.620 186 98.15
5 200 0.620 124 222.15
222.15
Hence Npv of project 1 is 222.15
IRR of project 1 is calculated as trial & error method by assuming rate as 20 % & 25%
At 20% Npv is
Year Cf Factor Npv
0 ( 600) 1 (600)
1 100 .833 83.3
2 200 .694 138.8
3 250 .579 144.75
4 100 .482 48.2
5 500 .402 201
Positive npv 16.05
At 25% rate
Year CF factor NPV
0 (600) 1 (600)
1 100 .8 80 88
2 200 .64 128
3 250 .512 128
4 100 .410 41
5 500 .328 164
Negative NPv 59
Irr is the rate at which diffrence of present value of cash flow & initial investment is 0 hence
Formula of IRR=
Lower rate of NPv + (value of lower rate Npv/ lower rate npv - higher rate npv ) * difference of rates
Hence 20+(16.05/16.05+59)*5
=21.07
Payback period is period in which total amt shall recover so formula is
A +(B/c)
n the above formula,
A is the last period with a negative cumulative cash flow;
B is the absolute value of cumulative cash flow at the end of the period A;
C is the total cash flow during the period after A
Hence payback period is
4 +87.85/186
=4.47 year
Profitability index= present value of cash flows / intial investment
Hence 822.15/600
=1.370
Now project B at 7%
Formulas were same as above hence only calculation is present
Year cash flow pv factor Npv cumu.cf
0 350 1 (350) (350)
1 150 0.935 140.25 (209.75)
2 250 0.873 218.25 8.5
3 100 0.816 81.6 90
4 100 0.763 76.3 166.4
5 200 0.713 142.6 309
5 100 0.713 71.3 380.3
Npv of projct 2 is 380.3
iRR of project B is assumed at 40% & 45%
At 40% there is poitive NPV 2.8
At 45% there is negative npv of (25.3)
As calculated same as above by factors of 40% & 45%
Hence IRR =
40(2.8/28.1)*5
=40.5%
Payback period = same as above formula in project A
1+209.75/218.25
=1.96 year
Profitability index= use above formula
730.3/350
=2.087year
=
Observation
Project A projectB
Npv 222.15 380.3
IRR 21.07 40.5
Payback 4.47 1.96
Period
Profitability
Index 1.370 2.087
Hence we should accept project B because higher Npv .irr ia higher & amt of investment recover in only 1.96
& when investment amt is different we calculate profitability index hence in both project profitability index higher also in project B thats why
Project B is prefered