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

[Adrian Ng]

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:rvaradhan at jhmi.edu] 
Sent: Wednesday, August 25, 2010 7:24 PM
To: Adrian Ng
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 [mailto:ang at 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:rvaradhan at jhmi.edu] 
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 [mailto:ang at hamiltonlane.com] 
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 [mailto:rvaradhan at jhmi.edu] 
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 [mailto: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:
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 at 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.