Asset Management, Cryptocurrency, Financial Model, Financial Services, Investing, Personal Finance, Stock Market

10-year Financial Projections, 5-year Financial Projections, Accounting, Cap Table, Cash Flow Analysis, Cash Flow Projections, CFO, Controlling, Customer Relationship Management (CRM), Dashboard, DCF Model, Dividends, Excel, Financial Analysis, Financial Model, Financial Modeling, Financial Planning, Financial Reporting, Investment Multiple, Investor Cash Flows, IRR (Internal Rate of Return), NPV (Net Present Value), Performance Tracking, Personal Finance, Private Equity, Startup Financial Models, Venture Capital

Video Overview:

At a high level, this Excel template is designed for smaller long-only funds that need to track positions / holdings value and distributions of stock and/or crypto positions over time and have distributions being made to investors (up to 20). You can expand this with a little formula work, and Excel can probably handle up to 200 without any issues. This was designed for positions that are held for a month or longer on average and wouldn’t fit very well for day trading.

As a fund manager, it is important to see an overview summary of performance, and this template will allow for that based on the data entry. One could also use this as their own personal portfolio management tool if so desired. The template will track data on a monthly and annual basis for up to 20 years. Over 50 assets can be tracked at any one time. This is another configuration that could be expanded fairly easily if needed.

There is functionality to track dividend distributions as well. This could be from stocks or crypto staking as well.

In general, the flow is as follows:
1. Enter client names
2. Enter deposits and withdrawals on a single database
3. Enter position entries and exits based on units, entry/exit price, and any relevant fees
4. Determine if there is a management fee (if so, it will accrue, and distributions will be used to pay this off before profits are distributed).
5. Determine the share of profits after fees (there are IRR hurdles to allow for more advanced distribution terms for investors).

Based on the above entries, you will be able to see all distributions to fund members based on their equity deposits/withdrawals relative to other members (profits split on a pro-rata basis) as well as distributions to the fund itself. The holdings value of all positions over time per month-end prices will also display.

In order to handle the proper cost of units sold accounting when positions are opened and closed over time, a bundle system was implemented. This means you can have multiple entries into a position, but as soon as you start selling the position, the next buy entry must be grouped into a new ‘bundle’. There are up to 40 bundles that can exist for any one asset (up to 50 assets). This gives accurate profit calculations when selling based on the average cost per unit of each bundle.

Any dividends can all live in a single bundle category, and the user can define that as any of the 40 bundles. In order to better manage this, an ‘inventory’ tab exists that will show the current outstanding shares/units that exist for each bundle based on all existing data as well as the cost basis of each.

IRR calculations are shown by members as well as the investor pool in the aggregate, which could vary depending on the timing of deposits/withdrawals.

The model assumes all distributions are ‘sent’ to members, so if they were to reinvest, simply enter a new deposit of their distribution amount.

The instructions in the workbook will help you get started, as well as the video overview.

There is a DCF Analysis for the investor pool as a whole and for the fund operator.