Excel NPV Function

This tutorial demonstrates how to use the Excel NPV Function in Excel to calculate the net present value of a series of cash flows.

Excel Function NPV

NPV Function Description:

The NPV Function Calculates the net present value.

To use the NPV Excel Worksheet Function, select a cell and type:
npv formula syntax

(Notice how the formula inputs appear)

NPV Function Syntax and Inputs:

rate – It’s the rate of interest or the discounted rate over one period. It should be entered as a percentage value or a decimal number.

value1 – It’s the numeric values that represent a series of regular cash flows.

value2 – OPTIONAL: Second value(s) representing cash flows.

What Is Net Present Value?

The difference between the present value of cash inflows and cash outflows over a specific period is the net present value (NPV). It is assumed that for the investment to be profitable its NPV should be positive and if the NPV is negative then the investment will result in a net loss.

It is simply calculated as:

NPV Formula

What Is Excel NPV Function?

The Excel NPV function calculates the Net Present Value of an investment based on it’s discounted rate or rate of interest and a series of future cash flows. The Excel NPV Function uses the following equation to calculate the Net Present Value of the investment:

Excel NPV Formula

Where:

n = number of time periods

i = period number

rate = discount rate or interest rate

values = cash flow

Calculate Net Present Value of a business

npv function example 1 data

Let’s assume, we start a business and the initial investment of $100,000 will be required at the end of the first year. And it is expected that the business would start generating profit from the second year. The second, third, fourth, and fifth-year profits are projected to be $20,000, $35,000, $50,000, and $80,000 respectively. The rate of return we could get in alternate investment is 10%. So, we need to find out the net present value of the business.

The initial investment of the business is entered with a negative sign as it represents outgoing cash

Value 1 = -$100,000

The formula used for the calculation of the net present value of a business is:

Excel Function NPV Example 1

The Net Present Value of the business calculated through Excel NPV function is

NPV = $24,226.66.

Calculate Net Present Value of a Project

npv function example 2 data

Let’s calculate the Net Present Value of a project, where an investment of $10,000 was made at the start. The annual discount rate is 8%. The returns from 1st year to 4th year are given in the above table.

The formula used for the calculation is:

npv function example 2

The Net Present Value of the business calculated through Excel NPV function is

NPV = $9,437.1

After deducting the initial investment from the above value, we’ll get a better picture of the investment

NPV (after deducting initial investment) = $9,437.1 – $10,000 = -$562.90

The Net Present Value of the business is -$562.90.

The NPV result came in negative, it means that the investment would still not be profitable at the end of 4th year.

NPV in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

NPV Function Google Sheets

Additional Notes

Use the NPV Function to calculate the Net Present Value of an investment.

Values must be in chronological order. So the payment and income values must be entered in the correct sequence and these values must occur at the end of the period.

In Excel 2007 to 2019, from 1 to 254 value arguments can be entered that represent the payments and income. In excel 2003 and older versions up to 30 value arguments can be entered.

Cash inflows(where cash is received such as coupon payments) have a positive sign while cash outflows (where cash is paid out such as money used to purchase the investment) have a negative sign.

NPV and PV (present value) function are quite similar. The only difference between NPV and PV is that PV allows cash flows to begin at the start of the investment period and also at the end of the investment period.

Periodic cash flows are required. Cash flows most occur on a consistent basis each period.

NPV Examples in VBA

You can also use the NPV function in VBA. Type:
application.worksheetfunction.npv(rate,value1,value2)
For the function arguments (rate, etc.), you can either enter them directly into the function or define variables to use instead.

Return to the List of all Functions in Excel