Preferred Return Investment Fund Tracker: Multi-Member

Description
This template was made to track the preferred return earned on one-time investments made by investors. The investors can have varying deposit dates and end dates. As soon as the 'deposit' happens, the return will begin to accrue.

Note, when the term 'preferred return' is used, in this case it means an IRR and includes the return of initial capital plus a compounded return rate. The compounding period can be defined as monthly, quarterly, or annually.

The sheet can handle up to 30 members, but it is expandable (see the end of the video for exactly how to do this). It is mainly a matter of just dragging all the formulas down on each module. Everything is straight forward and easy to follow.

Any fund operator contributions (GP equity contributed) is based on the total investment of each investor and their 'investment rate'. If it is 100%, then the GP will not invest anything, but if rate is below 100%, the GP will have funds contributed.

In order to keep track of the cash position of the fund itself, there is a 'cash flow planning' tool that takes all investor activity into account as well as distributable cash flow (profits), cash deployments (usage of investor money), and distributions.

The month-end cash position is based on the total investor deposits less cash deployed, plus profits, less distributions.

The 'deposit month' means the investor will be eligible for any fund distributions starting that month and the 'end month' means no distributions distributed in that end month will go to the investor. (inclusive/exclusive)


All investors that are 'eligible' to recieve a distribution will have two levels for how cash is split. Since this is based on the LP (investors) getting a preferred return and then splitting cash with the GP thereafter at defined rates, the total cash going to each individual investor waterfall will be based on their investment amount relative to other investors (all investors are 'preferred' so they don't have priority over each other just as long as they are in an 'eligible' month). The second level is their preferred return being met based on all the cash flowing to their individual waterfall. 

Note, I did add the option for two extra preferred return hurdle rates if so desired. If not, simply define the cash flow split after the first IRR hudle as whatever the split is beyond the investor receiving their initial hurdle.

There is an individual tab to lookup any given investor and see their monthly and annual contributions / distributions.

The entire fund can track data for up to a 20 year period and any individual investor can be tracked for up to 15 years.

If an investor is going to have 'add-on' investments that need to be accounted for, you would have to start them as a new investor slot and just mark that in the investor description box for the new row.

Note, when there are periods with no distributions, any eligible investors will still accrue their preferred return due and that will count towards their basis as to what they are owed in order to achieve the hurdle rate that was set.

This template is also included in two bundles:
- All Models Bundle: https://www.eloquens.com/tool/P8Y4TX4v/finance/financial-forecasting-models/financial-models-120-useful-and-usable-logic
- Joint Venture Waterfalls: https://www.eloquens.com/tool/MQJ8h73v/finance/joint-ventures/joint-venture-template-bundle