Question

1. Solve the following questions in excel using the skills from class.

1. Lindner is considering an investment with an initial cost of $122,400 and salvage value of 63,600 with the following cash flows over the next 6 years:

Year	Cash flow
1	$28,750
2	$19,500
3	$44,100
4	$27,900
5	$15,850
6	$13,050

The discount rate is 14.8%. Find the NPV and IRR, what is the decision and what was the criteria for each rule?

2. You want to analyze the impact of a new project that will cause its cash flows to increase 12,000 over last year’s, and continue to grow at a constant rate of 7% per year for the foreseeable future. The discount rate is 13.5%. Analyze the project in three ways:
   1. Calculate the NPV and IRR of this project based on an initial investment cost of $97,000 and the change in cash flows each year, assuming the growth continues forever.
   2. Find the NPV and IRR of this project if the project only generates cash flows for 17 years.
   3. Use an embedded function in Excel to calculate the NPV of the project if the cash flows had zero growth, and the project only generates cash flows for 35 years.

UC Inc. has predicted unlevered free cash flows (FCF) of $19,800, $21,540, $25,300, and $28,900 for the next 4 years. Find the average growth rate using the predicted values. Then, assuming the growth rate persists forever at this rate, find the present value of the terminal value. Finally, find the total enterprise value. The discount rate is 18%.

Answer 1

(1): NPV = sum of all present values (PVs). PV = cash flow * PVIF and PVIF = 1/(1+r)^n where r is the interest rate/discount rate and n is the year of the cash flow.

Year	CF	Description	1+r	PVIF	PV
-	- 122,400	Initial investment	1.148	1	- 122,400.00
1	28,750	annual cash flow		0.8711	25,043.55
2	19,500	annual cash flow		0.7588	14,796.22
3	44,100	annual cash flow		0.6610	29,148.28
4	27,900	annual cash flow		0.5757	16,063.37
5	15,850	annual cash flow		0.5015	7,949.13
6	13,050	annual cash flow		0.4369	5,701.11
6	63,600	salvage value		0.4369	27,784.70
				NPV	4,086.37

Thus NPV = $4,086.37 (rounded to 2 decimal place).

IRR is the rate which makes the NPV as nil. I have found it using trial and error method.

Year	CF	Description	1+r	PVIF	PV
-	- 122,400	Initial investment	I	1	- 122,400.00
1	28,750	annual cash flow		0.8630	24,810.87
2	19,500	annual cash flow		0.7447	14,522.55
3	44,100	annual cash flow		0.6427	28,343.35
4	27,900	annual cash flow		0.5546	15,474.65
5	15,850	annual cash flow		0.4787	7,586.65
6	13,050	annual cash flow		0.4131	5,390.58
6	63,600	salvage value		0.4131	26,271.34
				NPV	0

Thus IRR = 1.158766 - 1 = 15.88% (rounded to 2 decimal place)

The decision is to accept the project. Criteria for NPV rule is that if NPV > 0 then project is acceptable. Here NPV is + 4086.37 and so the project is acceptable. IRR criteria is that if IRR > minimum required rate of return then the project is acceptable. Here IRR of 15.88% is > discount rate of 14.8% and hence the project is acceptable.