Portfolio Simulator

import tabulate

data = [
        [2004,1.69,1.72,1.20,1.82,0.82,63],
        [2005,1.68,1.66,1.41,2.07,0.91,61],
        [2006,1.69,1.56,1.59,1.95,0.75,78],
        [2007,2.29,2.38,1.76,2.93,1.31,68],
        [2008,1.77,1.71,1.40,2.21,1.09,64],
        [2009,2.10,2.12,1.75,2.46,1.30,23],
        [2010,3.21,2.80,2.12,3.41,1.41,48],
        [2011,2.48,2.26,1.90,2.71,1.39,44],
        [2012,2.21,2.48,1.73,2.37,1.35,55],
        [2013,2.02,2.01,1.81,2.26,1.34,59],
        ['Average',2.11, 2.07, 1.67, 2.42, 1.17, 56]
]

headers = ['Vintage','Pooled','Mean','Median','Upper Q','Lower Q','Funds']
table = tabulate.tabulate(data, headers=headers)
print(table)

# 1. Import necessary libraries

import numpy as np
import pandas as pd
import tabulate
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker

# 2. Define the investment parameters

np.random.seed(42) # for reproducibility
fund_size = 50e6
fees = 0.12
carry = 0.2
portcos = 24
duration_years = 7
investable_capital = fund_size * (1 - fees)
follow_on_percent = 0.20
primary_investments = investable_capital * (1 - follow_on_percent)
check_size = primary_investments / portcos
number_vcs = 4_000 # approximate number of VCs with more than 20 investments in the USA, according to crunchbase

# 3. Define the probabilities and returns for each outcome


probabilities = [0.59,  # "failed", 
                 0.15,  # "breakeven", 
                 0.15,  # "triples",
                 0.10,  # "homerun",
                 0.01]  #  "unicorn"

# 4. Simulate the returns for the 24 companies 4000 times
df = pd.DataFrame(columns=["multiples", "positions", "total"])

def get_net(x, carry=carry, fund_size=fund_size):
    """ 
    returns x minus carry when appropriate 
    works with numbers or np arrays
    """
    return (np.maximum(x-fund_size, 0) * (1-carry) + np.minimum(x, fund_size))

for _ in range(number_vcs):
    # Define a list of functions for each distribution
    distributions = [
        lambda: 0,  # failed investments return $0
        lambda: np.random.uniform(1, 1.25),  # breakeven return 1x - 1.25x
        lambda: np.random.uniform(2, 4),  # "triples" return 3x - 8x
        lambda: np.random.uniform(12, 20),  # homeruns return 6x - 20x
        lambda: np.random.uniform(50, 175)  # unicorns return 50x - 175x
    ]

    # Sample indices based on probabilities
    indices = np.random.choice(len(distributions), size=24, p=probabilities, replace=True)

    # Call the chosen function for each index to get the actual value
    multiples = np.array([distributions[i]() for i in indices])
    

    positions = multiples * check_size
    total = positions.sum()

    # Append the values to the DataFrame
    new_row = pd.DataFrame({
        "multiples": [multiples],
        "positions": [positions],
        "net_positions": [get_net(positions)],
        "total": [total],
        "net_total": [get_net(total)]
    })

    df = pd.concat([df, new_row], ignore_index=True)
    
df = df.sort_values(by="total")

# 5. Calculate quartiles for the 100 simulations

def get_percentile(df, percentile):
    """ returns percentile from a sorted dataframe """
    return df.iloc[int(df.shape[0]*percentile)]

def get_irr(tvpi, years=duration_years):
    """ returns percentile from an ordered list """
    return (tvpi**(1/years)-1)*100

statistics = {f"{pcnt*100:,.0f}th": get_percentile(df,pcnt)['net_total']
               for pcnt in [0.25,0.50,0.75,0.90,0.95,0.99]}
statistics['Mean'] = df["net_total"].mean()
statistics['Top'] = df["net_total"].max()

# 6. Display the results

data = []
headers = ['Percentile', 'Net Proceeds', 'Net TVPI', f'IRR ({duration_years} yrs)']
for q, net_total in statistics.items():
    tvpi = net_total/fund_size
    irr = get_irr(tvpi)
    data.append([q, f"${net_total / 1_000_000:.0f} M", f"{tvpi:,.2f} x", f"{irr:.2f} %"])

# .INX	01/01/2004 $1,131.13	12/31/2013 $1,848.36
data.append(["S&P500", "N/A", "1.63 x", f"{get_irr(1.63):,.2f} %"])

table = tabulate.tabulate(data, headers=headers)
print(table)

# 7. Visualize the results

plt.figure(figsize=(14, 7))
plt.hist(df['net_total'].tolist(), bins=50, color='blue', alpha=0.7)
for q, val in statistics.items():
    plt.axvline(val, color='red', linestyle='dashed', label=f"{q}: ${val/1e6:,.0f}M")
plt.title(f"Distribution of Returns over {number_vcs} Runs")
plt.xlabel("Net Proceeds")
plt.ylabel("Number of Runs")
plt.legend()

# Custom formatter for x-axis
formatter = ticker.FuncFormatter(lambda x, pos: f"${x/1e6:,.0f}M")
plt.gca().xaxis.set_major_formatter(formatter)

plt.grid(True, which="both", linestyle="--", linewidth=0.5)
plt.show()