Video Overview:

I made this template as a request from a client, and this was the prompt for the design behind this financial tracking spreadsheet:

“I want to be able to track and report what the preferred return per investor will be based on when the investor made their deposit and based on their amount. I run a preferred return fund.”

The native version allows for tracking of up to 30 fund investors (this can be expanded, and you can see how at the end of the instructional video above). The maximum tracking period for an investor is 15 years, and the entire fund will show data for up to a 20-year period.

As soon as the investor deposits, that begins their preferred return accrual. Whenever there are distributions (option to input monthly), the cash will be split at the defined rate for the preferred return hurdle for the investor. This may be 100%/0% (hard preferred equity), or it may be pari passu or something else. The user can define this split rate for the GP/LP accordingly.

After the preferred return hurdle rate is achieved, there are up to two more IRR hurdles that can be configured if so desired and the resulting cash distribution rates therein. It will depend on how the operating agreement is structured as to how these will be configured.

My favorite part about this Excel tool is the cash flow planning tab. On that, the fund manager can define when and how much cash is deployed (used to invest in things), resulting profits, and resulting distributions.

Notice how profits and distributions are separated. This is because the operator has the option to re-invest profits / pay distributions, or a combination of both. This tab will track the funds’ running cash balance in order to make sure distributions are not greater than the actual cash available to distribute.

Even when there are no monthly payouts, the tracker will calculate how much the preferred return *interest* compounds by in each period, and all distributions will go to satisfy that first hurdle rate.

There is an option for the GP / fund operator to contribute capital as well.

Primary Model Inputs:
– Investor Name
– Date Invested (inclusive)
– End Month (Exclusive) – Max 180 Periods
– Annual Preferred Return (1st IRR Hurdle)
– LP Investment Amount
– LP Investment Rate
– GP Investment Amount
– GP Investment Rate
– IRR Hurdle 2
– IRR Hurdle 3
– LP Share Until 1st Hurdle
– LP Demote 1
– LP Demote 2
– LP Demote 3
– Compound Period (monthly = 12, quarterly = 4, annual = 1)

Primary Model Outputs:
– Final Exit IRR per Investor
– Fund Operator IRR
– Monthly and Annual cash flow per investor and fund operator
– Individual investor summary tab with a visual of contributions / distributions
– Each of the 30 investor slots has its own cash flow waterfall tab that shows exactly how the returns accrued / calculated / distributed
– DCF Analysis for each individual investor