XIRR function

[Vitalikssssss]

Hi everyone,

I would like to implement XIRR function from Excel in Caspio.

Can somebody give me a direction on how to achieve this?

Thanks,

vitalikssssss

[Hastur]

Hello @Vitalikssssss!

It is possible to implement such a formula within Tabular report using additional JS.

Please follow these steps:

1. You need to create additional APP parameter to use the "moment.js" library.
Please check this article to get familiar with the APP Parameters in Caspio - https://howto.caspio.com/apps/app-parameters/

2. Create the Tabular Report and add the Header/Footer. Please insert this code into the Header:

<div style='display:flex; justify-content:flex-start;padding:30px;'> 
      <input placeholder="Guess Rate, %" id='rate'></input>
      <button id='click' class='cbResultSetAddButton' style='margin-left:10px;'>Calculate XIRR</button>
      <div id='result' style='margin-left:10px;'></div>
    </div>
    <script src="[@app:URL_1]"></script>
    <script>
       
      "use strict";

document.addEventListener('DataPageReady', function () {
  document.querySelector('#click').addEventListener('click', function (event) {
    var formatDate = 'DD/MM/YYYY';
    var columnDateNumber = 2;   //Change based on your column position on a result set
    var columnValuesNumber = 3; //Change based on your column position on a result set

//Not changeable part

    var datesSelector = "tr>td:nth-child(".concat(columnDateNumber, ")[class^=\"cbResultSetData\"]");
    var valuesSelector = "tr>td:nth-child(".concat(columnValuesNumber, ")[class^=\"cbResultSetData\"]");
    var dates = [];
    var values = [];
    document.querySelectorAll(datesSelector).forEach(function (item, index) {
      if (!item.hasAttribute('style')) {
        return dates.push(item.innerText);
      }
    });
    document.querySelectorAll(valuesSelector).forEach(function (item, index) {
      if (!item.hasAttribute('style')) {
        return values.push(+item.innerText);
      }
    });
    var guess = +document.querySelector('#rate').value;
    var some;

    if (guess != 0) {
      some = guess/100;
    }

    document.querySelector('#result').innerHTML = "Result:<strong>".concat(XIRR(values, dates, some, formatDate)*100, "%</strong>");
  });
});

function XIRR(values, dates, guess, local) {
  // Credits: algorithm inspired by Apache OpenOffice
  // Calculates the resulting amount
  var irrResult = function irrResult(values, dates, rate) {
    var r = rate + 1;
    var result = values[0];

    for (var i = 1; i < values.length; i++) {
      result += values[i] / Math.pow(r, moment(dates[i], local).diff(moment(dates[0], local), "days") / 365);
    }

    return result;
  }; // Calculates the first derivation


  var irrResultDeriv = function irrResultDeriv(values, dates, rate) {
    var r = rate + 1;
    var result = 0;

    for (var i = 1; i < values.length; i++) {
      var frac = moment(dates[i], local).diff(moment(dates[0], local), "days") / 365;
      result -= frac * values[i] / Math.pow(r, frac + 1);
    }

    return result;
  }; // Check that values contains at least one positive value and one negative value


  var positive = false;
  var negative = false;

  for (var i = 0; i < values.length; i++) {
    if (values[i] > 0) positive = true;
    if (values[i] < 0) negative = true;
  } // Return error if values does not contain at least one positive value and one negative value


  if (!positive || !negative) return "#NUM!"; // Initialize guess and resultRate

  var guess = typeof guess === "undefined" ? 0.1 : guess;
  var resultRate = guess; // Set maximum epsilon for end of iteration

  var epsMax = 1e-10; // Set maximum number of iterations

  var iterMax = 50; // Implement Newton's method

  var newRate, epsRate, resultValue;
  var iteration = 0;
  var contLoop = true;

  do {
    resultValue = irrResult(values, dates, resultRate);
    newRate = resultRate - resultValue / irrResultDeriv(values, dates, resultRate);
    epsRate = Math.abs(newRate - resultRate);
    resultRate = newRate;
    contLoop = epsRate > epsMax && Math.abs(resultValue) > epsMax;
  } while (contLoop && ++iteration < iterMax);

  if (contLoop) return "#NUM!"; // Return internal rate of return

  return resultRate;
}
        
    </script>

You should change the selectors of your Date and Value fields, if they are placed in the different position.
Selectors can be changed here: 

    var columnDateNumber = 2;   //Change based on your column position on a result set
    var columnValuesNumber = 3; //Change based on your column position on a result set

Please find the print screen attached.

Do not forget to Disable the HTML editor.

 

Also, find the dummy application with this customization attached - XIRR_Function_1_0_2019-Aug-22_1503.zip

Please let me know if you need any assistance.