Question

For each project, calculate the NPV, IRR, profitability index (PI) and the payback period. For each capital budgeting decision tool, indicate if the project should be accepted or rejected, assuming that each project is independent of the others. Important Note: The venture capital folks have a firm maximum payback period of four years. Risk free rate = 1.10%, MRP = 9.5%, Required return = 14.40% Yes excel is fine

Expected cash flows for the four potential projects that Avalon is considering as shown below:                       

Year	Project A	Project B	Project C	Project D
0	-$7,250,000	-$8,500,000	-$6,500,000	-$4,500,000
1	$2,000,000	$1,000,000	$2,000,000	$2,000,000
2	$2,500,000	$1,500,000	$2,000,000	$2,000,000
3	$3,000,000	$2,000,000	$2,500,000	$1,000,000
4	$1,000,000	$2,000,000	$2,500,000	$1,000,000
5	$1,000,000	$1,600,000	$2,000,000	$500,000
6	$1,000,000	$1,600,000		$500,000
7		$1,500,000
8		$1,500,000
9		$1,500,000
10		$1,500,000

I have provided a suggested template for your final answers. Below the grid (and/or next page) is where you should show all your required backup calculations. If you are working this in Excel, feel free to submit your Excel sheet, where the equations in the cells will provide the required backup. Be sure to clearly indicate the required rate of return for each project (you calculated this in Problem 3).    

		Year	Project A	Project B	Project C	Project D
Points		Req. Return (use 2 decimals xx.xx%)
4	4a	NPV (to nearest $1)
1	4b	NPV accept/reject
2	5a	IRR (xx.xx%)
1	5b	IRR accept/reject
2	6a	PI (show 2 decimals)
1	6b	PI accept/reject
2	7a	Payback Period (x.x years)
1	7b	Payback accept/reject

Answer 1

NPV is the difference between present value of cash flows and initial investment. If NPV is positive of a project then it will be accepted. If NPV is negative then it will be rejected.

IRR is the internal rate of return at which present value of cash flows are equal to initial investment. If a project's IRR is greater than its required return then it will be accepted or it will be rejected if its IRR is lower than required return.

PI or the profitability index is the ratio of benefits (present value of cash flows) and cost (initial investment). If a project has PI of greater than 1 then it will be accepted and if it is lower than rejected.

PI = present value of cash flows/initial investment

Payback period is the time taken by a project's cash flows to recover its initial investment. if project's payback period is lower than required payback period then it will be accepted. otherwise it will be rejected.

In the below table, we have cumulative cash flows of project A in column C. we can see in column C that project A recovers $4,500,000 in 2 years. remaining initial investment of $7,250,000 - $4,500,000 = $2,750,000‬ it will recover in year 3 from year 3 cash flow of $3,000,000. so payback period of project A is 2 years + $2,750,000/$3,000,000 = 2 years + 0.9 or 2.9 years.

Year	Project A	Cumu. Cash flow	Project B	Cumu. Cash flow	Project C	Cumu. Cash flow	Project D	Cumu. Cash flow
0	-$7,250,000		-$8,500,000		-$6,500,000		-$4,500,000
1	$2,000,000	$2,000,000	$1,000,000	$1,000,000	$2,000,000	$2,000,000	$2,000,000	$2,000,000
2	$2,500,000	$4,500,000	$1,500,000	$2,500,000	$2,000,000	$4,000,000	$2,000,000	$4,000,000
3	$3,000,000	$7,500,000	$2,000,000	$4,500,000	$2,500,000	$6,500,000	$1,000,000	$5,000,000
4	$1,000,000	$8,500,000	$2,000,000	$6,500,000	$2,500,000	$9,000,000	$1,000,000	$6,000,000
5	$1,000,000	$9,500,000	$1,600,000	$8,100,000	$2,000,000	$11,000,000	$500,000	$6,500,000
6	$1,000,000	$10,500,000	$1,600,000	$9,700,000			$500,000	$7,000,000
7			$1,500,000	$11,200,000
8			$1,500,000	$12,700,000
9			$1,500,000	$14,200,000
10			$1,500,000	$15,700,000
Req. Return	14.40%		14.40%		14.40%		14.40%
NPV (to nearest $1)	-$47,453.00		-$511,987.42		$926,545.19		$506,434.73
NPV accept/reject	Reject		Reject		Accept		Accept
IRR (xx.xx%)	14.12%		12.86%		20.08%		19.82%
IRR accept/reject	Reject		Reject		Accept		Accept
PI (show 2 decimals)	0.99		0.94		1.14		1.11
PI accept/reject	Reject		Reject		Accept		Accept
Payback Period (x.x years)	2.9		5.3		3.0		2.5
Payback accept/reject	Accept		Reject		Accept		Accept

Calculations