The Net Present Value (NPV) or net present worth is a popular financial analysis method to determine the feasibility of an investment in a project/business, security, or anything that deals with cash flow. For example, suppose an investor needs to choose between investing his money into setting up either of the two factories, which would result in better profitability in the future and would give better returns than the other. The other popular budgeting methods include IRR(Internal Rate of Return) and the Payback Period.
In simple terms, NPV is the difference between the present value of the cash inflows and the cash outflows. The NPV applies to cash flows occurring at different times and is dependent on the discount rate implied. Net Present Value (NPV) is the value of all future cash flows (inflow or outflow) over the entire life of an investment discounted to the present value.
A Positive (>0) Net Cash Flow (NPV) resembles a profitable investment that would result in net gains after the payback period.
Formula to Calculate NPV in Excel
Mathematically, NPV can be calculated by using the formula:
where:
NPV = Net Present Value
= Net Cash Flow at time t
= Discount Rate
= Time of the Cash Flow
Steps to calculate NPV in Excel
Let’s now get into the steps to calculate the NPV in Excel and apply the above formula to our sheets.
Step 1: Input the Data
- Enter the relevant data from the start of the business in correct formatting, for which you want to calculate NPV.
- Clearly Mention the Cash Inflows and Cash Outflows of the investment along with the discount rate applicable
Here, in our example, we are calculating the NAV of a company, discounted at a rate of 4%, started six years ago. We have Cash Outflows and Cash Inflows data with us. You may notice that the ‘Initial Investment’ adjacent cell of Cash Inflows is empty, the reason being, that our example company was set up (started) at that time with an initial investment of $80,000, hence No Cash Inflows.
Step 2: Calculate the Net Flow
- Now, we need to calculate the Net Cash Flow.
- Net Cash Flow = Cash Inflows – Cash Outflows
Here, in our example, it will be calculated using (B2-C2)
- Apply the same formula to all the cells of the ‘Net Cash Flow’ column. (You may encounter both positive and negative values, as Net Cash Flow of a company can be either positive or negative)
Step 3: Calculate the Present Value of Cash Flow
- We now need to calculate the Present Value of Cash Flow, to do this:
- Insert the NPV function in an output cell
=NPV(rate, value)
This function of Excel – Returns the net present value of an investment based on a discount rate and a series of future payments (negative values) and income (positive values)
To use this, simply type =NPV( in your output data cell
- Enter/select the first parameter of the formula – discount rate, followed by a comma for the next set of values
=NPV(B10,
as we have our discount rate in the B10 cell which holds 4% as value
- Now, drag and select all of the values in Net Cash Flow column excluding the one with initial investment and finally close the bracket and hit enter
=NPV(B10, E3:E7)
Note: this would not include the initial investment as the NPV by definition is the value of all future cash flows (positive or negative) and it should not include the initial investment while calculating using the NPV formula of Excel
Step 4: Write Down Initial Investment
- Though you already have the initial investment with you, but we need the modulus of initial investment
- Meaning, make a separate cell, for Initial Investment with only positive value or without sign (negative sign)
As shown below, in our example we have used our initial investment of $80,0000 in a new cell for easier readability and calculation.
Step 5: Calculate NAV in Excel
- Finally, to calculate Net Present Value of an investment:
- We calculate the difference between the Present Value of Cash Flow and Initial Investment as,
- NPV = Present Value of Cash flow – Initial Investment
In our example, we do so by subtracting H6 from H4
=H4-H6
Hence, we get our final Net Present Value of $ 3,307.27.
Conclusion
This shows how you can calculate the net present value in Excel based on the cash inflow and outflows. We hope you enjoyed this tutorial! Stay tuned for many such tutorials.