How to calculate the value and payback period of business investment?

It is essential to calculate the value and payback period of business investment.

When a business invests in a new piece of machinery, this is known as a capital expenditure. It is vital to every business to ensure that their capital expenditures, especially if they represent a major investment, create value instead of destroying value. The systematic process of identifying, analyzing, and selecting the best investment is called capital budgeting.

Many companies do not know how to perform an investment appraisal or underestimate its importance. A business could be investing in PPE that consumes cash and valuable time but that is ultimately unprofitable. Investments in long-term assets tend to be expensive, inflexible, and affect multiple accounting periods. In this article, you will learn how to perform some powerful investment appraisal methods for long-term assets in Excel. The major appraisal methods covered are:

  • Payback period and Discounted Payback period
  • Internal Rate of Return (IRR)
  • Net Present Value (NPV)

Unfortunately, the payback, IRR, and NPV methods do not always agree and could recommend you a different course of action, especially if there are multiple projects involved. The goal is to teach you how to make the best investment decision for your company given the company’s limited resources.

Capital budgeting investment appraisal methods: Determining the value and payback period of business investment

1. Payback period and discounted payback period

1.1. Payback period

The term payback period is self-explanatory. It is the time required for the business to recover the money it has invested in an asset or a project. The company determines the timeframe within which the initial investment needs to be recovered, such as a specified number of months or years.

For example, a company is considering purchasing an industrial robot that can be bought for a consideration of $500,000. For the investment to meet the company’s internal target, the initial investments should be repaid within 4 years. The expected cash flows of the asset are:

Payback period represents the time required to recover the money the company has invested

According to the company’s cash-flow forecasts, it expects to generate $75,000 after-tax cash flow in Year 1 from the industrial robot. In other words, $425,000 of the initial investment still needs to be recovered at the end of Year 1. The initial investment of $500,000 will be fully recovered between Year 3 and Year 4 when the cumulative cash flow turns positive. More precisely, the Company only needs $25,000 from the Year 4 cash flow (or 6.25% of the Year 4 cash flow). Since the investment is fully recovered within 4 years, the company undertakes this investment.

The chief advantage of the payback period is its simplicity. However, the payback period has two major shortcomings.

  • The method ignores all the cash inflows after the set payback cut-off target. This implies, in our example, that the cash flows of year 5 and beyond are completely disregarded. Sometimes, the company is excessively focused on projects with a short payback period at the cost of more profitable projects with longer payback periods.
  • The payback period does not consider the time value of money. In simple terms, the value of a $ today is not the same as the value of $ in the future.

1.2. Discounted payback period

The basic payback period is heavily criticized for ignoring the concept of “the time value of money”. The discounted payback period tackles this flaw by discounting future cash flows to the present time. It uses a discount rate to calculate the value of the future cash flows as of the present.

But what exactly is a discount rate? The discount rate can represent several things, such as the return required by investors, the overall riskiness of the cash flows, or the cost of capital. Selecting an appropriate discount rate is situational dependent on the size of the company, its track record of profitability, the riskiness of product/project, the cost of borrowing, etc. In essence, the higher the discount rate, the lower the value of the cash flows from future periods.

To discount the cash inflows from our previous example, we need to do the following steps:

  1. Determine the discount rate: In our example, we set the discount rate at 15%. However, for young companies with a limited track records and startups, the discount rate or hurdle rate can exceed 25%.
  2. Once the discount rate has been determined, we need to look up the present value factor for year 1 to year 5 from the Present Value factor for a Single Future Amount table. Given a discount rate of 15%, the discount factor for year 1 is 0.8696, for year 2 is 0.7561, etc.
  3. Multiply the cash flow of each year with the corresponding present value factor.
  4. Calculate the cumulative cash flows to determine the payback period. Since the cash flows are not adjusted for the time value of money, the result is the discounted payback period.

When we discount the cash inflows from our previous example based on a discount rate of 15%, we get the following results.

It is clear from the above table that it will take additional time to recover our initial investment of $500,000. At the end-of-year 3, we still need to recover $157,000 instead of $25,000 because of the time value of money.

The key improvement of the discounted payback period relative to the basic payback period is that it accounts for the time value of money. However, the method still does not consider the cash inflows after the set payback cut-off target.

2. Internal Rate of Return (IRR)

The internal rate of return is an appraisal method that measures the investment’s return in % terms. The company needs to set a target rate of return, often equal to the company’s cost of capital, for its investment, e.g. 15%.

Apply the following rules of thumb:

  • If IRR > desired rate of return, the project should be undertaken.
  • If IRR < desired rate of return, the project should be rejected because it doesn’t meet the company’s required rate of return.

Excel contains an IRR function. The IRR of our fictitious example is 32.6%. The internal rate of return can be derived as follows:

  1. Select a cell and type =IRR(select relevant range). In my case, the cash flows are in cell C13 to H13. So, the formula should look like =IRR(C13:H13). For this function to work, you need to have a negative cash flow in year 0.
  2. Press enter. You should now have successfully calculated the IRR of 32.6%.

While the Internal Rate of Return (IRR) is a relatively straightforward method that considers the time value of money and all cash flows, it can cause unsound capital budgeting decisions because of several factors that affect its usability:

  • Deception risk: If the IRR is used as the sole decision criterion when changes in the direction of the cash flows occur, it can deceive the decision-maker in selecting the wrong project.
  • Capital constraints: In case the company is considering several investment opportunities but lacks the resources to invest in all of them, focusing on the IRR can lead to a suboptimal decision. The project with the highest IRR does not always create the most shareholder value.

However, the Internal Rate of Return and the Net Present Value method (see below) lead to the same outcome in terms of accepting or rejecting an investment when dealing with projects that are independent of each other.

3. Net Present Value (NPV)

The Net Present Value (NPV) is the sum of the present values of all the future cash flows associated with an investment minus the initial investment. It considers all the cash flows and discounts them back to the present. While the IRR measures investment returns in % terms, the NPV method measures the investment returns in $ terms.

The principal benefit of using the NPV method instead of the IRR method is that the NPV method never deceives you. It is the king of all capital budgeting investment appraisal methods.

To NPV of our investment can be derived as follows:

  1. Select a cell and type =NPV(C20,D19:H19). In my case, the discount rate is reflected in cell C20. Next, you need to select the range of cash flows from year 1 to year 5.
  2. Last, we need to add the initial investment of minus $500,000 in cell C19. So, the formula should look like =C19+NPV(C20,D19:H19).
  3. Press enter. This should result in a positive NPV of $320,000.

In the background, Excel will apply the discount factor to the initial cash flows to arrive at the discounted cash flow for year 1 to year 5. We have shown this in the above table for clarity purposes.

Apply the following rules to make sound managerial decisions that maximize shareholder wealth:

  • A positive NPV shows that the project should be undertaken because it generates value.
  • A negative NPV shows that the project should not be undertaken because it is value destructive.
  • An NPV of 0 indicates that the project doesn’t create nor destroys value. In other words, the cash inflows are equal to the cash outflows. This is exactly the point where the IRR is located. To illustrate this point, we have changed the discount rate from 15% to 32.6% in line with the IRR that we calculated previously. The NPV becomes 0 (ignoring rounding error) when we apply a discount rate of 32.6%.

Executive Summary

Companies invest in new equipment or long-term assets all the time. Since capital expenditures can represent major investments in terms of financial resources and time, it is vital that they generate value.

In this article, we taught you how to calculate the value and payback period of business investment. We covered the payback period and discounted payback period, the Internal Rate of Return (IRR), and the Net Present Value (NPV) method. While every method has its merits and disadvantages, the NPV method is the best capital budgeting appraisal method. Applying the NPV method results in sound management decisions that maximize shareholder wealth.