Categorized | the i.t. files

The spreadsheet formulas every entrepreneur should know, Part One

Pen and Excel bar graph
The Series
1.) Internal Rate of Return
1a.) Modified Internal Rate of Return
2.) Net present value
3.) Payback
4.) Average
5.) Standard deviation
Bonus!) Excel Solver

If you’re like most entrepreneurs or business owners, you spend a fair amount of your productive poking away at some spreadsheet or other, but your demands of your spreadsheet program rarely go beyond its ledger or recording abilities. You probably avoid the math functions like they were a slab of maggoty meat and have no idea how much easy-to-use math has been packed into your spreadsheet program. That, after all, is why spreadsheets were invented 30 years ago: to crunch numbers on the fly.

From VisiCalc in the 80′s to the free ThinkOffice in the late 2000′s, spreadsheets were designed to allow you to enter values and, if you’ve specified these values to be calculated, the spreadsheet would automatically spit out ratios, averages, financial calculations, and even linear programming numbers. On the fly. Change a value in the spreadsheet, shazam!, the calculated totals change. What makes a spreadsheet a spreadsheet, not just a handy table maker, then, are the math formulas that you can specify for various cells. Here’s the thing. Even if you have a congenital aversion to math, spreadsheets are designed to make setting up mathematical formulas no more difficult than specifying the variables. You don’t really need to understand the math, just how to put the variables in.

So over the next week, we offer up a simples series of tutorials on how to use the math functions that everyone involved in business — particularly business owners and entrepreneurs — should know.

We’ll start by simpling listing the formulas we’ll be covering — a kind of quick pass of the smorgasbord. After the jump . . .

Internal Rate of Return
The Internal Rate of Return (IRR) is a fundamental calculation for determining the profitability of an investment. Every entrepreneur putting together even rudimentary financial projections should have an IRR formula lurking in some cell. IRRs are the bread and butter of venture capital or investor presentations. Even if you’re not looking for funding, failing to calculate an IRR can be fatal in planning a future business or project.

While mathematically the internal rate of return is a fairly complex formula, the basic concept is simple: it is the internal, annualized return on a given set of investments. It is not an average return (which is called the Average Accounting Return), but includes compounding and inflation. While mathematically complex, it is magnificently simple to set up an IRR spreadsheet formula, as you’ll find out in our next post — we start our tutorial with IRR!

Net present value
Venture capitalists and similar investors rely heavily on IRR to determine the soundness of an investment, but most finance professionals prefer Present Value or Net Present Value — and it’s not uncommon for VC’s or other investors to rely on NPV rather than an IRR. Any time you’re preparing prospective financials, a slot on your spreadsheet for NPV calculations is indispensible.

The net present value evaluates the return on an investment relative to a minimum compounded rate and tells you how much you how much money (in dollars) you’ll make over that minimum rate. What makes NPV such a powerful tool is that it relates $1 in investment today with a $1 in investment years from now. Typically, a business investment involves an immediate outlay of cash, while the returns come in year after year — after inflation has eroded the value of the dollar. (For instance, if I borrowed $10,000 from you today and offered to repay you $10,000 tomorrow or repay you $10,200 one year from now, what’s the better deal? If you figure in inflation and calculate an NPV, you’ll find the value of the first option is $10,000 but the value of the second option, because inflation erodes the value of the dollar, is actually closer to $9,900 — the first option is the better investment, no?) In addition, the immediate investment is certain in its amount, but the returns are just guesses (and less reliable the farther you go out). Projected returns are always risky, and NPV takes this into account by factoring in a minimum rate.

While you may imperfectly understand NPV and may never get a hankering for the underlying complex math (it can take a few minutes to calculate an NPV on a financial calculator), setting up a spreadsheet function is, like the IRR, unbelievably easy. And it should be part of every single spreadsheet calculating investment and returns.

Calculating the payback period (PBP) is a standard, everyday financial function that entrepreneurs, small businesses, and major corporations use all the time, even though it has limited use and reliability. A Payback Period calculation simply tells you at what point in time your investment will turn positive, when the sum total of what you’ve invested and what you’ve earned in returns is greater than zero. But, for some reason, Excel does not offer a simple formula to calculate a Payback Period, but that doesn’t mean it can’t be done. We’ll walk you through a fairly involved but accurate way to calculate your payback period (but, once you do all this rigamarole, you’ll be able to find it in the spreadsheet anyway).

Most accounting programs will calculate the various types of depreciation (straight-line depreciation, double-declining balance, sum-of-year’s digits) unless you’re on a fairly inexpensive or cheap accounting software (such as Microsoft Money). In that case, most spreadsheets offer the full range of depreciation calculations for individual years. To set up a year-by-year depreciation schedule, though, will take some work.

From finance to operations to project management, finding the “center” of a set of values is essential to evaluating your success as a business. Averages and other measures of center, such as mean and mode, are relatively easy to calculate, but if you’re storing values in a spreadsheet — say the hours you’re employees are working on individual projects — calculating averages (or means or modes) should be built into every spreadsheet.

Standard deviation
Averages tell you very little; the most powerful statistical tools you have for evaluating performance is standard deviation and variance. Whether you’re trying to control project resources, employee productivity, the effectiveness of an outsourced call center, the quality of your manufacturing, or simply how well your Web site is performing, standard deviation and variance gives you the fullest picture of how well your business is performing in one aspect or another. In general, but not always, you strive as a manager to improve averages and tighten standard deviations (Six Sigma, for instance, is all about tightening standard deviations so that there’s almost no variance around the average).

And one advanced topic: linear programming
Now it sounds pretty daunting, this “linear programming” thingamajig. The kind of thing MBA’s do to justify their existence on this earth. But for a small subset of entrepreneurs, generally those who make things, linear programming is a far more useful and relevant tool than it is for the big factories that use it. In the simplest possible terms, a linear programming calculation tells you how to most profitably use limited resources. For instance, in a manufacturing example, your startup XYZ makes two products, A and B. They both require machine C. If you know how much marginal profit you make on each product and how much time it takes to make each product on machine C, then you can calculate exactly how many A’s and how many B’s you should make each day to maximize profits (or revenues or whatever). A perfect example are spinners who manufacture custom dyed yarns from their garage or back room. Every product they make requires certain “machines” and delivers greater or lesser profits. What’s the best combination of products from a profitability perspective? That’s what a linear programming calculation tells you.

But you don’t need to go there now. Wait a few days and you’ll have enough linear programming to last you a lifetime. But first, we turn to the financial tools every spreadsheet comes with, starting with the internal rate of return . . .

Be Sociable, Share!

5 Responses to “The spreadsheet formulas every entrepreneur should know, Part One”


  1. [...] Formulas Every Entrepreneur Should Know, Part Three: Modified Internal Rate of Return The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback [...]

  2. [...] Five Spreadsheet Formulas Every Entrepreneur Should Know: Internal Rate of Return The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback [...]

  3. [...] spreadsheet formulas every entrepreneur should know, part four: Net Present Value The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback [...]

  4. [...] spreadsheet formulas every entrepreneur should know, Part 5: What is payback? The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback [...]

  5. [...] spreadsheet formulas every entrepreneur should know, Part 5: Calculating Payback The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback [...]

Leave a Reply

Shoestring Book Reviews

Shoestring Venture Reviews
Richard Hooker on Jim Blasingame

Shoestring Fans and Followers



Business Book: How to Start a Business

Shoestring Book

Shoestring Venture in iTunes Store

Shoestring Venture - Steve Monas & Richard Hooker

Shoestring Kindle Version # 1 for e-Commerce, # 1 for Small Business, # 1 for Startup 99 cents

Business Book – Shoestring Venture: The Startup Bible

Shoestring Book Reviews

Shoestring Venture Reviews

Invesp landing page optimization
Powered By Invesp
Wikio - Top Blogs - Business