Venture Capital Calculations
November 9th, 2006
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.
It should go without saying if you download a Microsoft Excel file from my website, upload it to another website and claim it as your own work, I will find out, and I will take action.
After completing my MBA studies at the Sauder School of Business I somehow ended up in the CFA® Program, I even managed to successfully pass all the exams. You can view my resume if you are curious.
Recently on Twitter was a post about valuing startups 9 different ways. That is six more ways than I did in the spreadsheet I made. However the custom Microsoft Excel spreadsheet complete with VBA code is available to download above, just do not claim it as your intellectual property or resell it, doubly so if you are a CFA Candidate or Charterholder.
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.
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 for cells to make it hopefully easy to understand. The spreadsheet makes use of a few tips and tricks from the book “Analyzing Business Data with Excel“. One convention I use is cells with green backgrounds can be changed 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 with a linear growth in market share
- Break-even: 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 with 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. 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 break-even analysis to do it properly you need a more detailed cost structure and likely a monthly model. I eventually made several more spreadsheets that are of use to entrepreneurs and would-be venture capitalists. You can of course leave questions and comments below.
This entry was originaly posted on , it was last edited on and is filed under: Finance and tagged: Microsoft Excel, Spreadsheet, Valuation, Venture Capital.
Would you be able to share the valuation model. Doesnt seem to be working when I try and click the link
You had me worried. This post is quite old, 18 years old in fact. But I clicked the link “download my spreadsheet” and it worked. My webhost got sold again and WordPress has been updated many times, but I put that file in a folder which I then allowed people to download from, it was an old school solution. Now WordPress probably lets you upload files and stores them natively. Anyway try the second link in the article. I’ll open the file, but honestly after 18 years, there was never any warranty, but it should still be accurate math.
It still seems to work, it still requires macros AKA VBA. I don’t know if I could build this today, haven’t used VBA in a long time. People might use Python for this, but some people always want everything in Excel and don’t want to learn another tool.
Hi, I’m just a Malaysian international student studying at UofT right now. Just wanted to say thank you for your work. I know t’s almost 20 years after you posted this, but I’m still using this for a project I’m working on, so just wanted to say thanks :)
P.S. I like the blog too, btw.
I’m glad the spreadsheet still works. I have not tested it with the latest version of Microsoft Excel on my laptop. I used to be better at Excel and VBA, now I use other tools. I think some of the other spreadsheets I made and gave away were more work, but keeping them and this blog online for not 20 years, but probably close to 15 now did take effort.
I do not think my blog ranks as well in Google as before. I don’t really have much time or effort for blogging these days, but hopefully after I finish yet another certification exam, I’ll have more time and energy.
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.
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 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 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.
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 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.