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 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.