Major Model Meltdown
Hi I run a PE Fund and I am having a heck of a time calculating carried interest. I know what carried interest is obviously but where my problems lie is that I cannot seem to reconcile the data I am provided from my various plan sponsors into an excel model I inherited from an ace modeler/accountant type. The purpose of calculating or I should say guesstimating carried interest is that my plan sponsors so firms like Georgian Capital only report Partner Capital Accounts to me quarterly though I provide NAVs to my unitholders monthly. In between quarters I have to "guess" carried interest but as I say I am having a heck of a time on this. I hope someone on this forum is brighter than me...feel free to pm me or hit me at joey 81325 at g mail dot com thanks!
Just expanding on my first post: I get a quarterly report from Georgian Capital. A PE Firm in Canada. They provide me with a summary of portfolio companies including the name, date and cost of the purchase at the start of the quarter [I note that with many of these portfolio companies you have the PE Firm making an equity (obviously) investment in some class of common or pref shares and then subsequent tranches of investment over time]. In a fourth column I get "total cost" which is ostensibly the identical figure as the Cost-at-the-start-of-the-quarter column unless, obviously, the PE firm ponies up more money intra the quarter. So then things get weird very quickly...the next six columns are: Unrealized Q1, 17 Valuation, Realized Q1, 17 Valuation, Unrealized Q2, 17 valuation and Realized Q2, 17 valuation, a total column [i.e. Realized and Unrealized Valuation for Q2, 17 and a column I have no idea about called MOI. I assume this is Multiple On Investment but have yet to have it confirmed. So that's the PE Funds investment summary. Next I have the PE Funds own financials so b/s operations statement, statement of CF, notes to financials and then my own PE Funds Partner Capital Statement [i.e. the statement that shows how much my investment in the Georgian PE Fund I held as at the last quarter].
Finishing off this triumvirate posting I turn to the monstrosity of a spreadsheet my "mad scientist" predecessor left me with. The behemoth begins with a column called "Name" hey that's easy peasy...he basically data entry the individual portfolio company names. Next column is something called "Remaining Book Value" [absolutely no clue what this means but if you put a gun to my head it would be the portion of the portfolio companies the PE firm still has on their books and has not "spun off" or sold right], the there are two columns called Valuation and Estimated Exit Values. Valuations - you'd think - were right off the PE Firm's statement but I cannot for the life of me reconcile the figures. I did read and write down the following statement "Carrying value factors unrealized remaining while carry cost factors reazlied and unrealized". Wild guess is that this statement tries to articulate that the column "Remaining Book" is whatever is left on the PE Firms books of the portfolio companies but it doesn't add up. Valuation columns I have no clue on and estimated exit value looks like he made it up...doesn't appear to matter though. Further down a few rows the column values are aggregated and then multiplied by a factor [the fact of how much of the PE Firms fund I own in my fund...13%]. I will add a fourth post and for whoever is reading this THANKS YOU in advance...this model is really ticking me off.
A lot to digest here...I'll come back to this thread when I have more time.
In short, I'll take care of a few of the easy ones. Instead of "Remaining Book Value" we typically show "Remaining Cost" in a similar fund spreadsheet we keep at my firm. Remaining Cost is calculated by taking the sum of your investments in the asset, less any realizations or distributions made back to the fund, e.g., if you paid $50 upfront, an additional $20 over your hold, and have returned $10 to the fund, your remaining cost is $60.
Amounts in a valuation column are likely going to be an input and not necessarily tie to any items on the respective companies' financial statements. From my experience, this would be where you input the fund's view of the current value of the portfolio companies if the assets were sold at that point in time. Given everything else is historical (i.e., your remaining cost), this is where you drive the "mark" of each investment. Say you have an asset that does $50 of EBITDA, has $100 debt and $10 cash, and is marked at 6x LTM. Your EV would be $300 and equity value would be $210. Assuming full ownership and no fees, the "valuation" in this spreadsheet would be this $210. Typically, what I've seen is that this net equity value to the fund (at changing valuation levels over time, whether it be quarterly, semi-annually, etc.) is going to be your ending "Book Value" for any period. In between your beginning and ending book value you may have a "value adjustment" type column that bridges the gap between the two numbers. An example of this is say at Q1 you mark your equity investment in an asset at $100 but by Q2 your mark goes up due to improving LTM performance to $120 (could be due to keeping the same multiple and applying to new LTM EBITDA) - in this case you'd have $100 book value in the first period and $120 in the second period, with $20 of value adjustments. This can obviously be made a bit more complicated when you start adding columns for things like cash adjustments, etc. between book value columns.
From the sounds of the sheets you're describing, it seems that you'll need to do a few more calculations to reach carry numbers. I also don't know how it's determined at your fund. IRR calculations should be obvious (use XIRR with all the inflows/outflows of each investment). MOIC should be the sum of your current book value (i.e., equity "mark" valuation) and any previous distributions divided by total invested capital (numerator and denominator should be adjusted for any re-callable capital per the guidance from your fund agreement).
Necessitatibus sed in ut voluptatem. Dolor alias occaecati sed impedit et. Quisquam nemo voluptatem dolorum nihil labore excepturi molestiae. Saepe nihil non deleniti nisi magnam in.
Repellendus aut dolorem quae quis. Sunt asperiores quisquam enim maxime. Libero cum laudantium distinctio enim velit officiis. Voluptas voluptatibus accusantium reprehenderit eaque ducimus enim.
Libero vero modi ab aspernatur et nemo est. Nihil ex praesentium voluptas unde qui vero facere illo. Eum ullam modi aliquid reprehenderit. Recusandae commodi vitae velit quasi consequatur eum.
Culpa ducimus atque nobis. Consequatur consequatur inventore et exercitationem. Enim optio nam est eos fugit. Debitis molestiae totam animi. Doloribus rerum aut nobis exercitationem omnis expedita. Repudiandae aut adipisci veritatis maiores. Minima aut nihil dolorem est quae ut ut.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...