Internal rate of return and payback period

Financial Analysis in Power BI

Nick Edwards

Capital Markets Analyst

Internal rate of return (IRR)

The internal rate of return is the discount rate that makes NPV zero.

A graph that shows an NPV profile, and points to where NPV equals 0. This is the internal rate of return.

Financial Analysis in Power BI

Internal rate of return (IRR)

The internal rate of return is the discount rate that makes NPV zero.

  • It is an iterative function
    • "guess-and-check"
    • Not easy to find by hand
  • XIRR() is used in Power BI
    • Cash flows
    • Dates of cash flows

IRR is where the NPV equals 0

1 https://www.investopedia.com/terms/i/irr
Financial Analysis in Power BI

Testing the IRR

Let's say XIRR gives us 21.86% for this cash flow...

Plugging in the IRR of a cash flow into it's NPV formula will make it equal zero.

Financial Analysis in Power BI

Testing the IRR

Let's say XIRR gives us 21.86% for this cash flow...

Plugging in the IRR of a cash flow into it's NPV formula will make it equal zero.

Financial Analysis in Power BI

Testing the IRR

Let's say XIRR gives us 21.86% for this cash flow...

Plugging in the IRR of a cash flow into it's NPV formula will make it equal zero.

Financial Analysis in Power BI

Testing the IRR

Let's say XIRR gives us 21.86% for this cash flow...

Plugging in the IRR of a cash flow into it's NPV formula will make it equal zero.

Financial Analysis in Power BI

Making decisions with IRR

  • A hurdle rate is the target return for an investment

IRR investment criteria:

  • If IRR > the hurdle rate then invest
  • If IRR < the hurdle rate then don't invest
  • Mutually exclusive projects:
    • Choose the project with the highest net present value
    • NPV represents a real dollar amount

Example: Your company has a hurdle rate of 10%. If the IRR is 7%, should they invest in the project?

Answer: No! IRR < the hurdle rate.

Financial Analysis in Power BI

Payback period

  • The break-even point is the point where initial investment + cash flows = 0.
  • Payback period is the time it takes to break even
    • Simple to understand, making it a popular metric
    • Does not consider the time value of money; no discounting of cash flows
    • Does not analyze profitability
1 https://www.investopedia.com/terms/p/paybackperiod.asp
Financial Analysis in Power BI

Payback period

  • The break-even point is the point where initial investment + cash flows = 0.
  • Payback period is the time it takes to break-even.
    • Simple to understand, making it a popular metric
    • Does not consider the time value of money; no discounting of cash flows
    • Does not analyze profitability

Discounted payback period

  • Uses discount cash flows to find the length of time it takes break-even
    • Uses time value of money
    • More conservative since cash flows are discounted, it will take longer
1 https://www.investopedia.com/terms/p/paybackperiod.asp
Financial Analysis in Power BI

Payback period

Period 0 1 2 3 4 Payback Period
CF (5,000.00) 2,500.00 2,500.00 2,500.00 2,500.00 2
Discounted CF (10%) (5,000.00) 2,272.73 2,066.12 1,878.29 1,707.53 3
Financial Analysis in Power BI

Payback period

Period 0 1 2 3 4 Payback Period
CF (5,000.00) 2,500.00 2,500.00 2,500.00 2,500.00 2
Discounted CF (10%) (5,000.00) 2,272.73 2,066.12 1,878.29 1,707.53 3

Decision criteria

  • Investment horizon is the length of time an investor wants to be invested in an asset.
    • If the payback period =< the investment horizon, invest
    • If the payback period > the investment horizon, don't invest
Financial Analysis in Power BI

Let's practice!

Financial Analysis in Power BI

Preparing Video For Download...