What a great and wonderful world it would be if all projects started would turn out to be a highly profitable, predictable endeavor! Better, still -wouldn’t it be great if there was a “magic wand” that could tell us which projects would be profitable even before starting? While any project can be subject to bad fortune, changing market conditions or poor timing, a thorough analysis of the project profitability is critical to predicting project financial success. Thankfully, the Net Present Value model (NPV) serves as our “magic wand” in project selection. The Net Present Value has long been a favorite of successful investors like Warren Buffett and you can apply the same principles toward your next project success. We’ll provide you the customizable template and show you how to adapt it for your own use.
The Time Value of Money
Net Present Value (NPV) alludes to the difference between the value of money now against its value at a future date. This is referred to as the “time value” of money. The concept is rooted in the fact that the value of money now is greater than the value of the same sum collected at a future date. Why is $1 today worth more than $1 a year from now and substantially more than five years from now? The loss of value of money over time is attributed to:
(2) the fact that money can be invested in a bank or business to collect interest over time. If interest is earned, $1 today may be worth $1.50 a year from now. The only mechanism for a future $1.50 to equate to a current $1 is if the money loses value over time. The “present value” of $1.50 a year from now is $1 in this simple example.
NPV and Project Selection
In project management, NPV determines whether the expected returns of a project, considering the time value, will outweigh its investments now. When considering selecting between two projects (“A” and “B”), NPV helps a project manager identify which of the two projects will be more profitable, and thus worthy of investment. The Net Present Value is one of several important project profitability indicators along with Internal Rate of Return (IRR) and Payback Period. For completeness, the template provided at https://freeprojectmanagementtemplates.com/ performs all three profitability analyses. The combination of Net Present Value, Internal Rate of Return and Payback Period are typically included in the “business case” for a project. These three indicators are the data-driven element of the business case to guide the project sponsor toward investment. Of these, the NPV is the most predictive because it relates to the buying of the rewards reaped through the project.
How NPV is Calculated?
The calculation is performed in two parts. The first is to calculate the Net Present Value of future cash flows: Net Present Value (NPV) = Sum of (Cash flow / (1 + discount rate) ^ number of time periods).
Cash Flow or “Future Value” is the anticipated or projected revenue during each period. Discount rate or “rate of interest” is the expected return on investment (ROI) on investment or funding which would be (or has been) invested in the project (usually expressed in decimal digits such as 0.1 to mean 10%). “n” is the number of periods (usually expressed in years) that the project will be (or has been) running.
The Present Value of each period is calculated and added together. The second step is to subtract the initial investment from the sum of discounted future cash flows. In the form of an equation:
If the Net Present Value is positive (example: $1000), the project is profitable. In comparing calculated NPV of various projects to be undertaken, a project (among other projects) with the highest positive value is said to be the most profitable. However, if the calculated NPV is negative (for example -$1000), the project is not profitable or not investment-worthy. In comparing calculated NPV of various projects to be undertaken, a project (among other projects) with the highest negative value or the lowest positive value is said to be the least profitable.
Calculating NPV in Excel
In most cases, you do not have to calculate NPV by hand, since there is an built in feature in Microsoft Excel for calculating NPV.
To find the NPV in Excel, use the following steps:
- Enter your discount rate (in decimal format) in a cell.
- Enter all the periods (in increasing order) in another set of cells.
- Add the various cash flows under their respective periods.
- Finally, to calculate the NPV as follows:
=NPV (select the discount rate cell, select first cash flow cell:last cash flow cell)
Let’s compare the profitability of two projects (A & B) by the NPV method
Initial investment: $20000
Discount rate: 10%
Year 1: $10000
Year 2: $30000
Year 3: $18000
Year 4: $36000
Calculating Present Value for each year:
Year 1: 10000/(1 + 0.10)^1 = $9090.91
Year 2: 30000/(1 + 0.10)^2 = $24793.39
Year 3: 18000/(1 + 0.10)^3 = $13523.67
Year 4: 36000/(1 + 0.10)^4 = $24588.48
Thus, NPV = ($9090.91 + $24793.39 + $13523.67 + $24588.48) – $20000
NPV = $51,996.45
Initial investment: $10000
Discount rate: 10%
Year 1: $16000
Year 2: $32000
Calculating Present Value for each year:
Year 1: 16000/(1 + 0.10)^1 = $14545.45
Year 2: 32000/(1 + 0.10)^2 = $26446.28
Thus, NPV = ($14545.45 + $26446.28) – $10000
NPV = $30991.74
The NPV for Project A is $51,996.45 and Project 2 is $30991.74. Therefore, we should invest in Project A since it has a higher NPV value.
A Short Tutorial on Our Template
The aim of our template, available under “Project NPV Model” here: https:/freeprojectmanagementtemplates.com/all-templates/ is that the user need only to input a few key pieces of information and allow the spreadsheet to perform the heavy lifting. However, he template is fully writable (unprotected) and may be adapted according to your specific project and business needs. There are four tabs: (1) BASIC DATA; (2) FREE CASH FLOWS; (3) ASSUMPTIONS; (4) INSTRUCTIONS. The INSTRUCTIONS tab includes some useful definitions and clarifying remarks. All assumptions on which the model is founded, whether related to pricing, market share, R&D costs, etc. should be captured in the Assumptions tab at the outset of the project. Especially for lengthy projects, it is necessary to capture all past assumptions to perform a post-project review of financials.
Getting Started with the BASIC DATA Sheet
An excerpt from the BASIC DATA sheet is shown below. In this example, we assume an R&D project is required to bring a manufactured good to market. The initial inputs are sales volume, price per unit and manufacturing cost. In the corresponding rows to the right, we have an opportunity to adjust for inflation. If sales or pricing remains flat, simply input “zero” in these cells.
Figure 1: Instructions for Completion of the BASIC DATA Tab
Next, Development Costs are broken down by period (typically per year) and entered in the R&D field. The total internal resource load is entered in the “Resource Headcount” row. If Capital Expenditures were purchased, they are included in the Investments / Capex row. Miscellaneous and Sales, Marketing and Admin expenses, if applicable, are introduced in the appropriate fields. The model can be further refined by entering the changes in working capital. The calculation is beyond the scope of this tutorial, but working capital has to do with calculation of assets and outstanding debts. If this information is unavailable, it may simply be left blank with little impact to the accuracy of the model.
The Output – Project Profitability Prediction
The CASH FLOWS tab is the workhorse driving the calculation machine and is intended to be an output summary; no input or manipulation should be necessary. The calculation is simple but looks unwieldy because of the realistic accounting of all essential business activities. The cash flow from each period is calculated based on the sales volume and deducted from expenses such as salaries, administrative expenses, taxes, etc. The total free cash flow for each period is obtained by summing all the income and subtracting the expenses. Row 41 applies the WACC (the interest rate) which “discounts” the future cash flows according to the familiar NPV formula. The resulting Net Present Value is expressed in row 44, which indicates a profitable project in this example.
Figure 2: Outputs of the FREE CASH FLOWS tab
Two other common profitability performance indicators are included for convenience. The first is the payback period, which is easily spotted as the first period which shows positive cash flow (year 9 in our example). Finally, the Internal Rate of Return (IRR) is presented. This is a more abstract indicator but can be generally interpreted as the growth rate of the investment annually. By definition, it is the discount rate at which future cash flows (NPV) are equal to zero. Many project management offices and management boards are still keen to evaluate prospective projects according to traditional payback periods and IRR, these are included for convenience.
We hope you have benefited from the refresher on NPV and find use in our custom template. Wishing you all the best project selection success,
Chartered Project Management