When working with data related to investments and project assessments, you may sometime have to calculate the NPV (Net Present Values) of projects/investments.
In this tutorial, I will show you how to calculate the Net Present Value (NPV) in Excel using two simple inbuilt functions (NPV and XNPV).
So let’s get started!
What is NPV – Easy Explanation
Let’s first understand what net present value means.
NPV is the value that represents the current value of all the future cash flows without the initial investment. In other words, you can find out the value of future incomes discounted to the present value.
Let me explain with an example.
Suppose you have a project where you need to invest $50,000 now and it will give you $10,000 every year for the next 10 years.
Given that you know the prevalent discount rate (also called the interest rate of cost of capital), you can calculate what this money (that you will get in the future) is worth now.
If this NPV value is more than the initial investment (which was $50,000), you will make a profit, and if not, then you will have loss at your hands.
NPV is widely used in assessing capital projects assessments and making investment decisions.
It can help you to know whether your investment or a project is profitable or not.
In Excel, you can calculate it using the NPV function. It’s quite straightforward and makes the calculation of NPV really simple.
This article will guide you through the function and warn you about the limitations and prerequisites.
Excel NPV Function
The NPV function in Excel has the following parameters:
=NPV(rate, values)
- rate – a discount rate for a period
- values – an array of cells containing future payments (negative value) or incomes (positive value).
Here are some important prerequisites for using the function:
- the function takes in calculation only numerical values, while all others are ignored
- values occur at the end of each period
- values are in chronological order (from the oldest) and have the same time-space
- discount rate must be formatted as percentages
- all periods must have their value
Note that for this function, you need to have a regular inflow or outflow of values. In case you’re working with data where inflow/outflow happens at irregular intervals, you will have to use the XNPV function (covered later in this tutorial).
Calculating Net Present Value (NPV) in Excel
Let’s say that you have the following cash flow data (in column C). You also have the discount rate (in cell E2) and you now want to calculate the NPV of the cashflows
In column C, we have the cash flow, where initial investment at the end of period 1 (C2) is negative (-$20,000).
In cells C3:C8, we have the future incomes from the investment which are positive. The discount rate of 5.50% is in cell E2.
Based on these inputs, you want to calculate the net present value.
The below formula will give you the NPV value for this data:
=NPV(E2,C3:C8)+C2
Let me quickly explain what happens here.
We have used the NPV formula and we have ignored the value in cell C2, as this is the initial outflow. This is the money that goes out of the pocket on Day 1.
But then we have the inflows coming which come at the end of each period. So the value in cell C3 comes at the end of Year 1 and value in cell C4 comes at the end of Year 2.
So we have used the NPV formula to calculate the net present values of all the inflows and then added the initial outflow of -$20,000 back to the formula.
This gives us the final NPV of the total project cashflows.
Now if this value is positive, we would consider the project to be profitable and if it’s negative, we will consider it loss making.
Note that we only ignore the initial outflow. In case there are more outflows during the project lifetime, those will be used in the NPV formula (but make sure outflows are negative values).
Using NPV Function to Compare Multiple Projects
You can also use the NPV function to compare several projects’ investments and decide which project is the most profitable.
You just need to provide a discount rate, an initial investment for each project, and cash flows.
The logic here would be simple – you will calculate the NPV of all the projects, and choose the one with highest NPV.
Let’s say that you have 3 projects with their investments (C2, D2, E2), and cash flows (C3:C8, D3:D8, E3:E8).
In cell C10, there is a discount rate of 5.50%, and in cells C11, D11, and E11, you want to get the NPV for all three projects.
In cells C11, D11, and E11, the formulas are respectively:
=NPV($C$10,C3:C8)+C2
=NPV($C$10,D3:D8)+D2
=NPV($C$10,E3:E8)+E2
As you can see:
- Investment 1 has NPV of $5,111
- Investment 2 has NPV of $2,946
- Investment 3 has NPV of $5,366.
You can now easily compare these three projects and conclude that the third project is the most profitable one.
Difference between NPV and XNPV functions in Excel
As you already learned, the NPV function in Excel requires exactly the same time periods for the cash flow.
On the other hand, the XNPV function enables you to enter specific dates for each value. These dates can occur in irregular periods.
If you try to use both functions to get a net present value of a cash flow occurring in irregular periods, you will see the big difference in results. Therefore, the XNPV function is much more precise than the NPV.
Below is the syntax of the XNPV function:
=XNPV(rate, values, dates)
- rate – a discount rate for a period
- values – an array of cells containing future payments (negative value) or incomes (positive value)
- dates – the dates at which the cashflow takes place
Let’s say that you have the following cash flow and discount rate and want to calculate a net present value using both the NPV and XNPV functions.
In column C, you have the cash flow, where initial investment at the end of period 1 (C2) is negative (-$20,000). In cells C3:C8, you have the future incomes from the investment which are positive.
Date for each income is in cells D2:D8. The discount rate of 5.50% is in cell F2.
Based on these inputs, you want to calculate the net present value using two functions.
The formula in cell G2 is for calculating the NPV where we are not considering the dates:
=NPV(F2,C3:C8)+C2
The formula in cell H2 is using the XNPV where dates are also considered:
=XNPV(F2,C2:C8,D2:D8)
As you can see, the NPV function returned $27,156 in G2, while the XNPV function returned $38,387.
The reason for this difference is that NPV considers each cash flow to occur at the end of the period, while XNPV considers the exact date and discounts it accordingly. In most cases, your value with XNPV would be higher or equal to the NPV value.
So, if dates are involved, use XNPV to calculate Net Present Value in Excel.
Free NPV (Net Present Value) Calculator
Below is a free net present value calculator where you can enter the initial investment value, the discount rate, and the total cash flows separated by commas, and it will give you the NPV value.
Net Present Value (NPV) Calculator
I hope you found this tutorial useful!
Other Excel tutorials you may also like:
Sorry for my confusion, but should the dates all be in 2021, or as this project seems to progress across 7 periods, or 6 years, should they progress annually, ie: 2021, 2022, 2023, etc. but with different dates.