2 Replies Latest reply on May 26, 2018 10:32 AM by Yuriy Fal

    XIRR in Tableau

    suvas.chandra

      Can we calculate XIRR in Tableau ? I see that using R integration it is possible to calculate IRR. But, is it possible to do XIRR ?

        • 1. Re: XIRR in Tableau
          Esther Aller

          Hi Suvas,

           

          I do not know if it is possible to calculation XIRR in R, however it is definitely not currently a feature of Tableau Desktop. I recommend voting on this Idea https://community.tableau.com/ideas/2893 to show your interest

           

           

          • 2. Re: XIRR in Tableau
            Yuriy Fal

            This Table Calc has been working for me.

            Credits go to Alberto Santini, link is here:  XIRR · GitHub

            // XIRR in Tableau+R

             

            SCRIPT_REAL('

            sppv <- function (i, n) {

              return((1 + i/100)^(-n))

            }

             

            # Net Present Value

            npv <- function(x, i) {

              npv = c()

              for (k in 1:length(i)) {

                pvs = x * sppv(i[k], 1:length(x))

                npv = c(npv, sum(pvs))

              }

              return(npv)

            }

             

            # Internal rate of return for non-periodic cash flow

            # Input: cashflow - vector of numeric

            #           dates - vector of strings containing dates with this format "%d-%m-%y"

            # Output: irr - internal rate of return - range 0,1

            #

            xirr <- function (cashflow, dates) {

              if (length(cashflow) != length(dates)) {

                stop("length(cashflow) != length(dates)")

              }

             

              cashflow_adj <- c(cashflow[1])

              for (i in 1:(length(cashflow)-1)) {

                d1 <- as.Date(dates[i], "%d-%m-%y")

                d2 <- as.Date(dates[i+1], "%d-%m-%y")

                # There are no checks about the monotone values of dates

                interval <- as.integer(d2 - d1)

                cashflow_adj <- c(cashflow_adj, rep(0, interval-1), cashflow[i+1])

              }

             

              # Bisection method finding the rate to zero npv

              left = -10

              right = 10

              epsilon = 1e-8

              while (abs(right-left) > 2*epsilon) {

                midpoint = (right+left)/2

                if (npv(cashflow_adj, left) * npv(cashflow_adj, midpoint) > 0) {

                  left = midpoint

                } else {

                  right = midpoint

                }

              }

             

              # Irr for daily cashflow (not in percentage format)

              irr = (right+left) / 2 / 100 

              # Irr for daily cashflow multiplied by 365 to get yearly return

              irr <- irr * 365

              # Annualized yield (return) reflecting compounding effect of daily returns

              irr <- (1 + irr / 365) ^ 365 - 1

             

              irr

            }

             

            cashflow <- .arg1

            dates <- .arg2

            xirr(cashflow, dates)

            ',

            ATTR([Cash Flow Amount]),

            ATTR(STR(int(day([Date]))) + '-' + str(int(month([Date]))) + '-' + str(int(right(str(YEAR([Date])),2))))

            )

             

            Hope it could help.

             

            Yours,

            Yuri