If you want to learn excel keep reading or you can hire an excel expert from GetXperts.com to help you immediately
I’ve been meaning to write a bit about NPV after having discussed how to calculate discounted cash flows and using NPV to calculate the valuation of this site. Since I haven’t written about corpoate finance in a while, I thought this might be a good change of pace.
When we calculate DCFs, what we’re essentially trying to do is calculate the value of all the future cash flows at a given point in time, most often at the present moment. As discussed earlier, to do this, you need to know or forecast future cash flows in each period as well as have a discount rate in mind that reflects the riskiness of the project or events leading to the cash flows.
One way to determine whether a project is worth accepting or not is to look at its net present value (NPV). At a high level, NPVs are very much like DCFs. NPVs involve comparing the present value of cash inflows with cash outflows, and calculating each one can get complicated depending on how a company is set up (leveraged or unleveraged) because deciding what to include or exclude and their interactions with tax can get hairy. So, to simplify this discussion on IRR, I’ll use Experiments in Finance’s NPV calculations to demonstrate.
As you can see from the upper left-hand graph, I’ve been plotting the “cash flows” from this site each quarter in order to determine whether running this site is a worthwhile “project” from an NPV standpoint. (You can read through each month’s valuation updates if you’re curious.) The only trick here is that whereas you normally project future cash flows and discount them back to the present to determine a project’s value, I’m running a what-if experiment by putting in each month’s cash flows and discounting them back to the date I started this site (in the past).
I also decided to define the cash flows in my NPV calculation as simply the revenues from this site (in the form of payments from ads) less my monthly expense for hosting costs. Note that I conveniently ignored labor costs, because, as you should be able to guess without doing an NPV, this site is really not worth doing once I calculate the time I’m putting into writing here! (This isn’t always the case, as some people run a perfectly good business doing this…take a look at Darren for example.)
As a general rule, and assuming that the assumptions in your calculations are reasonable, you want to accept projects whose NPVs are positive and reject those that are negative.
Here’s how I calculate the NPV of this site each month using Excel:
1) Calculate free cash flows (FCFs):
I started this site in January, with paltry earnings from ads and $15.01 to purchase my domain name ($15) and hosting costs for that month. (Hostgator had a special for $0.01). Hence, as is normal in any project or business, you can see that I had negative cash flows for the first few months. My normal expenses each month just comprise my hosting costs, but in April I made a donation to pfblogs.org, so I decided to include that in as an expense.
To me, in corporate finance the biggest challenge in setting
up a good NPV is getting the cash flows correct, especially when ensuring that all possible project expenses and revenues are taken into account and timed correctly. The calculation itself is easy; it’s the accurate information-gathering that’s always the hardest part!
2) Determine your discount rate or cost of capital
I chose 5% as my discount rate in this valuation. I decided on this figure because I consider the amounts involved in running this site small and not very risky and more akin to putting the money in a savings account rather than anything else. After all, if the project fails, I quit running the site, and that’s that. The ads I run are done through pretty established companies that will pay when they’re supposed to. And I can’t very well invest the small amount I’m putting in each month nor the amounts I’m getting back in other places (like the stock market), so my opportunity cost is also minimal. Note that 5% is the annual rate, and I must change this to the appropriate periodic rate in order to calculate the NPV correctly. Using my periodic rate calculator. the monthly equivalent of 5% is around 0.407% or so.
3) Use Excel’s NPV function to calculate your net present value.
The NPV function is pretty simple to use. Just type:
=NPV(rate, free cash flow1, free cash flow 2, … free cash flow n)
to run the calculation. In my case, I’ve got my correctly adjusted rate in cell B3 and cash flows from cells C10 to I10, so the equation looks like:
Through the month of July, my NPV is $89.93, so it’s an acceptable project to undertake, as long as we don’t consider labor costs. (Taking labor costs into account at even $10 per hour * 10 hours a week * 4 weeks a month, which is an underestimation, I think, NPV stands at a whopping -$2,766.85 for this site!)
Just like DCFs and any sort of valuation method, NPVs have their limitations. For one, they’re pretty inflexible. If you have decisions and options midway through the project, their values aren’t considered in NPV calculations, and in these cases, real options might be a better valuation method.
Also, whether a project’s NPV is positive or negative is irrelevant if the inputs into the calculation are out of whack. If you’re a project’s supporter, try to avoid the tendency to overestimate the amounts and timing of revenues or cash inflows or underestimate their counterpart outflows. Finally, both DCFs and NPVs are highly sensitive to terminal values. For example, if you have an project with an infinite life (or are running a valuation on a company), the last cash flow in your calculation essentially represents not only the cash flow for that period but also all the future cash flows past that point. If you overestimate this amount (or the implied growth rate that that amount represents), then you can easily come up with a very positive NPV.
In fact, this is probably one of the biggest criticisms of the NPV and DCF analyses done by sell-side analysts in investment banking. If you ever get a chance to look at such estimations, you might notice that oftentimes the implied steady-state growth rate is even greater than GDP. If that were the case, then the company would eventually take over the entire world!