How to calculate Internal Rate of Return (IRR)

Hi, everyone,

is there anyone who knows how to calculate IRR within Power Apps, below you can find the implementation of code based on vague description of ChatGPT but I am not sure whether it is correct or not. Did not find any official documentation for IRR excel function from which I could reverse engineer it.

Visual Basic has its own library with financial functions where IRR is available but unfortunately I was not able to reproduce it.

Description for IRR function:
Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

Any help or advice would be greatly appreciated. I know the code might be incomprehensible so feel free to ask any question, I will try to respond to them ASAP.

/*
- col_MERCURY_BC_ROP is local collection where all cashflows are stored in record type column hence the ungroup function, basically one row has a column PROFIT_ROP is a record with additional 50 rows (yearCount),
- the arrCashFlows within "With" function is a array of profits which are calculated based on internal formula
*/
Clear(col_Results);
With(
    {   
        locRate: 0.1, // starting guess for IRR (10%)
        locAccuracy: 0.01,
        //cash flows in array
        arrCashFlows: 
                /*internal formula*/
                ForAll(
                    Sequence(yearCount) /*yearCount is static value = 50*/ As aRecord,
                        {
                            profit: 
                                /*calculate individual cashflows*/
                                Sum(
                                    Filter(Ungroup(ShowColumns(col_MERCURY_BC_ROP, PROFIT_ROP, POCET_HP_IK1), PROFIT_ROP), Year = Text(LookUp(yearMatrix, Value(Y) = Value(aRecord.Value - 1)).Year)), POCET_HP_IK1 * Value
                                ),
                            y: aRecord.Value - 1
                        }    
                )
    },
    ForAll(
        Sequence(100, 0, 0.01) As rRecord,
        Collect(col_Results,
            {
                locRate: rRecord.Value - 1,
                /*calculate npv*/
                locNpv: Sum(
                        ForAll(
                            Sequence(yearCount) As nRecord, 
                                Round(
                                    /*calculate individual cashflows*/
                                    Sum(
                                        Filter(
                                            Ungroup(
                                                ShowColumns(col_MERCURY_BC_ROP, PROFIT_ROP, POCET_HP_IK1), PROFIT_ROP), Value(Year) <= Value(LookUp(yearMatrix, Value(Y) = Value(nRecord.Value - 1)).Year)
                                                ), POCET_HP_IK1 * Value)
                                    * 
                                    (
                                        1 / Power((1 + Value(Substitute(txt_wacc_input.Value, ",", "."))), nRecord.Value - 1)
                                    ),
                                2)
                            ), Value
                    )
            }
        )
    )
);
/*find closest positive value to 0*/
Set(varIRR, Text(First(SortByColumns(Filter(LastN(col_Results, 999), Abs(locRate) < 0.01), "locRate", SortOrder.Ascending)).locRate, "0.00%"))

Thank you all.

Cheers

Krystof

Categories:

Using Apps Using Formulas