Fund Return Calculation

You will be requested to calculate the expected return on a venture fund given specific parameters and assumptions. The deliverable is an excel sheet model and a 1-2 page document with the description of your analysis.

Assumptions

The management fee is calculated on total committed capital, not remaining capital. 2.5% annual management fee, with decreasing amount after 7 years:
7th = 2.25%

8th =2%

9th =1.75%

10th=1.5%

Fund expenses: include legal, accounting and back-office, audit, taxes, diligence expenses.

For a 10M fund,

Legal = 20K/year
Back Office= 40K/year
Audit = 25K/year
Taxes = 1K/year
Diligence Expenses = 14K/year

Waterfall:
Up to 3x, 80/20
>3x and <6x, 75/25
6x+, 70/30

Construct a portfolio and financial model to find a scenario for a
FUND SIZE: $50M
Target  of portfolio companies: 30
WHERE:
LP gets 3x return for his/her committed capital
(ignore GP 1%)