### 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**

**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.

**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**

**Initial Investment Cost:**- The machine costs
**$7,000**.

- The machine costs
**Useful Life of the Machine:**- The machine has a useful life of
**6 years**.

- The machine has a useful life of
**Additional Revenue Generated:**- The new machine increases production capacity, resulting in additional revenue of
**$4,000 per year**.

- The new machine increases production capacity, resulting in additional revenue of
**Cost Savings:**- The machine is more efficient, resulting in annual cost savings of
**$1,000**(e.g., reduced labor costs, energy savings).

- The machine is more efficient, resulting in annual cost savings of
**Maintenance Costs:**- Annual maintenance costs for the machine are
**$500**.

- Annual maintenance costs for the machine are
**Depreciation:**- The machine is depreciated on a straight-line basis over 6 years. The annual depreciation expense is
**$7,000 / 6 = $1,166.67**.

- The machine is depreciated on a straight-line basis over 6 years. The annual depreciation expense is
**Tax Rate:**- Assume a corporate tax rate of
**30%**. Depreciation will reduce taxable income, affecting cash flows.

- Assume a corporate tax rate of
**Residual Value:**- The machine has a residual (salvage) value of
**$500**at the end of its useful life (Year 6).

- The machine has a residual (salvage) value of

**Cash Flow Calculation**

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

**Gross Cash Flow:**- Gross Cash Flow = Additional Revenue + Cost Savings – Maintenance Costs

**Depreciation Expense:**- Depreciation reduces taxable income but does not affect actual cash flow.

**Tax Impact:**- Tax Impact = (Gross Cash Flow – Depreciation) * Tax Rate

**Net Cash Flow:**- Net Cash Flow = Gross Cash Flow – Tax Impact + Depreciation

**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)

**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

**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.

- The PI indicates the ratio of the total discounted cash flows (NPV + Initial Investment) to the initial investment. In our example, a PI of

### 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 investment is GOOD. NPV is 8687,60 and IRR is 63,41%.**

- The
**NPV**of**$8,687.60**indicates that the investment is good because it has a positive NPV, meaning the project’s returns exceed the required rate of 20%. - The
**IRR**of**63.41%**is much higher than the required rate of return of 20%, further confirming that the investment is very attractive.

These calculations show that the investment is highly profitable given the assumptions made.

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

Comments are closed.