Question

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

Years	1	2	3	4	5
CF	Last 6 digits of your student ID	120,000	130,000	140,000	543978
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

First year CF is assumed to be 110000

NPV = PV of CF - Cash Outflow

NPV = 110000/(1.05)^1 + 120000/(1.07)^2 + 130000/(1.08)^3 + 140000/(1.1)^4 + 543978/(1.1)^5 - 5439783

NPV = -4693620.83

Year	CF	Rate	PVF	PV
0	-5439783		1	-5439783.00
1	110000	0.05	0.95	104761.90
2	120000	0.07	0.87	104812.65
3	130000	0.08	0.79	103198.19
4	140000	0.1	0.68	95621.88
5	543978	0.1	0.62	337767.54
Total				-4693620.83

NPV function cannot be used as excel takes the input of one rate.

Investment Recommendation: NO, since negative NPV

Year	CF
0	-5439783
1	110000
2	120000
3	130000
4	140000
5	543978
IRR	-33%