Question

Your company received an investment proposal which requires an initial investment of $5682678 now. The project will last for 5 years. You also have the following information about this project;

Years	1	2	3	4	5
CF	682678	120,000	130,000	140,000	568267
Discount Rate	5%	7%	8%	10%	10%

1. If you receive the above cash flows at the end of each year, calculate the NPV using both spreadsheet method and excel NPV function, and clearly mention if you will invest in the project or not based on your result of NPV.
2. Compute the IRR of the above project using the IRR function in excel.

Answer 1

A	B	C	D	E	F	G	H	I	J	K	L
2
3		Cash flow can be represented as follows:
4		Year	0	1	2	3	4	5
5		Cash Flow	($5,682,678)	682678	120000	130000	140000	568267
6		Discount rate		5%	7%	8%	10%	10%
7
8		NPV is the present value of all the future cashflows less initial investment.
9
10		Present value of cash flows can be calculated as follows:
11
12		Year	0	1	2	3	4	5
13		Free Cash Flow (FCF)	($5,682,678)	$682,678	$120,000	$130,000	$140,000	$568,267
14		MARR (i)		5%	7%	8%	10%	10%
15		(P/F,i,n) for each year for 1 year		0.95	0.93	0.93	0.91	0.91	=1/((1+I14)^(I12-H12))
16		PV factor		0.95	0.89	0.82	0.75	0.68	=PRODUCT(E15:I15)
17		Present Value of cash flows = FCF*(P/F,i,n)		$650,170	$106,809	$107,139	$104,891	$387,053	=I13*I16
18		Present value of future cash flows	$1,356,061.23	=SUM(E17:I17)
19
20		NPV for Project	=Present value fo future cash flows - Initial investment
21			($4,326,616.77)	=D18+D13
22
23		Hence NPV of the project is	($4,326,616.77)
24
25		Calculation of IRR of the project:
26		IRR is the rate at which NPV of the project will be zero i.e.
27		Given the following cash flow IRR can be calculated as below:
28
29		Year	0	1	2	3	4	5
30		Free Cash Flow	($5,682,678)	$682,678	$120,000	$130,000	$140,000	$568,267
31
32		IRR can be found using hit and trial method for above equation.
33
34		IRR can also be found using IRR function in excel as follows:
35		Year	0	1	2	3	4	5
36		Free Cash Flow	($5,682,678)	$682,678	$120,000	$130,000	$140,000	$568,267
37		IRR	-30.38%	=IRR(D36:I36)
38
39		Hence IRR of the project is	-30.38%
40

Formula sheet