Vitalikssssss Posted August 22, 2019 Report Share Posted August 22, 2019 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 Quote Link to comment Share on other sites More sharing options...
2 Hastur Posted August 22, 2019 Report Share Posted August 22, 2019 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. Vitalikssssss 1 Quote Link to comment Share on other sites More sharing options...
Question
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
Link to comment
Share on other sites
1 answer to this question
Recommended Posts
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.