Categorized | tip sheet

The spreadsheet formulas every entrepreneur should know, part four: Net Present Value

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

Since we are tightly focusing on the needs of entrepreneurs rather than standard small business owners, of necessity our epic series on spreadsheet formulas has been ushered in by the most relevant math for determining the viability of a prospective business or project: the internal rate of return, the modified internal rate of return, and net present value. As such, these formulas take the cash flows that you project (or fantasize) for your business in the months and years ahead and give you a neat, tidy number that assigns a specific value to investments required.

The internal rate of return, as we’ve discovered, gives entrepreneurs and prospective equity investors a relatively accurate annual return on the investments made, whether those returns are distributed to the investors regularly (the internal rate of return) or reinvested in the business and distributed in a lump sum at the end of the investment period (the modified internal rate of return).

Useful as these are — it’s always soothing to have a number followed by a percent sign — nearly every finance professional on the planet will tell you that the only reliable measure of an investment and its prospective cash flows is the net present value. Unlike a percentage, the net present value tells you the value of the investment in real money. While most equity investors will employ an IRR or MIRR, it’s not all that uncommon for them to run an NPV on your prospective financials, as well. Even if you don’t chase down an equity investor, an NPV is the most accurate measure of the future profitability of your business or project. As a result, I always run several NPV’s for my clients on more than one set of prospective financials.

And, in just a few short paragraphs, you’ll be able to do the same for yours!

(Because we want this tutorial to be useful to entrepreneurs who may have no math in their background, the following introduction to net present value is pretty long — really good, but long. If you already know what a net present value is, you may want to skip to the last section on how to figure an NPV in your spreadsheet.)

What is an investment’s value?
We are heroically striving to get no more complicated than sixth grade math in introducing these spreadsheet formulas, so before we define “net present value,” let’s take a very, very simple example.

You give me $100. Next year, I return your $100 plus an extra ten dollars, as a thank you gift, but you, being the miser you are, call it “interest.”

How much money did you make? If you answered $110, you’re an idiot, because you lost the $100 a year ago and just now recovered it. If you answered $10, you’re much closer to the mark, because now you have ten bucks you didn’t have before.

That $10 that you make is the value of the investment, as distinct from the rate of return, which is a fraction (0.1 or 10%).

Do you understand the difference? The value is always a dollar figure, the rate of return always a percentage.

But the return or value is not the real return
Notice that I said that the $10 was close to the right answer — meaning, of course, that it really isn’t the value or return on your investment.

How so? Well, for one, inflation has reduced the purchasing power of your dollar over the year-long course of the investment. This has two implications: the $100 that I return to you is worth less than the hundred bucks you lent me a year ago. So you’ve actually lost money outside of the interest I paid you. And the $10 I give you in interest is worth less than $10 from a year ago.

There’s one other consideration. Rather than lend me $100, you could’ve invested the money in, say, a Treasury bill, a completely risk-free investment. Since a T-bill investment is a sure thing, you can very rightly consider the real value of your loan to me as the interest I pay over and above the interest you would’ve earned on a treasury bill.

Let’s add another wrench to this gear. Suppose that I’m in bankruptcy court, in foreclosure, having my car repossessed, and about to have major heart bypass surgery . . . with no health insurance. You lend me $100 at 10% interest, but you quite realistically don’t expect me to pay you back a penny. I am a pretty major lending risk — the personal equivalent of the junkiest of junk bonds.

Well, you could always invest your money in junk bonds, no? If you look at the junk bond markets and ferret out the riskiest of the junk bonds, then the return you expect from me should be equivalent. So you may want to adjust the measly ten bucks I’m giving you relative to the return on an equivalently risky loan. In this case, you’d consider the real return to be the interest I pay over and above (or under and below) the return you’d make on an equally risky investment.

Net Present Value
When you adjust the return (or value) of an investment by subtracting out part of the return for inflation (which gives you the real return), or subtracting out the return you’d make on a risk-free investment, that’s called discounting the return. Discounting takes that basic dollar value and adjusts it to give you a more realistic picture of the investment’s desirability. Discounting gives you today’s value — or the present value — of the investment.

So now we’re ready to define this notoriously difficult financial concept:

The Net Present Value tells you how an investment will actually change your purchasing power — in dollars — over the course of that investment and its cash flows.

What is the Net Present Value of our loan example if inflation is hopping along at 2.4% a year? Answer: $7.25.

What is the Net Present Value of our loan discounted for the return on a 2.5% T-bill? Answer: $7.13. So, relative to a Treasury bill, you’re adding $7.13 to your bank account in today’s dollars.

However, you may want to calculate the investment relative to a junk bond since, in the fictional circumstances I described above, I’m pretty much in the junkiest junk category as a borrower. Let’s say the going rate on junkiest junk loans is 15%. If you use 15% as your discount rate, the value of the $100 loan is: -3.78. You are losing $3.78 as compared to investing the money in an equally risky loan.

You don’t need a whole brain full of math to realize that when a Net Present Value comes out negative, it’s not a good investment. The minus sign means you’re losing money (relative to inflation, risk-free investments, or equivalant investments), so you want to shake the dust off your sandals and move on.

Determining the discount
It should now be abundantly clear that the real value to using an NPV lies in the rate at which you discount the returns. Because you can discount your cash flows using any number you or your investors can pull out of your head or some other part of your anatomy, how do you know you’ve got the right discount rate? Fortunately, there really are only four or five useful discount rates:

Inflation: the most obvious discount is the rate at which the purchasing power of your money is declining every year, that is, the projected rate of inflation. When you discount an investment’s cash flows using an inflation rate only, you’re calculating the real value of the investment, that is, the value of the investment in today’s dollars. Now, while you can pull any number out of thin air as “your” inflation rate, you can easily find what economists think the inflation rate will be for one, two, five, or ten years just by doing a quick search on the Internet.

Treasury bills: T-bills represent the lowest risk free rate available. Since T-bills are always an option and are always guaranteed, they make a perfectly legitimate discount rate. If you can’t beat a T-bill with your investment, then you shouldn’t make the investment. Again, five minutes on the Internet will give you the current rates on T-bills for any investment length.

Insured deposits: If, like most small business owners, you require an investment below $400,000, then the highest risk-free rate you can find are FDIC-insured bank deposits. Since your goal is to compare your investment to the best you can do without taking any risk, you would use the bank deposit’s interest rate rather than a T-bill. A phone call to a couple banks will soon supply you with the highest insured rate you can find.

Cost of capital: The finance ubermeisters who run corporate America typically calculate NPV’s by discouting cash flows relative to their cost of capital. You, too, may have to use cost of capital as your discount rate — if, that is, you’re paying for the money you’re using. For instance, you may be borrowing the money to run your business or launch a project. Suppose, for instance, that you finance your business by taking out a small business loan at 7% interest. In that case, you don’t want to use inflation or risk-free returns as your discount rate when calculating an NPV. Why? Because in order to make money, you have to beat what you’re paying in interest on the small business loan. In this case, 7% is your cost of capital (that was easy), so you would use 7% as your discount rate. If your NPV comes out negative, then either find a lower interest loan or find a way to make more money in your projected financials!

Equivalent risk investments: Finally, if you really want to do the footwork, you can always find investment opportunities that carry the same amount of risk as your business or project. You want to find out what return the market expects from that kind of investment and compare your business returns to those investments.

How do you calculate an NPV?
Setting up an NPV calculation is just a tad more esoteric and convoluted than calcuating an IRR or MIRR on the same cash flows. In addition, it takes some real, solid financial decision-making on your part — for instance, how you’re going to discount the cash flows, as we discuss above.

It’s worth the effort, though. There is literally no other financial calculation in the known universe that more accurately demonstrates the true and accurate value of an investment. Hell, once you know how to do an NPV, you’ll probably be smashing it against all your investments, not just your business projects!

The variables
If you simply put a bunch of cash flows in a spreadsheet and tried to insert an NPV calculation, you’d probably quit when confronted with the variables the spreadsheet is asking you for.

Don’t be daunted — it’s actually stunningly easy.

Just like an IRR or MIRR, the most important variables are the cash flows and their timing. If an investment delivers returns monthly, then the returns have to be entered into the spreadsheet for every month. If you invest a pile of money at the beginning of the year, a pile more six months later, and another pile two years later, you have to place those investments in the proper relationship to the returns (in this case, in intervals of half a year). If returns or investments are variable (say, once in April, once in May, and once in November), then you have to choose the lowest common unit of time, in this case, monthly — most months, of course, will have cash flows of zero, but April, May, and November will have cash flows).

The second most important variable is the discount rate, which we discussed above. Now, remember: timing is everything. So if your cash flows are annual cash flows, then your discount rate will be whatever you choose. For instance, if you choose 4% as your discount rate, you will enter 4% or 0.04. However, if your cash flows are monthly, then your discount rate must be adjusted to a monthly rate. That’s not as hard as it looks: simply enter 0.04/12. Couldn’t be easier. If cash flows are biweekly, then your discount rate is 0.04/26 (there are 52 weeks in a year). Easy doesn’t begin to describe it.

Let’s use the exact same cash flows we used when discussing an IRR (remember, the IRR was 4.17% on those cash flows). Remember: this is a real set of calculations from prospective financials set up for a restaurant. The investor gets 20% equity plus a big share of profits for years three to eight. After year eight, the investor gets 20% of the profits while retaining 20% equity ownership.

Here’s the scenario we used last time:

In year 0, the cash flow is negative 2.5 million dollars (money going in to the investment is always a negative cash flow).
In year 1, the cash flow is 0 to the investor.
In year 2, the cash flow is 0 to the investor.
In year 3, the return to the investor is $385000
In year 4, the return to the investor is $421000
In year 5, the return to the investor is $457000
In year 6, the return to the investor is $582000
In year 7, the return is $634000
In year 8, the return is $691000

So you would set that up as follows in your spreadsheet:

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8
-2,500,000 0 0 385000 421000 457000 582000 634000 691000

Those are the cash flows you enter in the spreadsheet. Now you can calculate the NPV.

Highlight a cell in the spreadsheet and choose “Insert/Function” and find the NPV function in your spreadsheet’s formula library (in Excel, it’s for some bizarre reason listed as “NPV” under “Financial”).

Insert NPV in Excel

You’ll get a dialog box that looks something like this:

NPV dialog box in Excel

Values are the cash flows in order. Unlike an IRR or MIRR, you have to enter each and every cash flow. The first cash flow — the $2.5 million investment — goes into the field for Value 1. The second cash flow — $0 — goes into the field for Value 2. Every time you enter a value, the spreadsheet will add another field for the next value, so you can’t run out of fields. In order to properly use the power of the spreadsheet, you should not enter the value of the cash flow, since it’s already entered in the spreadsheet. Rather, you should enter the cell of the cash flow (like F2 or I2) — you can do so by putting the cursor into the value field and then selecting the cell. That way, if you change the values, the NPV will automatically recalculate.
Rate: this variable, usually the first you’re required to fill in, is the discount rate, as we discussed above. If you’re uncertain, then consult our little section above about choosing the inflation rate, risk-free rate, bank deposit rate, or equivalent investment rate as your discount rate.

With the cursor in the Value 1 box, select the cell with the first cash flow (-2,500,000). With the cursor in the Value 2 box, select the cell with the second cash flow (0). With the cursor in the Value 3 box, select the cell with the third cash flow (0). Continue through all the cash flows until you’ve entered the cell for the ninth cash flow in Year 8 (691,000). You should have nine values. Now type in a discount rate (I picked 3% as the 10-year T-bill rate — a few years ago).

NPV dialog box in Excel

Hit “Okay” (or Submit or whatever) and the spreadsheet will automatically calculate the NPV. For these cash flows, the net present value in comparison with a 10-year treasury (a few years ago) is: $164,070.33. That’s how much more the investment is worth than a 10-year treasury bond in today’s dollars.

Suppose, however, that the investor takes out a second mortgage (he lives in Malibu) at 5.25% to pay for the investment. If we had entered 5.25% as our discount rate (the investor’s cost of capital), the investment is worth: -$135,590.56. Negative numbers are always bad in a net present value. In this case, the investor is going to pay $135,590.56 more in interest on the mortgage he used to finance the deal than he’s going to get back in returns over the next eight years (that doesn’t mean it’s a loser — he may wish to calculate his returns to include the 20% equity stake and future profit distributions).

Let’s say that the investor can get 4.17% from his bank. If you use 4.17% as the discount rate, the net present value is . . . 0. In other words, there’s no difference between banking the money at 4.17% over the next eight years and investing in this restaurant.

If that 4.17% sounds familiar, then kudos for paying attention through all this boring stuff. That, if you remember, is the internal rate of return on these cash flows. Which leads us, not surprisingly, to the technically correct definition of an internal rate of return:

The internal rate of return is the discount rate at which the net present value of a series of cash flows is 0.

In other words, the IRR translates an investment into its equivalent as an investment with compounded interest, which, ta-da, is how I originally defined it.

So, why would you need an NPV?

If you are investing your own money, an NPV is the most accurate, unassailable measure of the value of your investment. It can in an instant tell you whether an investment is worth making relative to inflation or some other investment since a negative NPV means “bad” and a positive NPV means “good.”

Again, if you’re investing your own money in your business, an NPV gives you the opportunity to measure the desirability of your investment relative to several other investments you can make. There’s no end to the number of NPV’s you can calculate on your anticipated cash flows.

Third, if you’re looking for equity investors and you’ve cobbled together a persuasive set of prospective financials, a set of NPV’s comparing the investment to several others is the quickest way to illustrate the desirability of the prospective investment — more so than even an IRR.

Since we’ve had so much fun today, I think it’s time to close up shop. Tune in next week for breakevens. Now, the math behind a breakeven is ten times easier than an NPV but, believe it or not, it’s twice as hard to set up in a spreadsheet. We’ll show you the tricks of the trade to make it easier.

Be Sociable, Share!

2 Responses to “The spreadsheet formulas every entrepreneur should know, part four: Net Present Value”


  1. [...] 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 [...]

  2. [...] 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 [...]

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