J. Morgan of SparkPlug Inc. has been approached to take over a production facili
ID: 2484963 • Letter: J
Question
J. Morgan of SparkPlug Inc. has been approached to take over a production facility from B.R. Machine Company. The acquisition will cost $1,620,000, and the after-tax net cash inflow will be $248,000 per year for 12 years. SparkPlug currently uses 8% for its after-tax cost of capital. Tom Morgan, production manager, is very much in favor of the investment. He argues that the total after-tax net cash inflow is more than the cost of the investment, even if the demand for the product is somewhat uncertain. “The project will pay for itself even if the demand is only half the projected level.” Cindy Morgan (corporate controller) believes that the cost of capital should be 11% because of the declining demand for SparkPlug products. (Use Table 1 and Table 2.) Required: 1. Assume the project's after-tax cost of capital is 8%. Calculate the NPV of this project. Should Morgan accept the project? (Negative amounts should be indicated by a minus sign. Round your answer to the nearest whole dollar amount.) 2. Calculate the NPV of this project, if Cindy Morgan is correct and uses 11%. Should Morgan accept the project? (Negative amounts should be indicated by a minus sign. Round your answer to the nearest whole dollar amount.) 3(a). Use the built-in function in Excel to estimate the project’s IRR. (Round your answer to 2 decimal places.) 3. Do a sensitivity analysis by using GOAL SEEK to determine, given estimated cash inflows, the original investment outlay that would result in an IRR of 11%. (Round your answer to nearest whole dollar amount.)
Explanation / Answer
The following assumptions has to be taken
The total initial investment is $1,620,000
The cost of capital is 8%
The after tax cash inflow taken are $248,000
The number of years of the project is 12 years
The calculation of NPV is given below; NPV is the difference between present values of cash inflows with the present value of cash outflows
NPV At 8% cost of capital
year
equipment cost
after tax cash inflow
present value factor at 8%
present value of cash inflow after tax
0
(1,620,000)
(1,620,000)
1
(1,620,000)
1
248000
0.92593
229,630
2
248000
0.85734
212,620
3
248000
0.79383
196,870
4
248000
0.73503
182,287
5
248000
0.68058
168,785
6
248000
0.63017
156,282
7
248000
0.58349
144,706
8
248000
0.54027
133,987
9
248000
0.50025
124,062
10
248000
0.46319
114,872
11
248000
0.42888
106,363
12
248000
0.39711
98,484
Net present value
248,947
If the cash flow is taken as $124,000 50% of $248,000 and rest assumptions being the same , the NPV will be
Calculated is given below
NPV At 8% cost of capital
year
equipment cost
after tax cash inflow
present value factor at 8%
present value of cash inflow after tax
0
(1,620,000)
(1,620,000)
1
(1,620,000)
1
124000
0.92593
114,815
2
124000
0.85734
106,310
3
124000
0.79383
98,435
4
124000
0.73503
91,144
5
124000
0.68058
84,392
6
124000
0.63017
78,141
7
124000
0.58349
72,353
8
124000
0.54027
66,993
9
124000
0.50025
62,031
10
124000
0.46319
57,436
11
124000
0.42888
53,181
12
124000
0.39711
49,242
Net present value
(685,526)
2) The following assumptions has to be taken
The total initial investment is $1,620,000
The cost of capital is 11%
The after tax cash inflow taken are$248,000
The number of years of the project is 12 years
The calculation of NPV is given below; NPV is the difference between present values of cash inflows with the present value of cash outflows
NPV At 11% cost of capital
year
equipment cost
after tax cash inflow
present value factor at 11%
present value of cash inflow after tax
0
(1,620,000)
(1,620,000)
1
(1,620,000)
1
248000
0.90090
223,423
2
248000
0.81162
201,282
3
248000
0.73119
181,335
4
248000
0.65873
163,365
5
248000
0.59345
147,176
6
248000
0.53464
132,591
7
248000
0.48166
119,451
8
248000
0.43393
107,614
9
248000
0.39092
96,949
10
248000
0.35218
87,342
11
248000
0.31728
78,686
12
248000
0.28584
70,889
Net present value
(9,896)
If the cash flow is taken as $124,000 , 50% of $248,000 and rest assumptions being the same , the NPV will be
Calculated is given below
NPV At 11% cost of capital
year
equipment cost
after tax cash inflow
present value factor at 11%
present value of cash inflow after tax
0
(1,620,000)
(1,620,000)
1
(1,620,000)
1
124000
0.90090
111,712
2
124000
0.81162
100,641
3
124000
0.73119
90,668
4
124000
0.65873
81,683
5
124000
0.59345
73,588
6
124000
0.53464
66,295
7
124000
0.48166
59,726
8
124000
0.43393
53,807
9
124000
0.39092
48,475
10
124000
0.35218
43,671
11
124000
0.31728
39,343
12
124000
0.28584
35,444
Net present value
(814,948)
3 ) the calculation in irr in excel is given below
NPV At 8% cost of capital
year
equipment cost
after tax cash inflow
present value factor at 8%
present value of cash inflow after tax
0
(1,620,000)
(1,620,000)
1
(1,620,000)
1
248000
0.92593
229,630
2
248000
0.85734
212,620
3
248000
0.79383
196,870
4
248000
0.73503
182,287
5
248000
0.68058
168,785
6
248000
0.63017
156,282
7
248000
0.58349
144,706
8
248000
0.54027
133,987
9
248000
0.50025
124,062
10
248000
0.46319
114,872
11
248000
0.42888
106,363
12
248000
0.39711
98,484
Net present value
248,947
internal rate of return
10.87%
4 ) with the initial investment of $1,610,105 at cash flow of $248,000 for 12 years IRR will be 11%
NPV At 8% cost of capital
year
equipment cost
after tax cash inflow
present value factor at 8%
present value of cash inflow after tax
0
(1,620,000)
(1,620,000)
1
(1,620,000)
1
248000
0.92593
229,630
2
248000
0.85734
212,620
3
248000
0.79383
196,870
4
248000
0.73503
182,287
5
248000
0.68058
168,785
6
248000
0.63017
156,282
7
248000
0.58349
144,706
8
248000
0.54027
133,987
9
248000
0.50025
124,062
10
248000
0.46319
114,872
11
248000
0.42888
106,363
12
248000
0.39711
98,484
Net present value
248,947
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.