Muschamp Rd

Venture Capital Calculations

November 9th, 2006
Microsoft Excel logo

With all my recent readings on Excel I figured I should make something that didn’t have anything to do with my hockey pool. So I decided to make an easy to use spreadsheet for valuing a company, determining how large of investment results in what percentage of ownership, shares to issue, share price, etc.

If you want to skip right to downloading my spreadsheet, go ahead.

Update September 2013: People still download and use the spreadsheets I made over a half dozen years ago, though I never did get a job in venture capital or even finance. I seem to be back to fixing other people’s websites, proving what a waste of time, effort, and money my Sauder MBA was. But if you want to know more about valuing a startup, you should check out this post on Funders & Founders.

If you would like some explanation on valuing a company and selling a portion to investors (especially VCs) I recommend a portion of the Strategis.gc.ca website, particularly this section. My spreadsheet implements the classic VC method as defined in Jeffry Timmons’ book. It now also implements two discounted cash flow valuations one with linear growth and one with exponential growth.

I also implemented a version of the First Chicago Method. My implementation relies on accessing the likelihood of three scenarios and assigning a percentage of the original market size estimate that is to be captured in each scenario.

Excel spreadsheet

The final portion of the spreadsheet contains some simple VBA macros that make use of Goal Seek to determine the requirements to achieve a specific IRR, investment size, and maintain a certain percentage of ownership under the classic VC method.

The spreadsheet makes use of a large number of worksheets and defined names of cells to make it hopefully easy to understand. The spreadsheet makes use of a few tips and tricks from Analyzing Business Data with Excel. One convention I use is cells with green backgrounds can be changed at will to allow for “what if” analysis. Orange cells are changed by VBA macros. Green for go, orange for caution.

The Excel spreadsheet contains the following worksheets:

  • Input: for inputting all the values needed for the classic VC valuation method
  • Valuations: shows the PV, shares to issue, and share price using the four different methods on one worksheet
  • Classic VC: Estimates PV of the company, shares to issue and their price
  • First Chicago: Re-estimates the PV of the company, and recalculates shares to issue and their price.
  • DCF: uses a discounted cash flow calculation and a linear growth in market share
  • BreakEven: this is a simple line graph that shows yearly income and yearly
    fixed costs including the break even point for the linear discounted cash flows
  • DCF2: uses a discounted cash flow calculation and an exponential growth in market share
  • BreakEven2: this is another simple line graph that shows the yearly income and the yearly fixed costs including the break even point for the exponential discounted cash flows
  • Solve for IRR: contains a macro to solve for a particular IRR
  • Solve for Investment: contains a macro to solve for a particular investment size
  • Founder Control: contains a macro to solve for maintaining a certain percentage of the company owned by the founders
  • Workarea: interim calculations

Hopefully this is of use to someone. I also hope the people who make use of it show a little gratitude, something that was not always done by my former classmates who were all too happy to benefit from my effort though they didn’t always express it. I think this is being continued by the next generation of MBA students at Sauder based on some recent web searches that were conducted on Muschamp.ca today. Though one or two students in succeeding classes have written me about my various notes and reading summaries I made available online to try and make a point, at which I failed.

It should be noted there is absolutely no warranty or guarantee with these calculations. I did test them many times and check them by hand with a calculator. I made the spreadsheet using Office for Mac 2004 but it should work on most other versions. There are four macros, most of the spreadsheet will work without them if you are feeling paranoid. The discounted cash flow valuations are done almost entirely with VBA so that portion of the spreadsheet won’t work without macros enabled.

Although I provided some rough breakeven analysis to do it properly you need a more detailed cost structure and likely a monthly model. I may get around to making one but I think I’ll start a new spreadsheet as the current one is now over 200 Kb and has twelve worksheets.


  • I couldn’t leave well enough alone. After some thinking and a lot of looking up of VBA syntax and of course much debugging I implemented a discounted cash flow valuation worksheet.

    It uses a lot more VBA though still relatively few lines, a simplistic linear growth model and it isn’t the best method of valuing a start up, but it can be used for comparison purposes.

    I went back and modified the original posting to reflect the current state of the spreadsheet.

  • I couldn’t leave well enough alone. After some thinking and a lot of looking up of VBA syntax and of course much debugging I implemented a discounted cash flow valuation worksheet.

    It uses a lot more VBA though still relatively few lines, a simplistic linear growth model and it isn’t the best method of valuing a start up, but it can be used for comparison purposes.

    I went back and modified the original posting to reflect the current state of the spreadsheet.

  • There was bound to be a mistake. I thought of it the next day, that is what happens when you work late into the night on something. I fixed the VBA code and altered the output of the Discount Cash Flow calculation as I plan to add some charting and another Discount Cash Flow calculation that has a different growth curve than linear plus some break even analysis if I can get it all to work.

  • There was bound to be a mistake. I thought of it the next day, that is what happens when you work late into the night on something. I fixed the VBA code and altered the output of the Discount Cash Flow calculation as I plan to add some charting and another Discount Cash Flow calculation that has a different growth curve than linear plus some break even analysis if I can get it all to work.

  • I added the non-linear growth model along with some simple graphs that are automatically generated. If any errors are found I will fix them but I think unless I completely redo the spreadsheet to make it even more userfriendly I am done modifying and adding to it.

  • I added the non-linear growth model along with some simple graphs that are automatically generated. If any errors are found I will fix them but I think unless I completely redo the spreadsheet to make it even more userfriendly I am done modifying and adding to it.

  • I added a weighted average to the Valuations worksheet. I also added buttons to control the discounted cash flow valuations as that might be more intuitive. Hopefully all the kinks got worked out.

    The sheets that solve for a particular value still operate by changing the goal cell.

  • I added a weighted average to the Valuations worksheet. I also added buttons to control the discounted cash flow valuations as that might be more intuitive. Hopefully all the kinks got worked out.

    The sheets that solve for a particular value still operate by changing the goal cell.

  • I’ve made some more modifications to this spreadsheet behind the scenes. I probably won’t be working on it much more as I’ve made a new one which uses even more VBA and has a much more elaborate estimate of costs, cash , and discounted cash flows.

  • I’ve made some more modifications to this spreadsheet behind the scenes. I probably won’t be working on it much more as I’ve made a new one which uses even more VBA and has a much more elaborate estimate of costs, cash , and discounted cash flows.

  • Someone wrote a long email which got trapped in my spam filters concerning this blog posting which must do OK in Google for some querries. They wanted to know about residual value which is in the example on strategis.gc.ca but not present in either of the discounted cash flow calculations in this spreadsheet or any spreadsheets I’ve made since.

    This was something of an oversight so I implemented that simple calculation which greatly increases the estimated value of the firm using discounted cash flows. I also discovered and fixed a mistake on the non-linear discount cash flow growth model.

    Hopefully this spreadsheet is more useful and more accurate. No warranty is implied and for a more accurate estimate you will likely have to create your own spreadsheet rather than rely on the estimates I generate using VBA here and elsewhere. The classic VC method is still the most commonly used method of valuing a new high growth venture.

  • Someone wrote a long email which got trapped in my spam filters concerning this blog posting which must do OK in Google for some querries. They wanted to know about residual value which is in the example on strategis.gc.ca but not present in either of the discounted cash flow calculations in this spreadsheet or any spreadsheets I’ve made since.

    This was something of an oversight so I implemented that simple calculation which greatly increases the estimated value of the firm using discounted cash flows. I also discovered and fixed a mistake on the non-linear discount cash flow growth model.

    Hopefully this spreadsheet is more useful and more accurate. No warranty is implied and for a more accurate estimate you will likely have to create your own spreadsheet rather than rely on the estimates I generate using VBA here and elsewhere. The classic VC method is still the most commonly used method of valuing a new high growth venture.

  • Pingback: Muskblog » Blog Archive » The Origin of Brands: A Book Review()

  • Pingback: Muskblog » Blog Archive » Ready to Rocket VEF?()

  • Pingback: Muskblog » Blog Archive » Long overdue keyword referral post()

  • Pingback: 30th post about Venture Capital – Muskblog()

  • Pingback: SYWTOABP 4: How much does it cost to open a brew pub? « B.C. Beer Blog()

  • Pingback: 10 Year Blogiversary – Muskblog()

Posts on Muskblog © Andrew "Muskie" McKay comments not necessarily so...
CFA Institute does not endorse, promote or warrant the accuracy or quality of Muskblog. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.