Get startedGet started for free

Internal rate of return and payback period

1. Internal rate of return and payback period

Welcome back to Financial Analysis in Power BI. Previously we learned about the net present value and the profitability index. We'll cover two more important metrics related to capital budgeting; internal rate of return and payback period.

2. Internal rate of return (IRR)

As we've previously explored, discount rates play an important role in the time value of money and net present value. The NPV Profile shows that NPV decreases as the discount rate increases: they have an inverse relationship. The discount rate at which NPV equals zero is the internal rate of return or IRR. IRR is the rate of return for the project's cash flows, which can be used to compare against other investments.

3. Internal rate of return (IRR)

IRR is an iterative function, which means that it's a guess-and-check problem and it is not easy to figure out by hand. Luckily, we have computers that can run various discount rates until NPV equals zero. In Power BI, we use the function XIRR, and all it really needs to know are the cash flows and their dates. It's really that simple.

4. Testing the IRR

The best way to illustrate this is to solve for NPV using the IRR as the discount rate. Let's say that we ran this cash flow through XIRR, and it gave us 21 point 86%. What happens if we plug this into our NPV formula?

5. Testing the IRR

Let's set up the present value formulas for each cash flow by dividing the cash flow by one plus the discount rate to the power of t number of periods.

6. Testing the IRR

Then we begin to solve for each equation.

7. Testing the IRR

Finally, we find that the sum of all these present value cash flows equal zero. Exactly what we expected. If the discount rate is equal to the return on the investment, NPV will equal zero. This makes sense logically. Since discount rates are usually the cost of the project, if the cost of the project is equal to the return of the project, then there would be no real financial gain.

8. Making decisions with IRR

When companies invest in projects, they usually have a target return they're trying to beat, called a hurdle rate. If the IRR is greater than the hurdle rate, the project should be undertaken. If the IRR is less than the hurdle rate, then the project should be avoided, as it doesn't make the minimum return required. For example, your company has a hurdle rate of 10%. If the IRR is 7%, should they invest in the project? The answer is No. The IRR is less than the company's required rate of return. When dealing with mutually exclusive projects, follow the golden rule: choose the project with the highest net present value, since NPV represents a real dollar amount.

9. Payback period

Many companies are also interested in finding the time it takes to break-even on their investment. The break-even point is the point where the initial investment is paid back by all the cash flows. The payback period is simply the time it takes to break even on an investment. Its simplicity makes it a popular metric amongst financial professionals. However, it does have some drawbacks. One of the most notable is it doesn't consider the principles of the time value of money, so it does not discount cash flows. Another shortfall is that it does not analyze a project's profitability.

10. Payback period

Discounted payback period is a similar method to payback period, but the major difference is it considers the time value of money by discounting the cash flows. This makes the discounted payback period a more conservative method, as the cash flows will be reduced, making the break-even point further away.

11. Payback period

Here, we can see the difference between a regular payback period and a discounted one. The regular payback period breaks-even in two years, whereas the discounted payback period takes three years.

12. Payback period

Making decisions with a payback period is also straightforward. An investment horizon is the length of time an investor wants to be invested in an asset. Therefore, the project shouldn't be selected if the payback period is longer than the investment horizon.

13. Let's practice!

Okay, now it's time to put this knowledge to use. Let's practice!