How to quickly calculate NPV
In short:
Net present value (NPV) is the approximation of the total earnings of an investment or project less the initial investment if they were all received today. The value of NPV is that it allows companies and investors to forecast if an investment will be profitable and by how much.
While it’s possible to calculate the NPV by hand, the best approach is to use excel as it will automate the process for us.
Key Points
- Net present value (NPV) is an estimate of what the sum of all future cash flows would be worth if they were
- received today after subtracting the initial investment.
- Projects or investments with a negative NPV should be avoided.
- If projects or investments have a positive NPV they are generally considered profitable.
How NPV Works
NPV is an approximation of what the sum of all future cash flows of a project or investment would be worth if they were received today after subtracting the initial investment.
So, if we know a few important factors about a project we can calculate the NPV of the project. The variables we need to know are, the cash flows in the future, when in the future they are received, what our capital costs are, and how much we would need to initially invest.
Why calculate NPV?
The reason we even calculate NPV in the first place is because of the time value of money and opportunity costs.
Time value of money says that money today is worth more than money tomorrow. This principle infers the same $1 earned by a company today is worth more than the same $1 earned by the company 5 years from now. Essentially, this is because the company could do something constructive with the $1 they receive today that would make it worth more than $1 5 years from now.
Opportunity cost is the price you pay for selecting one project over another. So if a company is looking at two projects they need a way to compare the projects and select which one is best to pursue. While average rates of returns (i.e. IRR) are an important metric, typically the absolute dollar value will be more important for a company to consider when looking at a project.
NPV provides a means for a company or investor to mathematically account for these two critical factors which is why it’s important to know. The formula for NPV we’ve provided here:
Calculating NPV in Excel – hard way & easy way
Calculating NPV manually could be good to do initially so you understand the mechanics behind what’s going on. However, calculating NPV by hand all the time is time-consuming and not practical. Instead, Excel should be used to make the process quicker.
Here’s an example that we’ve set up in excel.
Let’s say we are looking at a corporate project that involves buying new machinery. The initial cost of the new machinery is $250,000 but we estimate the useful life of the machinery would run 10 years and we would be able to consistently generate $50,000 of free cash flow a year. Should we make this investment?
As we think about it, we know that if the company puts $250,000 in the stock market we can reasonably expect to earn an annualized 10% return. So, if the company is paying all cash our hurdle rate (WACC) would be 10%.
To figure out what we should do we jump into excel and model it out.
Since our NPV came out to being positive ( $307,228.36 – $250,000 = $57,228.36), we know that the machinery is the option we should take over investing in the stock market.
Calculating NPV in Excel – Quick approach
While the excel method we showed above is a possible way to calculate NPV, we can make it even more simple by using excel’s built-in “=NPV” function. Using the same scenario from above we except using the built-in function would be as follows.
*Please note, we are taking the present values of the cash flows then subtracting the initial investment value. By using this approach we can see if the present values of future cash flows pay for themselves above and beyond the initial investment amount.
The variables we need to fill in the excel function are only the first two and are filled in as follows.
