Question

Cash-flow Problem Set You are the owner of a large data-services firm and are deciding on the purchase of a new hardware cooling system that you expect will yield $233,300 in cost savings per year for the next 15 years. The installation of this cooling system will cost $3,000,000. Additionally, O&M expenditures for the colling system are expected to be $2,120 per year. 1. At face value, does this system seem profitable? By how much? 2. Assume that your company uses a discount rate of 6%. a. What is the Net Present Value (NPV) of this project? b. How does the NPV of this project change as you assume a higher or lower discount rate? Why? c. What is the IRR ROI of this project? d. How much should the yearly cost-savings be in order to break even? i. (hint) use goal-seek/what-if analysis 3. Suppose that you decide to finance the purchase of this system through a loan from the bank. The bank is willing to loan this money over an 8 year term at an interest rate of 4% per year. a. Using a 70/30 debt-to-equity ratio, what is the NPV of this project? i. (hint) calculate the yearly payment using excel function "PMT" b. How does the NPV of this project change if a larger portion is financed through equity (e.g. debt-to-equity ratio of 60/40)? Why?

use excel

Answer 1

1. Profit at Face Value
Cost Saving p.a	$2,33,300
Additional O&M expense p.a	-$2,120
Net Cash saving p.a	$2,31,180
*No. of years	15
Total Cash Saving	$34,67,700
-Cost of cooling system	-$30,00,000
Profit at Face value	$4,67,700
Thus, the system seems profitable at face value.

2 (a) NPV
Net Cash saving p.a	$2,31,180
PVAF @ 6%	9.7122
PV of Total Cash Saving	$22,45,266
-PV of Cost of cooling system	-$30,00,000
NPV	-$7,54,734

Year (n)	PVF @ 6% (1/1.06^n)
1	0.9434
2	0.8900
3	0.8396
4	0.7921
5	0.7473
6	0.7050
7	0.6651
8	0.6274
9	0.5919
10	0.5584
11	0.5268
12	0.4970
13	0.4688
14	0.4423
15	0.4173
PVAF	9.7122

2 (b)
As we assume a Higher discount rate, NPV will get lower as required rate of return is increasing but cashflow is same.
Eg: Taking discount rate as 7%, NPV will be -$894436
As we assume a Lower discount rate, NPV will get higher as required rate of return is decreasing but cashflow is same.
Eg: Taking discount rate as 5%, NPV will be -$600421

2 (c ) IRR

1.87%

(using IRR Function in Excel.)

2 (d) Yearly cost-saving to break-even
Net Cash saving p.a	$3,08,890
PVAF @ 6%	9.7122
PV of Total Cash Saving	$30,00,000
-PV of Cost of cooling system	-$30,00,000
NPV	$0
(Using Goal-seek function in Excel.)

3 (a) Debt-Equity= 70:30
Cost of cooling system	$30,00,000
Debt @ 70%	$21,00,000
Equity @ 30%	$9,00,000
Yearly Payment for 8 years	-$3,11,908	=PMT(4%,8,2100000,0,0)
Years	Cashflows	PVAF @ 6%	PV
0 (Equity)	-$9,00,000	1	-$9,00,000
1-8 (Loan Repayment)	-$3,11,908	6.2098	-$19,36,886
1-15 (Net Cash Saving)	$2,31,180	9.7122	$22,45,266
NPV			-$5,91,620

Year (n)	PVF @ 6% (1/1.06^n)	PVAF
1	0.9434
2	0.8900
3	0.8396
4	0.7921
5	0.7473
6	0.7050
7	0.6651
8	0.6274	6.2098
9	0.5919
10	0.5584
11	0.5268
12	0.4970
13	0.4688
14	0.4423
15	0.4173	9.7122

3 (b) Debt-Equity= 60:40
If a larger portion is financed through equity, NPV will REDUCE as Cash Outflow at 0 year will increase, leading to decrease in NPV.
Cost of cooling system	$30,00,000
Debt @ 60%	$18,00,000
Equity @ 40%	$12,00,000
Yearly Payment for 8 years	-$2,67,350	=PMT(4%,8,1800000,0,0)
Years	Cashflows	PVAF @ 6%	PV
0 (Equity)	-$12,00,000	1	-$12,00,000
1-8 (Loan Repayment)	-$2,67,350	6.2098	-$16,60,190
1-15 (Net Cash Saving)	$2,31,180	9.7122	$22,45,266
NPV			-$6,14,924