Calculating IRR (internal rate of return) on panel data?

[Becky Manning]

Hi, I have the following dataset

clear
input id str10 date1 CF TV
1 "03/31/2000" -100 100
1 "06/30/2000" -100 200
1 "09/30/2000" 0 220
1 "12/31/2000" 0 230
1 "03/31/2001" 0 230
2 "06/30/2005" -80 80
2 "09/30/2005" 0 85
2 "12/31/2005" -20 105
2 "03/31/2006" 15 110
2 "06/30/2006" 32 118
2 "09/30/2006" 118 0
end

gen date = date(date1, "MDY")
format date %td
gen qdate = qofd(date)
format qdate %tq
xtset id qdate

For each id, there are a series of cash flows (CF), by date, with a positive value being an outflow. For each date, there is also a terminal value. I would like to generate a new variable (IRR) that is the internal rate of return for that id as of each date. The IRR is based on the periodic cash flows from the id's first observation in the dataset through the current date, plus the terminal value on the current date. For instance, for id == 1 and date == 9/30/2000, the series of values for the IRR calculation would be -100, -100, 220, resulting in an IRR = 28.62% (this is the value excel calculates with the xirr function). My dataset consists of 200,000 id-date observations with approximately 40 observations per id, so I am looking to calculate around 40 different IRR values for each of the approximately 5,000 unique ids. I have tried reading up on the irr and finxirr commands from ssc, but I cannot figure out how to apply it to panel data like I have. Exporting the data to excel to run calculations is not an option for me, either. I would be so grateful for the forum's help.

Thanks,
Becky

[Clyde Schechter]

Caveat: I have never heard of internal rate of return before, although I have often worked with net present value which, according to what I see in the help file for -finxirr- (which, by the way is not from SSC, but from http://www.stata.com/users/kcrow) is a closely related concept. Regardless, on the assumption that -finxirr- works correctly, it can easily be adapted to your data by wrapping it in a program and iterating the program under -rangerun- :

gen date = date(date1, "MDY")
format date %td
gen qdate = qofd(date)
format qdate %tq
xtset id qdate

capture program drop one_xirr
program define one_xirr
    if _N > 1 {
        tsset date, daily
        tempvar stream
        gen `stream' = cond(_n == _N, TV, CF)
        finxirr `stream'
        gen xirr = r(xirr)
    }
    exit
end

rangerun one_xirr, by(id) interval(date . 0) verbose

Note: The -verbose- option in the -rangerun- command is not necessary for the calculation. However, I left it in so that in the event -finxirr- throws errors, you will see them. As I said, I'm not familiar with -finxirr- and so I don't know if this will prove important for debugging, or whether everything will run just fine from the start. I just don't know what -finxirr- really does, what abnormal conditions in the data it might check for, and how "finicky" it might be. I also don't know if your data set is squeaky clean, riddled with glitches, or somewhere in between. So the -verbose- option will let you see how compatible your data is with whatever -finxirr- expects of it and how gracefully -finxirr- handles difficult cases.

I suggest you try this out on a modest subset of your data first, because all the -finxirr- output for 200,000 runs is going to slow things down farther, and will keep your screen scrolling forever. Once you are satisfied that this is doing what you need on a modest subset, I recommend removing the -verbose- option for the production run on the full data set.

-rangerun- is written by Robert Picard and is available from SSC. To use -rangerun-, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also from SSC.

[Becky Manning]

Oh my goodness, thank you so much, Clyde. It works perfectly, although with a small change at the fault of my explanation for what I was looking for. For the stream of cash flows, when _n == _N, the value should actually be TV + CF to calculate the correct xirr that I was looking for (I updated it below, in case it's ever of use to anyone else). Such an easy tweak, thanks to your clear code. I so very much appreciate your help!

capture program drop one_xirr program define one_xirr if _N > 1 { tsset date, daily tempvar stream gen `stream' = cond(_n == _N, TV + CF, CF) finxirr `stream' gen xirr = r(xirr) } exit end rangerun one_xirr, by(id) interval(date . 0) verbose

[Nick Cox]

Here's the code in #3 displayed using CODE delimiters:

capture program drop one_xirr

program define one_xirr

if _N > 1 {
     tsset date, daily
     tempvar stream
     gen `stream' = cond(_n == _N, TV + CF, CF)
     finxirr `stream'
     gen xirr = r(xirr)
}

exit
end

rangerun one_xirr, by(id) interval(date . 0) verbose

[Nick Cox]

Compare https://www.reddit.com/r/stata/comme...el_data_by_id/

Please note that it's a request here -- see https://www.statalist.org/forums/help#crossposting --

and a rule in Reddit's Stata community to tell people about cross-posting. #

So closing that thread on Reddit with a cross-reference might help people there.

[Becky Manning]

Thank you, Nick - I have posted the cross reference on reddit. And thank you both for all your assistance and feedback to everyone posting on the forum. It's so incredibly helpful to access to you all.