I created a small X++ wrapper to call XIRR function from the C# project provided by https://github.com/klearlending/XIRR (Thank you!)
You can test XIRR function in Excel, btw.
using tmxExcelFinance; /// <summary> /// Implements various financial functions /// </summary> public final class myFinanceFunction { /// <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; while(leC.moveNext()) { [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, myInvestmentDetailsLineTmp _myInvestmentDetailsLineTmp) { List listCashFlowsDates = new List(Types::Container); myInvestmentDetailsLineTmp myInvestmentDetailsLineTmpLocal; myInvestmentDetailsLineTmpLocal.linkPhysicalTableInstance(_myInvestmentDetailsLineTmp); while select myInvestmentDetailsLineTmpLocal { listCashFlowsDates.addEnd([myInvestmentDetailsLineTmpLocal.TransactionCurrencyAmount, DateTimeUtil::newDateTime(myInvestmentDetailsLineTmpLocal.LineDate, 0)]); } myProjXIRRTable.XIRRValue = myFinanceFunction::calculateXIRR(listCashFlowsDates, 4); ...
Or
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 = myFinanceFunction::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.
_1074.jpg)
Posts
