What does a liquidation preference spreadsheet look like?

November 17, 2008

I’ve had some comments and emails asking if I would publish a liquidation preference spreadsheet.  Basically, when a company is thinking about an M&A deal, the first thing that everyone wants to know is how much money does everyone get from the merger proceeds.

Download Sample Liquidation Preference Spreadsheet

The spreadsheet is fairly straightforward.  You can plug in the deal value (merger proceeds) and spreadsheet automatically figures out exactly how much each founder gets and what the return per share is for each class/series of stock.  The spreadsheet determines if a series of preferred stock should convert based on whether the series would receive more merger proceeds as a holder of preferred stock or common stock.

The spreadsheet also takes into account whether options and warrants should be exercised or not.  For example, if the deal value is too low, the common stock merger consideration price per share may be lower than the exercise price for options.  Then the options will not be exercised.  Whether the options are exercised or not will affect the number of outstanding shares of common stock and therefore, the common stock price per share.

This spreadsheet assumes three rounds of financing with non-participating preferred stock, a couple of tranches of warrants (as a result of a bridge loan or two), and options with various exercise prices.  In my redacting of information in the spreadsheet, I’m sure that there is something broken.

I might post a spreadsheet in the future with more bells and whistles, such as the ability to easily manipulate the formulas for senior preferred and participating/non-participating preferred, as well as graphs to show the merger proceeds per share as aggregate merger proceeds increases. Please don’t expect any support or explanation on how the spreadsheet works. I already told someone who had emailed me that I might consider providing a tutorial if she brought my a decaf soy latte and a Sprinkles cupcake some afternoon.

Comments

  • Deana L Thompson

    I am trying to develop an algorithm for a VC firm that computes at what valuations (based on a merger, let’s say) multiple convertible pref shares will choose to convert (or not). It seems that the decision to convert for a given share class is based on what the other convertible share classes will do – in essence a circular argument.

    Do you have any suggestions for how to get around this?

    Thanks
    Deana

  • http://www.startupcompanylawyer.com Yokum

    @Deana – The spreadsheet will consist of various interdependent (circular) formulas, with if/then statements to determine if options will be exercised or preferred stock will be converted. This is simply the way the spreadsheet works. As long as you have iterations turned on in excel, it should work properly.

  • Deana L Thompson

    Wow, I appreciate the really fast response. I can’t tell if it’s brilliant or overly simplistic. Indeed, i didn’t think about leaving EXCEL in iterative mode but that is probably becasue i was trying to think it through at a different level:

    For each share class, for a given exit valuation, there is probably one configuration (some of the other classes converting and some of the others not converting) will get the best value for this share class. But for some other share class, there is the same issues. Of course, unless the exit value is high enough, so that every one wants to convert, it is not clear that there is a configration that maximizes everyone’s return. In fact, given a fixed valuation, there can’t be. So, it seems that the real question i am asking is “what is it one wants to optimize in deciding who will convert and who will not at any given value?” When I took otpimization theory in college, there was some sort of “objective function” that you tried to optimize, subject to constraints. I guess I don’t know what the objective function is here! Is it to maximize the nuymber of classes that convert?

    I have not mentioned the contract parameters but, to simplify, those that don’t convert get a senior liq pref and share with the others that don’t convert, some of the excess over the sum of the senior liq prefs, subject to a maximum multiple of invested dollars for each class. The classes that convert sinply share what’s left.

    Thanks for any thoughts!
    Deana

  • http://www.startupcompanylawyer.com Yokum

    @Deanna – The objective function is maximize dollars per share. If you program the spreadsheet properly and turn iterations on, it should iterate to determine the amount of return to each series of preferred and the common stock until the merger consideration is fully-allocated. For example, the conversion logic on a participating preferred with a cap is “if preferred receives more as preferred, then it won’t convert, otherwise convert” (assuming all shareholders act rationally to optimize their return). If you write if/then formulas for each series, it really isn’t that mathematically complicated. If you haven’t created or worked with this type of spreadsheet before, then it may be somewhat daunting as it may take some time to understand the math at a conceptual level (due to circular reference) in order to be able to create the spreadsheet.

  • Deana L Thompson

    I’m not trying to model a strategy over time, per se. I am assuming there is an exit value price that has been accepted and it is simply a matter of whether various shares of convertible preferred will convert or not. So, as I see it, what each share class wants to maximize is how many dollars of current value it has if it elects to convert versus how many dollars it has, if it remains as a convert. I don’t see dollars per share as relevant.

    I definitely know how to create complex spreadhsheets and how to use iterations, the latter, a practice I like to avoid when possible becasue it also iterates through any programming errors. But that’s another subject. Suffice it to say, I am willing to use iterative mode for this analysis.

    The point that I am obviously not communicating well is that what a share class comes away with, either if it converts or does not convert, seems to be dependent upon what other share classes it will share with, which depends on their decision to convert or not. So, if there are ten classes of convertible preferred, then I expect there would be 2 to the 10th power different combinations of splitting up a given merger price “pie of, say, $100M. Suppose 5 convert and 5 don’t, suppose 7 convert and 3 don’t. But which one is best? In fact, how do you define “best”?

    Feel free to tell me if I am totally missing the boat here and, if so, how. By the way, I am assuming there are no provisions for automatic conversion or anti-dilution protections. Those would be added in later.

    Thanks again!
    Deana

  • http://www.startupcompanylawyer.com Yokum

    @Deana – We create these spreadsheets all the time where you plug in merger consideration, and the spreadsheet calculates the price per share to each series of preferred and common. The spreadsheet has to iterate because if the preferred converts, the liquidation preference decreases and the number of shares of common increase, which will decrease the price per share to the common. And then the price per share to the common determines whether the preferred will convert, which is a circular formula that needs to iterate. Similarly, a decision to exercise an option will affect the number of shares of common, which will decrease the price per share to the common, which may then affect the decision to exercise. It’s a fairly simple math exercise.

  • Deana L Thompson

    I hear what you are saying but I still feel like I am missing the “objective function” which is to be optimized. What is best for class B might be what is worst for class C. So, in some overall sense, how do you find the best soluton set (such as convert, convert, don’t convert, convert, don’t convert for five series of converts, respecitvely). It would seem that without such an objective function, there are multiple solutions that an iterative spreadheet might find. As just an example, what I am finding is that as the potential exit vlaue declines, a certain series decides not to convert and, when it does that, it helps the other folks who already were sure they should convert and hurts the others that were already sure they would not convert. That sort of thing.

    How do you choose the solution that is, in some sense, best overall. That is what I’m asking.

    Thanks!
    Deana

  • http://www.startupcompanylawyer.com Yokum

    @Deanna – see my previous response. I don’t think I can explain it any clearer without walking you through a sample spreadsheet cell by cell. A spreadsheet can iterate through through multiple circular functions as they are simply if/then statements to determine conversion based on whether you get more or less as preferred or common. There will only be one solution that optimizes for all series of preferred and common assuming that all holders act rationally. There may be corner cases with non-participating preferred or participating preferred with a cap where a particular series may be indifferent between an incremental increase in merger consideration that is best visualized by graphing the result. I’m not sure where you are having the cognitive dissonance. (I may have to get around to writing a post about spreadsheet math and moving this set of comments.)

  • Curious

    @ Deanna & Yokum:

    So, do either you have a recommendation on product that solves this problem? Example spreadsheet or commercially available product? I’d be curious where Deanna came out on this. Did you build your own?

  • Deana

    I am in the process of developing my own. It will be tailored to the most common terms that we see, not attempting to be universal. Though I suspect he shies away from blatant self-promotion, it is my guess that Yokum's firm will develop templates for new clients, and I bet they do a great job. I'm sure he will correct me if I am wrong about that.

  • Deana

    I am in the process of developing my own. It will be tailored to the most common terms that we see, not attempting to be universal. Though I suspect he shies away from blatant self-promotion, it is my guess that Yokum's firm will develop templates for new clients, and I bet they do a great job. I'm sure he will correct me if I am wrong about that.

  • Deana

    I am in the process of developing my own. It will be tailored to the most common terms that we see, not attempting to be universal. Though I suspect he shies away from blatant self-promotion, it is my guess that Yokum's firm will develop templates for new clients, and I bet they do a great job. I'm sure he will correct me if I am wrong about that.

  • http://www.startupcompanylawyer.com Yokum

    @Curious & Deanna – I'll ponder posting a sample spreadsheet.

  • http://www.startupcompanylawyer.com Yokum

    @Curious & Deanna – I'll ponder posting a sample spreadsheet.

  • http://www.startupcompanylawyer.com Yokum

    @Curious & Deanna – I'll ponder posting a sample spreadsheet.

  • Jay_Levitt

    Sure wish I could download that into Excel…

  • http://www.startupcompanylawyer.com Yokum

    You should be able to download the excel file from docstoc by following the link once I fix the settings.

  • http://ralphhaygood.org Ralph Haygood

    Hmm…too bad I'm not in Palo Alto to buy your lattes and cupcakes.

    Rather than ad hoc tutorials, have you considered writing a book? The books I've seen dealing with corporate law and finance are too superficial (for complete newbies, most of whom just want to start small businesses), too general (for MBA types, most of whom will work in big companies), or too technical (for aspiring lawyers). I'd pay good money for a book, preferably an ebook, at the level of this blog.

    Of course, if you know of such a book, I'd be happy to learn of it.

  • http://www.startupcompanylawyer.com Yokum

    The problem with a book is that it would take too long to write the entire thing and publish it. With the blog, I can dribble out a little bit at a time. Perhaps I'll go back and organize/rewrite everything into a book after there is a critical mass of information.

  • http://www.caycon.com Rick O'Hara

    I sure would like to look at the Excel file. Are the settings fixed yet? If so, how do I find the spreadsheet in DocStoc?

  • http://www.startupcompanylawyer.com Yokum

    I changed the document to Scribd, as they are a WSGR client. Let me know if you still have problems downloading.

  • http://www.caycon.com Rick O'Hara

    Never mind! It downloaded fine!

  • http://www.lwyr.info Lawyers Information

    Looks good! This is a big help to first-time entrepreneurs for them to use to understand how participation, preference multiples, and even participation caps will affect distributions and ownership post-money and on exit.

  • kevin Pearl

    Interesting discussion.
    Liquidation preferences can get very complicated and managing up-rounds down rounds, multiples, caps, participation, coupons (interest, PIK), warrants (in the money/ cashless) etc and then working out who gets what, at some future date, is very complex.
    We have an on-line cap table manager http://www.vcmcaptables.com, that automates this whole process.

    Kevin Pearl CEO
    VCM Software

  • kevin Pearl

    Interesting discussion.
    Liquidation preferences can get very complicated and managing up-rounds down rounds, multiples, caps, participation, coupons (interest, PIK), warrants (in the money/ cashless) etc and then working out who gets what, at some future date, is very complex.
    We have an on-line cap table manager http://www.vcmcaptables.com, that automates this whole process.

    Kevin Pearl CEO
    VCM Software