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 [mailto:rvarad...@jhmi.edu] 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 [mailto:a...@hamiltonlane.com] 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 [mailto:rvarad...@jhmi.edu] 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 [mailto:a...@hamiltonlane.com] 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 [mailto:rvarad...@jhmi.edu] 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 [mailto: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: http://en.wikipedia.org/wiki/Internal_rate_of_return#Numerical_solution 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 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. ______________________________________________ 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.