# [R] Secant Method Convergence (Method to replicate Excel XIRR/IRR)

Thu Aug 26 05:36:20 CEST 2010

```I have written a general root-finder using the secant method.

secant <- function(par, fn, tol=1.e-07, itmax = 100, trace=TRUE, ...) {
# par = a starting vector with 2 starting values
# fn = a function whose first argument is the variable of interest
#
if (length(par) != 2) stop("You must specify a starting parameter vector of length 2")
p.2 <- par[1]
p.1 <- par[2]
f <- rep(NA, length(par))
f[1] <- fn(p.1, ...)
f[2] <- fn(p.2, ...)
iter <- 1
pchg <- abs(p.2 - p.1)
fval <- f[2]
if (trace) cat("par: ", par, "fval: ", f, "\n")
while (pchg >= tol & abs(fval) > tol & iter <= itmax) {
p.new <- p.2 - (p.2 - p.1) * f[2] / (f[2] - f[1])
pchg <- abs(p.new - p.2)
fval <- fn(p.new, ...)
p.1 <- p.2
p.2 <- p.new
f[1] <- f[2]
f[2] <- fval
iter <- iter + 1
if (trace) cat("par: ", p.new, "fval: ", fval, "\n")
}
list(par = p.new, value = fval, iter=iter)
}

Now we can use this function to find the zero of your NPV function as follows:

npv <- function (irr, cashFlow, times) sum(cashFlow / (1 + irr)^times)

CF <- c(-1000,500,500,500,500,500)

dates <- c("1/1/2001","2/1/2002","3/1/2003","4/1/2004","5/1/2005","6/1/2006")
cfDate <- as.Date(cfDate,format="%m/%d/%Y")
times <- as.numeric(difftime(cfDate, cfDate[1], units="days"))/365.24

secant(par=c(0,0.1), fn=npv, cashFlow=CF, times=times)

> secant(par=c(0,0.1), fn=npv, cashFlow=CF, times=times)
par:  0 0.1 fval:  854.2388 1500
par:  0.232284 fval:  334.7318
par:  0.2990093 fval:  156.9595
par:  0.3579227 fval:  30.59229
par:  0.3721850 fval:  3.483669
par:  0.3740179 fval:  0.08815743
par:  0.3740655 fval:  0.0002613245
par:  0.3740656 fval:  1.966778e-08
\$par
[1] 0.3740656

\$value
[1] 1.966778e-08

\$iter
[1] 8

Hope this helps,
Ravi.

____________________________________________________________________

Assistant Professor,
Division of Geriatric Medicine and Gerontology
School of Medicine
Johns Hopkins University

Ph. (410) 502-2619

----- Original Message -----
Date: Wednesday, August 25, 2010 10:51 pm
Subject: Re: [R] Secant Method Convergence (Method to replicate Excel XIRR/IRR)
To: Adrian Ng <ang at hamiltonlane.com>
Cc: "r-help at r-project.org" <r-help at r-project.org>

> You should use cfDate[1] as the time origin.  You cannot use
> 08-24-2010 as the time origin, since that will yield negative times.
>
>
>  Here is the correct solution.
>
>  ANXIRR <- function (cashFlow, dates, guess, tol=1.e-04){
>
>  npv <- function (cashFlow, times, irr) {
>  n <- length(cashFlow)
>  sum(cashFlow / (1 + irr)^times)
>  }
>
>  if (guess == 0) stop("Initial guess must be strictly greater than 0")
>
>
>  cfDate <- as.Date(cfDate,format="%m/%d/%Y")
>  times <- as.numeric(difftime(cfDate, cfDate[1], units="days") /
>  365.24)
>
>  irrprev <- c(0)
>  irr <- guess
>  pvPrev <- sum(cashFlow)
>  pv <- npv(cashFlow, times, irr)
>  eps <- abs(pv-pvPrev)
>
>  while (eps >= tol) {
>  tmp <- irrprev
>  irrprev <- irr
>  irr <- irr - ((irr - tmp) * pv / (pv - pvPrev))
>  pvPrev <- pv
>  pv <- npv(cashFlow, times, irr)
>  eps <- abs(pv - pvPrev)
>  }
>  list(irr = irr, npv = pv)
>  }
>  CF <- c(-1000,500,500,500,500,500)
>
>  dates <-
> c("1/1/2001","2/1/2002","3/1/2003","4/1/2004","5/1/2005","6/1/2006")
>
>  ANXIRR(CF, dates, guess=0.1)
>
>  > ANXIRR(CF, dates, guess=0.1)
>  \$irr
>  [1] 0.3740656
>
>  \$npv
>  [1] 2.102695e-09
>
>
>  Hope this helps,
>  Ravi.
>
>  ____________________________________________________________________
>
>  Assistant Professor,
>  Division of Geriatric Medicine and Gerontology
>  School of Medicine
>  Johns Hopkins University
>
>  Ph. (410) 502-2619
>
>
>  ----- Original Message -----
>  From: Adrian Ng <ang at hamiltonlane.com>
>  Date: Wednesday, August 25, 2010 8:33 pm
>  Subject: RE: [R] Secant Method Convergence (Method to replicate Excel
> XIRR/IRR)
>  Cc: "r-help at r-project.org" <r-help at r-project.org>
>
>
>  > Hi Ravi,
>  >
>  >  Using days and dividing it by 365 effectively converts the number
> to
>  > years anyway and allows for the irregular times to be specific to
> the
>  > days.
>  >
>  >  Also, when I replace dates[1] in your line:
>  >   times <- as.numeric(difftime(dates, dates[1], units="days") /
>  >  365.24) with "2010-08-24" I think I am getting some irregular
>  > results.
>  >
>  >  Effectively, what I was trying to do was match what Excel produced
>
>  > with its XIRR function.  With the example I gave excel returned an
> IRR
>  > of ~0.37 (or 37%)
>  >
>  >  I am still in the process of debugging it...
>  >
>  >
>  >
>  >
>  >
>  >  -----Original Message-----
>  >  From: Ravi Varadhan [
>  >  Sent: Wednesday, August 25, 2010 7:24 PM
>  >  Cc: r-help at r-project.org
>  >  Subject: RE: [R] Secant Method Convergence (Method to replicate
> Excel
>  > XIRR/IRR)
>  >
>  >  The secant method converges just fine.  Your problem might have
>  > occurred due
>  >  to improper conversion of dates to elapsed time.  You want to
>  > calculate IRR
>  >  using "year" as the time unit, not "days".
>  >
>  >  Here is the secant function (modified to account for irregular
> times)
>  > and
>  >  the results for your example:
>  >
>  >  ANXIRR <- function (cashFlow, dates, guess, tol=1.e-04){
>  >
>  >  npv <- function (cashFlow, times, irr) {
>  >  	n <- length(cashFlow)
>  >  	sum(cashFlow / (1 + irr)^times)
>  >  	}
>  >
>  >  if (guess == 0)stop("Initial guess must be strictly greater than
> 0")
>  >
>  >
>  >  	times <- as.numeric(difftime(dates, dates[1], units="days") /
>  >  365.24)
>  >
>  >          irrprev <- c(0)
>  >            irr <- guess
>  >          pvPrev <- sum(cashFlow)
>  >          pv <- npv(cashFlow, times, irr)
>  >  	eps <- abs(pv-pvPrev)
>  >
>  >  while (eps >= tol) {
>  >          tmp <- irrprev
>  >           irrprev <- irr
>  >          irr <- irr - ((irr - tmp) * pv / (pv - pvPrev))
>  >          pvPrev <- pv
>  >          pv <- npv(cashFlow, times, irr)
>  >           eps <- abs(pv - pvPrev)
>  >          }
>  >  	list(irr = irr, npv = pv)
>  >  }
>  >
>  >  CF <- c(-1000,500,500,500,500,500)
>  >
>  >  dates <-
>  >
> c("1/1/2001","2/1/2002","3/1/2003","4/1/2004","5/1/2005","6/1/2006")
>  >
>  >
>  >  ANXIRR(CF, dates, guess=0.1)
>  >
>  >  > ANXIRR(CF, dates, guess=0.1)
>  >  \$irr
>  >  [1] 0.4106115
>  >
>  >  \$npv
>  >  [1] 2.984279e-13
>  >
>  >
>  >  Ravi.
>  >
>  >  -----Original Message-----
>  >  From: Adrian Ng [
>  >  Sent: Wednesday, August 25, 2010 6:23 PM
>  >  Subject: RE: [R] Secant Method Convergence (Method to replicate Excel
>  >  XIRR/IRR)
>  >
>  >  The forum is kind of slow so I'm just re-sending you the message here:
>  >
>  >  Hi Ravi,
>  >
>  >  I'm just trying a fairly simple example:
>  >  CFs: -1000,500,500,500,500,500
>  >
>  >
> dates<-c("1/1/2001","2/1/2002","3/1/2003","4/1/2004","5/1/2005","6/1/2006")
>
>  >
>  >
>  >  Thanks a lot for your help.
>  >
>  >  -----Original Message-----
>  >  From: Ravi Varadhan [
>  >  Sent: Wednesday, August 25, 2010 5:44 PM
>  >  To: Adrian Ng; r-help at r-project.org
>  >  Subject: RE: [R] Secant Method Convergence (Method to replicate Excel
>  >  XIRR/IRR)
>  >
>  >  Yes, the secant method (like Newton Raphson) is not guaranteed to
> converge,
>  >  unlike the bisection method, but it has a superlinear convergence
>
>  > (not that
>  >  this matters much!).  Brent's method, which is used in `uniroot',
> is
>  > a
>  >  reliable and fast method, which is why I suggested it in my
> previous
>  > email.
>  >
>  >  Having said that, I am not sure about the convergence problem that
>
>  > you are
>  >  having without seeing the actual example.
>  >
>  >  Ravi.
>  >
>  >  -----Original Message-----
>  >  From: Adrian Ng [
>  >  Sent: Wednesday, August 25, 2010 5:28 PM
>  >  To: Ravi Varadhan; r-help at r-project.org
>  >  Subject: RE: [R] Secant Method Convergence (Method to replicate Excel
>  >  XIRR/IRR)
>  >
>  >  Hi Ravi,
>  >
>  >  Thanks for the responses.  I was actually trying to calculate IRR
>
>  > based on
>  >  unevenly spaced cash flows, and that's why I decided to use the secant
>  >  method.  I'm not sure if my answer isn't converging because I have
> some
>  >  careless mistake in the code, or if it's simply because unlike the
> bisection
>  >  method, the secant method doesn't 'sandwich' the desired root.
>  >
>  >
>  >
>  >  -----Original Message-----
>  >  From: Ravi Varadhan [
>  >  Sent: Wednesday, August 25, 2010 5:24 PM
>  >  To: Adrian Ng; r-help at r-project.org
>  >  Subject: RE: [R] Secant Method Convergence (Method to replicate Excel
>  >  XIRR/IRR)
>  >
>  >  Another approach is to use `uniroot' to find the zero of the NPV function:
>  >
>  >  npv <- function (cashFlow, irr) {
>  >  	n <- length(cashFlow)
>  >  	sum(cashFlow / (1 + irr)^{0: (n-1)})
>  >  	}
>  >
>  >  uniroot(f=npv, interval=c(0,1), cashFlow=cashFlow)
>  >
>  >  However, there may be situations where there are no real zeros or
>
>  > there are
>  >  multiple zeros of the NPV function.
>  >
>  >  Ravi.
>  >
>  >  -----Original Message-----
>  >  From: r-help-bounces at r-project.org [ On
>  >  Behalf Of Adrian Ng
>  >  Sent: Wednesday, August 25, 2010 8:39 AM
>  >  To: r-help at r-project.org
>  >  Subject: [R] Secant Method Convergence (Method to replicate Excel
> XIRR/IRR)
>  >
>  >  Hi,
>  >
>  >  I am new to R, and as a first exercise, I decided to try to
> implement
>  > an
>  >  XIRR function using the secant method.  I did a quick search and
> saw
>  > another
>  >  posting that used the Bisection method but wanted to see if it was
> possible
>  >  using the secant method.
>  >
>  >  I would input a Cash Flow and Date vector as well as an initial
>  > guess.  I
>  >  hardcoded today's initial date so I could do checks in Excel.
> This code
>  >  seems to only converge when my initial guess is very close to the
> correct
>  >  IRR.
>  >
>  >  Maybe I have some basic errors in my coding/logic? Any help would
> be
>  > greatly
>  >  appreciated.
>  >
>  >  The Wikipedia article to secant method and IRR:
>  >
>  >
>  >  Thanks!
>  >
>  >
>  >
>  >  ANXIRR <- function (cashFlow, cfDate, guess){
>  >          cfDate<-as.Date(cfDate,format="%m/%d/%Y")
>  >          irrprev <- c(0); irr<- guess
>  >
>  >
>  >          pvPrev<- sum(cashFlow)
>  >          pv<-
>  >  sum(cashFlow/((1+irr)^(as.numeric(difftime(cfDate,"2010-08-24",units="days")
>  >  )/360)))
>  >          print(pv)
>  >          print("Hi")
>  >
>  >
>  >  while (abs(pv) >= 0.001) {
>  >          t<-irrprev; irrprev<- irr;
>  >          irr<-irr-((irr-t)*pv/(pv-pvPrev));
>  >          pvPrev<-pv;
>  >
>  >  pv<-sum(cashFlow/((1+irr)^(as.numeric(difftime(cfDate,"2010-08-24",units="da
>  >  ys"))/365)))
>  >          print(irr);print(pv)
>  >          }
>  >  }
>  >
>  >
>  >
>  >
>  >
>  >  Please consider the environment before printing this e-mail.
>  >
>  >  	[[alternative HTML version deleted]]
>  >
>  >  ______________________________________________
>  >  R-help at r-project.org mailing list
>  >
>  >  and provide commented, minimal, self-contained, reproducible code.
>  >
>  >
>  >
>
>  ______________________________________________
>  R-help at r-project.org mailing list
>