An Exercise on NPV Profile Crossings

Order Details;

Higgs Electronics, Inc.
An Exercise on NPV Profile Crossings
Higgs Electronics, Inc. is considering a 5-year investment project. At t = 0 the project requires a
cash outflow of $8,000 for machinery. The machinery will be depreciated straight-line from t = 1
to t = 5, with no salvage value. The working capital (WC) required in year t is equal to 17% of
the change in sales between years t and t + 1 (t = 0, 1, 2, , 5).
Annual sales are expected to be $10,000; $15,000; $17,000; $17,000; and $13,000 in years one
through five of the project, respectively. The cost of goods sold (COGS) will be 60% of sales,
and selling, general, and administrative expenses (SG&A) will be 18% of sales. The corporate
tax rate is 35%, and it is symmetric so that if, say, net income is $100 then taxes would be $35.
Assume that the firms bank, AmeriCanes, is willing to provide Higgs with a 5-year loan with an
annual interest rate of 12%, regardless of the amount of the loan. The loan will be repaid in five
annual installments. Assume that you are considering two types of loans:
a) A constant payment (CP) loan. (Recall that in a CP loan, the total annual payment
[principal + interest] is the same for each year.)
b) A constant amortization (CA) loan. (Recall that in a CA loan, the principal portion of
the payment is the same for each year.)
Consider each type of loan separately. For each of the two types of loan, construct a SINGLE
graph containing the NPV profiles corresponding to the following loan scenarios: a) No loan; b)
25% loan; c) 50% loan; and d) 75% loan. The horizontal scale of the graph must be from 0% to
30%, and the vertical scale must be from $1,000 to $6,000. Verify that these four NPV profiles
cross at a unique specific discount rate, and state the exact numerical value of that crossing
rate as a percent with two decimals; e.g., 24.03%. IMPORTANT: In addition to the graph,
provide in your report four tables, one for each of the four debt scenarios, detailing how you
obtained the projects net cash flows in each of those scenarios. Include as part of each of the
tables the detailed loan amortization.NOTE: The NPV function in Excel is erroneous, but you can easily fix it. In particular, be
careful not to include the investment at t = 0 in the function. To illustrate, if the cash flows for t =
0, t = 1, , t = 5 are I, C1, , C5, then to correctly find the NPV you must input the data as
follows: NPV(rate%, C1, , CF5) I. (You can be more efficient than this by specifying a range
for the cells containing the cash flows.) If you input the data as NPV(rate%, I, CF1, , CF5)
you will get an incorrect NPV value!