Updated June 9, 2023
Introduction to NPV Formula in Excel
NPV function or formula comes under the FINANCIAL category of function in Excel. It’s a financial calculation to determine or measure the value of investments. It calculates the difference between the cash inflow (income) & cash outflow (cash you pay for the investment). NPV function helps out in analyzing the feasibility of a project & the time value of money.
Most commonly used function by financial analysts in financial modeling & analysis, equity investment, investment banking, real estate project, project financial assessment & loans & payouts from insurance contracts. NPV function is used as both worksheet & VBA function
Definition
NPV (Net Present Value) function in Excel calculates or determines the Net Present or actual Value of investment based on the series of future payments (Negative value) & income (positive values) concerning supplied discount rate & initial investments made.
The formula for the NPV function
Below is the formula:
Unfortunately, Excel does not define or calculate the NPV function correctly. To calculate NPV correctly in Excel, you should exclude the initial cash outflow (Investment) from your NPV formula and add the original investment amount at the end of the NPV formula to find the actual NPV.
=NPV (rate, value1, [value2], [value3] …) + the initial investment
or
=NPV (Discount rate, range of values) + the initial investment
The NPV function syntax or formula has the below-mentioned arguments:
- Values: It is a series of cash flows that indicates income & a series of future cash payments.
Here negative values are considered outgoing payments, whereas Positive values are treated as incoming payments or income.
Concerning the latest version of Excel, NPV can accept up to 254 value arguments.
- Rate: It is a discount or interest rate for a specific period of time.
In the output, a Positive or higher NPV value indicates that the investment is desirable, takes a good decision, and adds more or better value for your investment (profitable project or investment), whereas negative NPV values indicate a loss.
How to Use NPV Formula in Excel?
Let’s look at how the NPV function works in Excel.
Example #1 – Finding the Net Present Value
In the below-mentioned example, the Table contains the below-mentioned details.
The initial investment is 100000 (the initial investment is expressed in negative value), which is a cash outflow & interest rate is 10%
Series of cash flow: Year 0 -100000
Year 1 20000
Year 2 20000
Year 3 20000
Year 4 20000
Year 5 20000
Year 6 20000
Year 7 20000
Year 8 20000
Year 9 20000
Year 10 20000
I need to find NPV (NET PRESENT VALUE) using NPV FUNCTION.
Now, let’s apply the NPV function in cell “D16“. Select the cell “D16” where the NPV function needs to be applied, click the insert function button (fx) under the formula toolbar, and the dialog box will appear, type the keyword “NPV“ in the search for a function box, NPV will appear in select a function box.
Double click on the NPV function, and A dialog box appears where arguments for the NPV function must be filled or entered.
i.e. =NPV (rate, value1, [value2], [value3] …) + the initial investment
- Rate: It is a discount or interest rate for a specific period of time.
- Values Argument: It is a series of cash flows excluding the initial investment with a negative sign at the start of the stream. To select an array or cell reference, click inside cell B6, and you’ll see the cell selected, then select the cells till B15 so that the column range will get selected, i.e., B6:B15.
Click ok after entering the two arguments. =NPV(0.1,B6:B15)
To this formula, =NPV (0.1, B6:B15), you must add the initial investment values to get the correct NPV.
The above NPV calculation correctly excludes the 100000 initially invested cash outlay in the series of cash flows and then results in the correct NPV value of 22,891
Example #2 – Investment Based on Highest Returns
In the below-mentioned table, Suppose I want to select any one investment option from two given options with the same initial investment of 100000.
Here I need to compare NPV value, based on a 9% interest rate for both projects with an initial investment of 100000.
Let’s apply the NPV function in cell “B12” for Project 1 & “C12” for Project 2.
Select cell “B12” where the NPV function needs to be applied; click the insert function button (fx) under the formula toolbar; the dialog box will appear, type the keyword “NPV“ in the search for a function box, NPV function will appear in Select a function box.
The above step is simultaneously applied in cell “C12” also.
Double click on the NPV function, and a dialog box appears where arguments for the NPV function must be filled or entered.
i.e. =NPV (rate, value1, [value2], [value3] …) + the initial investment
- Rate: It’s a discount or interest rate for a specific period of time.
A similar procedure or steps is followed for Project 2 and for entering the rate argument.
- Value Argument: It is a series of cash flows excluding the initial investment (Year 0).
Here the value argument will be B7:B11 (Cash flow from year 1 to year 5). A similar procedure or steps is followed for Project 2 and for entering the value argument.
Click ok after entering the two arguments. ‘=NPV(0.09,B7:B11).
To this formula, =NPV(0.09, B7:B11), you must add the initial investment values to get the correct NPV.
For Project 1
=NPV(0.09,B7:B11)+B6 i.e. returns the NPV value 4,045
Whereas
For project 2
=NPV(9%,C7:C11)+C6 i.e. returns the NPV value 4,438
Inference: Based on the NPV, Project 2 or investment is preferable; it has given a better NPV than Project 1.
Things to Remember
- The NPV function ignores value arguments such as text values, empty cells or logical values, representations of numbers, or error values that cannot be translated into numbers.
- NPV function helps out in analyzing the feasibility of a project & the time value of money.
Recommended Articles
This is a guide to NPV Formula in Excel. Here we also discuss How to Use NPV Formula in Excel along with the examples and Excel template. You can also go through our other suggested articles to learn more –
