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. ____________________________________________________________________ Ravi Varadhan, Ph.D. Assistant Professor, Division of Geriatric Medicine and Gerontology School of Medicine Johns Hopkins University Ph. (410) 502-2619 email: rvarad...@jhmi.edu ----- Original Message ----- From: Ravi Varadhan <rvarad...@jhmi.edu> Date: Wednesday, August 25, 2010 10:51 pm Subject: Re: [R] Secant Method Convergence (Method to replicate Excel XIRR/IRR) To: Adrian Ng <a...@hamiltonlane.com> Cc: "r-help@r-project.org" <r-help@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. > > ____________________________________________________________________ > > Ravi Varadhan, Ph.D. > Assistant Professor, > Division of Geriatric Medicine and Gerontology > School of Medicine > Johns Hopkins University > > Ph. (410) 502-2619 > email: rvarad...@jhmi.edu > > > ----- Original Message ----- > From: Adrian Ng <a...@hamiltonlane.com> > Date: Wednesday, August 25, 2010 8:33 pm > Subject: RE: [R] Secant Method Convergence (Method to replicate Excel > XIRR/IRR) > To: Ravi Varadhan <rvarad...@jhmi.edu> > Cc: "r-help@r-project.org" <r-help@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 > > To: Adrian Ng > > Cc: r-help@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 > > To: Ravi Varadhan > > 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. > > Adrian > > > > -----Original Message----- > > From: Ravi Varadhan [ > > Sent: Wednesday, August 25, 2010 5:44 PM > > To: Adrian Ng; r-help@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@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@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-boun...@r-project.org [ On > > Behalf Of Adrian Ng > > Sent: Wednesday, August 25, 2010 8:39 AM > > To: r-help@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@r-project.org mailing list > > > > PLEASE do read the posting guide > > and provide commented, minimal, self-contained, reproducible code. > > > > > > > > ______________________________________________ > R-help@r-project.org mailing list > > PLEASE do read the posting guide > and provide commented, minimal, self-contained, reproducible code. ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.