Thursday, April 20, 2023

X++ to run XIRR

I created a small X++ wrapper to call XIRR function from the C# project provided by (Thank you!) 

You can test XIRR function in Excel, btw.

using tmxExcelFinance;
/// <summary>
/// Implements various financial functions
/// </summary>
public final class mgcFinanceFunction
    /// <summary>
    /// Calculates XIRR value
    /// </summary>
    /// <param name = "_cashFlows">List of containers kind [cashflow amount, its date]</param>
    /// <param name = "_decimals">how many decimals to calculate</param>
    /// <param name = "_maxRate">Maximum rate</param>
    /// <returns>XIRR value</returns>
    public static real calculateXIRR(List _cashFlows, int _decimals, real _maxRate = 1000000)
        var                                 cashFlows       = new System.Collections.Generic.List<CashFlowDates>();
        System.Collections.IEnumerable      cashFlowsI;
        ListEnumerator                      leC             = _cashFlows.getEnumerator();
        System.Double                       dbl;
        System.DateTime                     dt;

            [dbl, dt] = leC.current();
            cashFlows.Add(new CashFlowDates(dbl, dt));
        // convert to iterable
        cashFlowsI = cashFlows;

        return XIRR::Calc(cashFlowsI, _decimals, _maxRate);


This is how we can call the wrapper

 public static void calculateXIRRFromInvestmentDetailsLineTmp(RefRecId _projTableRecId, mgcInvestmentDetailsLineTmp _mgcInvestmentDetailsLineTmp)
        List listCashFlowsDates = new List(Types::Container);

        mgcInvestmentDetailsLineTmp mgcInvestmentDetailsLineTmpLocal;


        while select mgcInvestmentDetailsLineTmpLocal
            listCashFlowsDates.addEnd([mgcInvestmentDetailsLineTmpLocal.TransactionCurrencyAmount, DateTimeUtil::newDateTime(mgcInvestmentDetailsLineTmpLocal.LineDate, 0)]);
        mgcProjXIRRTable.XIRRValue = mgcFinanceFunction::calculateXIRR(listCashFlowsDates, 4);


 cashFlows1 = new List (Types::Container);
        cashFlows1.addEnd([-10000,str2Date("01/01/2008", 123)]);
        cashFlows1.addEnd([2750, str2Date("01/03/2008", 123)]);
        cashFlows1.addEnd([4250, str2Date("30/10/2008", 123)]);
        cashFlows1.addEnd([3250, str2Date("15/02/2009", 123)]);
        cashFlows1.addEnd([2750, str2Date("01/04/2009", 123)]);

res = mgcFinanceFunction::calculateXIRR(cashFlows1, 10);

Notes about the solution creation.

D365FO project must be <TargetFrameworkVersion>v4.6</TargetFrameworkVersion>

Last remark. I tried to implemented the same algorithm in X++, but I bumped into two limitations.

First, there is a limit for recursion depth - 400 levels; we can flatten it, though. 

Second, real loses required decimals and ends up with division by zero.

No comments: