Explanation of an Investment Decision

A company pays 7000 for a new machine, plans a 20% annual return on the investment, and expects these annual cash flows over the next six years. Is this investment good or not? Explain why is good or bad.

To evaluate whether this investment is good or not, we can calculate the Net Present Value (NPV) and the Internal Rate of Return (IRR) of the investment.

These metrics will help us understand if the investment meets or exceeds the company’s required rate of return of 20% annually.

Explanation of the Investment Decision

1. Net Present Value (NPV):

NPV calculates the present value of cash flows, discounted back at the required rate of return (20% in this case). The formula for NPV is:

If the NPV is positive, the investment is considered good because it is expected to generate more cash flows than the cost of capital.

2. Internal Rate of Return (IRR):

IRR is the discount rate that makes the NPV of all cash flows equal to zero. If the IRR is higher than the required rate of return (20%), the investment is considered good.

Decision Criteria:

• Good Investment: NPV > 0 or IRR > 20%
• Bad Investment: NPV < 0 or IRR < 20%

 

Let’s create a realistic scenario for evaluating the investment in the new machine by making specific assumptions. We’ll consider factors such as additional revenue generated, cost savings, maintenance costs, and depreciation.

Assumptions

1. Initial Investment Cost:
   • The machine costs $7,000.
2. Useful Life of the Machine:
   • The machine has a useful life of 6 years.
3. Additional Revenue Generated:
   • The new machine increases production capacity, resulting in additional revenue of $4,000 per year.
4. Cost Savings:
   • The machine is more efficient, resulting in annual cost savings of $1,000 (e.g., reduced labor costs, energy savings).
5. Maintenance Costs:
   • Annual maintenance costs for the machine are $500.
6. Depreciation:
   • The machine is depreciated on a straight-line basis over 6 years. The annual depreciation expense is $7,000 / 6 = $1,166.67.
7. Tax Rate:
   • Assume a corporate tax rate of 30%. Depreciation will reduce taxable income, affecting cash flows.
8. Residual Value:
   • The machine has a residual (salvage) value of $500 at the end of its useful life (Year 6).

Cash Flow Calculation

Let’s calculate the annual cash flows based on these assumptions:

1. Gross Cash Flow:
   • Gross Cash Flow = Additional Revenue + Cost Savings – Maintenance Costs
2. Depreciation Expense:
   • Depreciation reduces taxable income but does not affect actual cash flow.
3. Tax Impact:
   • Tax Impact = (Gross Cash Flow – Depreciation) * Tax Rate
4. Net Cash Flow:
   • Net Cash Flow = Gross Cash Flow – Tax Impact + Depreciation
5. Final Year Adjustment:
   • In Year 6, add the residual value of the machine to the net cash flow.

 

Now, let’s compute the cash flows for each year based on these assumptions:

Gross Cash Flow = 4,000 (Additional Revenue) + 1,000 (Cost Savings) – 500 (Maintenance Cost) = 4,500

Depreciation Expense = 1,166.67

Tax Impact = (4,500−1,166.67) × 30% = 999.999

Net Cash Flow = 4,500 − 999.999 + 1,166.67 = 4,666.67

 

Final Cash Flow for Year 6:

In Year 6, we add the residual value of the machine:

Net Cash Flow in Year 6 = 4,666.67 + 500 = 5,166.67

 

Cash Flows for Each Year

Year	Gross Cash Flow	Depreciation	Tax Impact	Net Cash Flow
1	4,500	1,166.67	1,000	4,667
2	4,500	1,166.67	1,000	4,667
3	4,500	1,166.67	1,000	4,667
4	4,500	1,166.67	1,000	4,667
5	4,500	1,166.67	1,000	4,667
6	4,500	1,166.67	1,000	5,167

Calculate NPV

Now, sum up all the present values and subtract the initial investment to get the NPV:

NPV=(3889.17+3247.92+2706.60+2246.99+1872.49+1730.42) − 7000

NPV=18693.59−7000=8687.60

The NPV is $8,687.60.

Interpretation

• The NPV of $8,687.60 is 124.11% of the initial investment of $7,000.
• This percentage indicates that the investment’s return exceeds the initial cost by 124.11%, which is quite substantial and confirms that the project is highly profitable.

The NPV expressed as a percentage of the initial investment is 124.11%, indicating that the investment generates a return far greater than its initial cost, making it an attractive investment option.

The Profitability Index (PI), or NPV as a percentage of the initial investment, represents the total return over the entire period of the investment, not per year.

Explanation of the Profitability Index (PI)

1. Profitability Index (PI):
   • The PI is a measure of how much value an investment generates relative to its initial cost.
   • It is calculated as:

PI = (NPV + Initial Investment) / Initial Investment =>

2.24 = (8687.60 + 7000​) / 7000

Alternatively, when considering NPV as a percentage of the initial investment:

NPV Percentage = (NPV / Initial Investment) ×100 =>

124.11% = (8687.60 / 7000) ×100

2. What Does PI Represent?
   • The PI indicates the ratio of the total discounted cash flows (NPV + Initial Investment) to the initial investment. In our example, a PI of 224.11% (or an NPV percentage of 124.11%) tells us that for every dollar invested, the project returns 2.24 dollars over the entire 6-year period.
   • The 124.11% represents the excess return (over and above the initial investment) generated by the project during its lifetime.

Key Points:

• Whole Period vs. Annual Return:
  • The NPV percentage (or PI) is a total measure over the entire period of the investment (6 years in this case). It is not an annualized figure.
  • It shows the total value created by the investment over its complete duration, not per year.

Calculate IRR

The IRR is the rate that makes the NPV of all cash flows equal to zero. In Excel, this is done using the IRR function, but you can also calculate it iteratively or use a financial calculator. Here’s how you would think of the calculation:

The 63.41% IRR is applied to all the future cash flows and represents the return that equates the NPV to zero. It reflects how much the investment returns annually, considering all the projected cash flows.

Now sum these : -7000 + 2855.20 + 1747.92 + 1069.61 + 654.54 + 400.45 + 276.28 = 4

Since this NPV is very close to zero, the IRR is approximately 63.41%.

In Excel, you would use the formula:

IRR=IRR({−7000,4667,4667,4667,4667,4667,5167})

Conclusion

 

The following VBA macro uses these realistic assumptions for the annual cash flows based on potential revenue, cost savings, maintenance costs, and tax impact. By running this macro in Excel, you can determine whether the investment is good (if NPV > 0 or IRR > 20%) or bad, given the company’s required return of 20%.

VBA Macro for this Scenario

Now, let’s create the VBA macro to calculate NPV and IRR based on this realistic scenario:

 

Sub EvaluateInvestmentWithRealisticScenario()

‘ Declare variables

Dim InitialInvestment As Double

Dim RequiredRateOfReturn As Double

Dim CashFlows(1 To 6) As Double

Dim NPV As Double

Dim IRR As Double

Dim i As Integer

Dim DiscountFactor As Double

Dim SumNPV As Double

 

‘ Initialize variables

InitialInvestment = 7000

RequiredRateOfReturn = 0.2 ‘ 20% required rate of return

 

‘ Annual net cash flows over 6 years (based on assumptions)

CashFlows(1) = 4667

CashFlows(2) = 4667

CashFlows(3) = 4667

CashFlows(4) = 4667

CashFlows(5) = 4667

CashFlows(6) = 5167 ‘ Year 6 includes the residual value of the machine

 

‘ Calculate NPV

SumNPV = 0

For i = 1 To 6

DiscountFactor = 1 / (1 + RequiredRateOfReturn) ^ i

SumNPV = SumNPV + (CashFlows(i) * DiscountFactor)

Next i

NPV = SumNPV – InitialInvestment

 

‘ Calculate IRR using Excel’s IRR function

IRR = WorksheetFunction.IRR(Array(-InitialInvestment, CashFlows(1), CashFlows(2), CashFlows(3), CashFlows(4), CashFlows(5), CashFlows(6))) * 100

 

‘ Display results

If NPV > 0 Then

MsgBox “The investment is GOOD. NPV = ” & Format(NPV, “0.00”) & ” and IRR = ” & Format(IRR, “0.00”) & “%”

Else

MsgBox “The investment is BAD. NPV = ” & Format(NPV, “0.00”) & ” and IRR = ” & Format(IRR, “0.00”) & “%”

End If

 

End Sub