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
library(readr)
library(dplyr)
library(tidyr)
library(purrr)
library(zoo)
library(lubridate)
library(tidyquant)
library(ggplot2)
library(RColorBrewer)
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"))
# 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"))
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)
}
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)
}
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)