This series of posts was inspired by and/or modified in part from the methods for performance metrics for private equity investments by Karl Polen

1 Packages

library(readr)
library(dplyr)
library(tidyr)
library(purrr)

library(zoo)
library(lubridate)
library(tidyquant)

library(ggplot2)
library(RColorBrewer)

2 Data

2.1 PE Data

pedata2 <- read_csv('pedata.csv') %>%
  mutate(
    fund=factor(fund),
    date=mdy(date),
    type=factor(type)
  )

pedata2 <- pedata2 %>% 
  group_split(type) %>%
  set_names(pull(group_keys(pedata2, type))) %>%
  map2_dfr(
    ., 
    names(.),
    ~.x %>% 
      group_by(date) %>% 
      summarise(value=sum(value), type=.y)
  ) %>%
  mutate(fund="Total", .before = date) %>%
  bind_rows(pedata2 %>% arrange(type, fund), .) %>%
  mutate_at(.vars = vars(fund, type) , .funs = factor)

pedata2 %>%
  format.dt.f(page_length = 10, perc_vars=NA, ron_vars=c("value"))

2.2 Market Data

# out1<-tibble(date=seq(first(r2k2$date), last(r2k2$date), by='days'))
# ndays<-interval(first(r2k2$date), last(r2k2$date))/days(1)
# out2<-tibble(date=first(r2k2$date) + days(0:ndays))
# all.equal(out1, out2)

r2k2  <- tq_get(
  '^RUT', # Russell 2000 Index
  get = "stock.prices", 
  from = min(pedata2$date)-5,
  to = max(pedata2$date)+5
) %>% 
  select(date, index=adjusted) %>%
  full_join(
    ., 
    tibble(date=seq(first(.$date), last(.$date), by='days')),
    by="date"
  ) %>% arrange(date) %>% fill(index)

r2k2 %>%
  format.dt.f(page_length = 10, perc_vars=NA, ron_vars=c("index"))

3 Code

3.1 irr.f

irr.f=function(x, gips=FALSE) {
  
  # The IRR is a financial metric that presents a variety of problems in practical 
  # implementation: https://rpubs.com/kpolen/18826
  
  # There are many contexts, for example in private equity and real estate 
  # partnerships, where legal documents require the calculation of IRRs. For 
  # example, an investment manager might receive enhanced compensation in the
  # form of an increased share of profits above a certain IRR. Or investors in 
  # a private placement of equity shares might be compelled in a registration 
  # rights agreement to sell a portion of their shares in an IPO, provided that
  # a threshold IRR has been met.
  
  # The problem is which IRR to use if there is more than one. Well crafted legal
  # documents will address this and require that if there is a net profit (the 
  # sum of the undiscounted cash flows is greater than zero), the lowest positive
  # IRR is used as the IRR. In the case of a loss, the largest negative IRR (the
  # one closest to zero) is used. Generally such contracts require using daily 
  # cash flows for these calculations.
  
  irr.freq=1
  #if("Date"!=class(time(x))) {warning("need Date class for zoo index"); return(NA)}
  if(any(is.na(x))) return(NA)
  if(length(x)<=1) return(NA)
  if(all(x<=0)) return(NA)
  if(all(x>=0)) return(NA)
  if(sum(x)==0) return (0)
  npv.f=function(i,x,freq,tdiff) {
    d=(1+(i/freq))^tdiff
    sum(x/d)
  }
  if(!is.zoo(x)) {
    timediff=-1+1:length(x)
  } else {
    timeline=time(x)
    timediff=as.numeric(timeline-timeline[1])
    if ("Date"== class(timeline)) irr.freq=365
  }
  if (sum(x)<0) {
    rangehi=0
    rangelo=-.01
    i=0
    # low range on search for negative IRR is -100%
    while(i<100&(sign(npv.f(rangehi,x,irr.freq,timediff))==sign(npv.f(rangelo,x,irr.freq,timediff)))) {
      rangehi=rangelo
      rangelo=rangelo-.01
      i=i+1
    }} else {
      rangehi=.01
      rangelo=0
      i=0
      # while hi range on search for positive IRR is 100,000%
      while(i<100000&(sign(npv.f(rangehi,x,irr.freq,timediff))==sign(npv.f(rangelo,x,irr.freq,timediff)))) {
        rangelo=rangehi
        rangehi=rangehi+.01
        i=i+1
      }}
  npv1=npv.f(rangelo,x,irr.freq,timediff)
  npv2=npv.f(rangehi,x,irr.freq,timediff)
  if (sign(npv1)==sign(npv2)) return(NA)
  cf_n=as.numeric(x)
  #calculate with uniroot if cash flow starts negative and ends positive otherwise do your own search
  if((cf_n[1]<0)&(cf_n[length(cf_n)]>0)) {
    ans=uniroot(npv.f,c(rangelo,rangehi),x=x,freq=irr.freq,tdiff=timediff)
    apr=ans$root } else {
      int1=rangelo
      int2=rangehi
      for (i in 1:40) {
        inta=mean(c(int1,int2))
        npva=npv.f(inta,x,irr.freq,timediff)
        if(sign(npva)==sign(npv1)) {
          int1=inta
          npv1=npva
        } else {
          int2=inta
          npv2=npva
        }}
      apr=mean(int1,int2)  
    }
  # convert IRR to compounding at irr.freq interval
  out=((1+(apr/irr.freq))^irr.freq)-1
  # convert IRR to GIPS compliant if requested
  if (gips) {
    if(x[1]==0)  x=x[-1]
    dur=index(x)[length(x)]-index(x)[1]
    if(dur<irr.freq) out=(1+out)^((as.numeric(dur))/irr.freq)-1
  }
  return (out)
}

3.2 pe.performance.f

pe.performance.f=function(dat) {
  # KSPME and Direct Alpha supplement the traditional private equity
  # performance measures with mathematically rigourous methods 
  # consistent with the CAPM framework.  KSPME provides a measure of
  # how much additional wealth is gained with a private equity
  # measurement.  Direct Alpha tells you the rate at which the 
  # additional wealth is accumulated.
  # https://rpubs.com/kpolen/16062
  out=list()
  # TVPI is "total value as a percent of invested" and calculates the 
  # "total value" as the sum of distributions plus the value of the 
  # unrealized assets still owned by the partnership divided, again, 
  # by the sum of the capital calls.
  out$tvpi=sum(dat$value[dat$value>0])/-sum(dat$value[dat$value<0])
  x_c=subset(dat,dat$type=='C')
  x_v=subset(dat,dat$type=='V')
  
  # DPI stands for distributions as a percent of invested and is 
  # calculated as the sum of distributions received divided by the 
  # sum of capital contributions made.
  out$dpi=sum(x_c$value[x_c$value>0])/-sum(x_c$value[x_c$value<0])
  x_cz=zoo(x_c$value,x_c$date)
  x_vz=zoo(x_v$value,x_v$date)
  lastinvec=function(x) {x[length(x)]}
  sum.f=function(x,...) {
    mt=merge(x,...,fill=0)
    zoo(rowSums(mt),time(mt))
  }
  x_all=sum.f(x_cz,x_vz)
  
  # IRR is the internal rate of return, customarily expressed as an
  # annual interest rate, calculated on the cash flows with the capital
  # calls expressed as negative numbers and including the final cash 
  # flow as part of the time series for the calculation. The IRR 
  # calculation presents some technical challenges in a production 
  # environment that Karl discussed in "Calculating IRRs 
  # for legal purposes and GIPS compliant financial reporting":
  # https://rpubs.com/kpolen/18826
  out$irr=irr.f(x_all)
  x_ciz=zoo(x_c$index,x_c$date)
  x_viz=zoo(x_v$index,x_v$date)
  x_imat=merge(x_ciz,x_viz,fill=0)
  x_ind=zoo(apply(x_imat,1,max),time(x_imat))
  # lastinvec=function(x) {x[length(x)]}
  fvfactor=(as.numeric(lastinvec(x_ind)))/x_ind
  x_fv=x_all*fvfactor
  
  # Private equity performance as a "public market equivalent".
  # KSPME is calculated as ratio of benefits to costs and a value 
  # greater than 1 indicates performance above the opportunity
  # cost benchmark. It is a wealth measure reflecting how much 
  # extra money you have by investing in the private equity fund 
  # compared to an alternative public markets investment. It has 
  # some nice mathematical properties that allow it to be related 
  # to the capital asset pricing model.  Gredil, Griffiths and 
  # Stucke have published an article that explores this relationship
  # and proposes an additional metric, called "Direct Alpha", 
  # which is a measure expressed as an annual percent of 
  # outperformance of a private equity investment compared 
  # to a public market benchmark: 
  # https://papers.ssrn.com/sol3/papers.cfm?abstract_id=2403521
  out$kspme=(sum(x_fv[x_fv>0]))/-sum(x_fv[x_fv<0])
  # It is calculated as the IRR of a time series constructed by 
  # combining the future value adjusted calls (as negative numbers), 
  # distributions and the final value.  So, it uses exactly the 
  # same data as KSPME but with different calculations to reduce 
  # the data to a single measurement.  To be consistent with the
  # CAPM formulation, Direct Alpha is presented as a continuously
  # compounded return.
  out$direct.alpha=log(1+irr.f(x_fv))
  
  # If you have an IRR of a private equity investment calculated as
  # a discrete annual return, you can then calculate an IRRd
  # that you would have earned by investing in a market index with 
  # the same timing of investments and withdrawals as the calls and 
  # distributions of the private equity investment.  The below 
  # formulation presumes you want IRRd to be a discrete annual amount
  # for compatibility with the traditional IRR (out$irr).
  
  out$ind.irr=-1+exp(log(1+out$irr)-out$direct.alpha)
  return(out)
}

4 Methods

pedata2 <- pedata2 %>%
  left_join(r2k2, by = "date")

out <- pedata2 %>%
  group_split(fund) %>% 
  set_names(pull(group_keys(pedata2, fund))) %>%
  map_dfr(pe.performance.f, .id="portfolio")

out %>%
  knitr::kable()
portfolio tvpi dpi irr kspme direct.alpha ind.irr
Fund 1 1.11 0.187 0.039 0.681 -0.130 0.182
Fund 2 2.40 0.779 0.626 1.883 0.351 0.145
Fund 3 1.80 0.958 0.268 1.334 0.120 0.125
Fund 4 1.27 0.353 0.071 0.846 -0.049 0.125
Total 1.57 0.553 0.175 1.083 0.029 0.142
pmepal <- brewer.pal(n=10,name='Spectral')[c(1,3,8,9,10)]
# palette(pmepal)
pmecut <- cut(out$kspme,c(0,.8,.95,1.05,1.2,100),labels=FALSE)

ggplot(out,aes(x=ind.irr, y=irr))+
  geom_text(label=out$portfolio, colour=pmecut)+
  xlab("Market Return")+
  ylab("Fund IRR")+
  geom_abline(intercept=0,slope=1)+
  ggtitle("Private Equity Performance")+
  annotate("rect", ymin=.47, ymax=.63, xmin=.169, xmax=.181, fill='grey80')+
  annotate("text", y=.61, x=.175, label="PME > 1.2", colour=pmepal[5], size=3)+
  annotate("text", y=.58, x=.175, label="1.05 < PME < 1.2", colour=pmepal[4], size=3)+
  annotate("text", y=.55, x=.175, label=".95 < PME < 1.05", colour=pmepal[3], size=3)+
  annotate("text", y=.52, x=.175, label=".8 < PME < 1.05", colour=pmepal[2], size=3)+
  annotate("text", y=.49, x=.175, label="PME < .8", colour=pmepal[1], size=3)