Categorized | tip sheet

The spreadsheet formulas every entrepreneur should know, Part 5: Calculating Payback

Pen and Excel bar graph
The Series
Introduction
1.) Internal Rate of Return
1a.) Modified Internal Rate of Return
2.) Net present value
3.) Payback (What Is Payback?, Spreadsheet Payback, Discounted Payback, Spreadsheet Discounted Payback)
4.) Average
5.) Standard deviation
Bonus!) Excel Solver

As you learned in our previous post, the basic math behind calculating paybacks is to simply run a sum on cash flows until that running sum hits zero. Investments and losses are negative numbers, returns are positive. Whether you’re laying out cash flows by month or year, the running sum will hit zero on some cell or another if your investment does well.

Since the geniuses who designed spreadsheets decided this should be really, really hard to do in a spreadsheet, you must assemble the parts. There is, for instance, no way to just simply insert a formula to calculate the payback period. You have to first sum up each period’s total return. So your spreadsheet not only needs total returns for each period, it needs a running sum of those totals.

You can do it through sheer brute force and run a sum each time you register a return.

Let’s take our series of returns and stick it into a table. We’ll add to that table a column with a running sum by adding a sum function that adds the year’s return to the last year’s sum:

Year 0 1 2 3 4 5
Return -10,000 -1,000 1,000 3,000 9,000 45,000
Total Return -10,000 -11,000 -10,000 -7,000 1,000 46,000

You can see instantly that the progressive sum turns positive in year 4 (that was hard).

Do you see? At the end of year 4, you have a postive total return ($1,000). At the end of year 3, you have a negative total return. So, sometime between the start and end of year 4, your investment “paid off.”

But what if you wanted the spreadsheet to do the really, really hard work of identifying year 4 as the year in which cash flow went positive (you lazy bum!). In that case, you will want the spreadsheet to count the cells to the last negative sum of the cash flows and add one more cell (to identify the year in which cash flows turned positive). The Excel function you use to do this is the COUNTIF function (count the cells if a certain parameter is true), in this case, count the cells if the running sum (C1 to C6) is negative and then add the next cell:

=COUNTIF(E5:Q5;”<0")+1

That will tell us how many cells to count to find the positive accumulated cash flow. But you can do this by sight. You then identify the year by using the spreadsheet’s index function – an index formula simply spits back a cell’s contents relative to certain parameters – in this case, we’re looking for the year number (Year 0 to Year 5) corresponding to the cell one greater than the last negative accumulated cash flow:

=INDEX(A1:A6;COUNTIF(C1:C6;”<0")+1

Since cells A1 to A6 contain the year names starting with Year 0 and ending with Year 5, this formula simply says “give the contents of the year cell that corresponds to the cell one greater than the one with the last negative accumulated cash flow sum.”

See?

With the cash flows above, this formula will display “Year 4” (of course, to make this work, the cell number must match the year number).

But this isn’t very precise.
What if you wanted to know when during the fourth year the cash flows turn positive (we found above that it was 4.9 years by doing the math ourselves). In that case, you need to identify the year in which the cash flows turn positive and divide that year’s cash flow by the accumulated cash flow, which will give you the fraction of the year in which the cash flow turned positive.

I’m willing to bet you didn’t follow that. Check it out:

In year 4, the accumulated cash flow turns positive ($1,000) when the year’s cash flow goes way up ($9,000). Divide $1,000 by $9,000 and you get 0.11. That’s the amount of time in year 4 in which cash flow was positive, which means that cash flow was negative for 0.89 years during year 4. That’s 4.9 years.

Okay, this ain’t easy so I ain’t going to explain it. To do this in your spreadsheet requires that you identify the year in which cash flows turn positive using the COUNTIF formula I outlined above. You then need to divide that year’s accumulated cash flow by the year’s cash flow using the spreadsheet’s index function to find that value. Forget it. Just copy and paste this formula which does exactly that:

=COUNTIF(C1:C6;”<0")+1-INDEX(C1:C6;COUNTIF(C1:C6;"<0")+1)/INDEX(B1:B6;COUNTIF(C1:C6;"<0")+1)

Cells C1 to C6 are the accumulated cash flows in the table above. Cells B1 to B6 are each year’s individual cash flows. Do you see? The COUNTIF formula always has the full range of accumulated cash flows in your spreadsheet (the running sum). The final INDEX function – which discovers the full cash flow for the year in which cash flows turn positive – covers the entire range of individual cash flows in your spreadsheet.

In the table above, that formula will automatically spit back “4.89.” So 4.89 years is the payback period.

Be Sociable, Share!

Leave a Reply

Shoestring Book Reviews

Shoestring Venture Reviews
Richard Hooker on Jim Blasingame

Shoestring Fans and Followers


Categories

Archives

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