Jump to content
  • 0

Question

1 answer to this question

Recommended Posts

  • 1

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/

joxi_screenshot_1566462737646.thumb.png.d841c77eb0bf46f7cc52fc40a2a32acf.png

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.

joxi_screenshot_1566462681522.png.f09a5fe3ca154b49079067080a22f521.png

Do not forget to Disable the HTML editor.

joxi_screenshot_1566462660459.png.e5c32432e9944bdea38bc97f11936694.png

 

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...