INTRATE

How to Use the Power BI DAX function INTRATE

In this article, we will take a closer look at the INTRATE function and show you how to use it effectively in Power BI.

Understanding the Concept of Internal Rate of Return

Before we delve into the specifics of the INTRATE function, it is important to understand the concept of internal rate of return (IRR). IRR is the rate at which the net present value (NPV) of cash flows is equal to zero. In other words, it is the rate at which the investment’s cash inflows and outflows are equal.

To calculate the IRR manually, you would need to use a trial-and-error method, where you adjust the discount rate until the NPV of the cash flows is zero. However, this can be a time-consuming and tedious process, particularly if you have a large number of cash flows.

This is where the INTRATE function in Power BI comes in. It allows you to easily calculate the IRR for a series of cash flows, saving you time and effort.

Syntax of the INTRATE Function

The syntax of the INTRATE function is as follows:

INTRATE(values, [guess])

The "values" parameter is a required argument that represents the series of cash flows for which you want to calculate the IRR. This can be a range of cells in a table or a DAX expression that returns a table.

The "[guess]" parameter is an optional argument that represents your guess for the IRR. If you do not provide a guess, Power BI will use a default value of 0.1 (10%).

Examples of Using the INTRATE Function

Let's take a look at some examples of how to use the INTRATE function in Power BI.

Example 1: Simple Cash Flow

Suppose you have a simple cash flow that looks like this:

| Year | Cash Flow |

|------|-----------|

| 0 | -1000 |

| 1 | 400 |

| 2 | 500 |

| 3 | 600 |

To calculate the IRR for this cash flow using the INTRATE function, you would use the following formula:

INTRATE(-1000, 400, 500, 600)

This would return an IRR of approximately 23.46%.

Example 2: Cash Flow with Uneven Periods

Suppose you have a cash flow that has uneven periods, like this:

| Period | Cash Flow |

|--------|-----------|

| 0 | -1000 |

| 0.5 | 200 |

| 1.5 | 300 |

| 2.5 | 400 |

| 3.5 | 500 |

To calculate the IRR for this cash flow using the INTRATE function, you would use the following formula:

INTRATE((-1000, 200, 300, 400, 500), 0.1)

This would return an IRR of approximately 26.71%.

Example 3: Cash Flow with Non-Annual Periods

Suppose you have a cash flow that has non-annual periods, like this:

| Year | Cash Flow |

|------|-----------|

| 0 | -1000 |

| 6 | 400 |

| 12 | 500 |

| 18 | 600 |

To calculate the IRR for this cash flow using the INTRATE function, you would use the following formula:

INTRATE((-1000, 400, 500, 600), 0.1/12, 12)

In this formula, we have used the "guess" parameter to specify a monthly rate of 0.1/12 and the "values" parameter to specify the payment intervals (in months). This would return an IRR of approximately 12.04%.

The Power BI DAX function INTRATE is a powerful tool that allows you to easily calculate the internal rate of return for a series of cash flows. By understanding the concept of IRR and the syntax of the INTRATE function, you can use this tool to evaluate the profitability of investments or projects with ease. Whether you have simple or complex cash flows, the INTRATE function can help you save time and effort in your financial analysis.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)