Site icon Excel Unlocked

NPV Function – How to Calculate NPV in Excel

The term NPV stands for Net Present Value. The Net Present Value refers to the discounting of the series of expected cash flows generated throughout the life of the project. In this tutorial, we would learn how to calculate the net present value using the excel NPV function and formula.

Let us first learn what is NPV in financial management and then deep dive into its calculation in excel. 😎

What is Net Present Value (NPV) in Financial Terms?

NPV is nothing but the present value of the future expected cash flows discounted at a specific rate of discounting minus the initial investment outlay.

The NPV is used to determine if an investment project is worth taking up or should be postponed/scrapped.

Financially and mathematically, the calculation of NPV goes like this:

NPV = Total Present value of future cash flows (PVCF) - Initial Investment

Here, PVFC = Cash Flow / (1+r)n

where, r = discounting rate ; n = year

All the PVFCs are summed up to compar with the intial outlay. If the NPV is positive, the project is worth accepting, else reject the project.

When To Use NPV Function in Excel

NPV formula in Excel is used to calculate the net present value of future cash flows with the help of excel. Many financial analysts do not use any calculator to find NPV, instead, they prefer using the NPV excel function or NPV calculator in excel.

The NPV function considers the time interval between two cash flows as equal (which is one year). Thus, any other time interval like 2 years, or quarters, or months will result an incorrect NPV value.

To calculate NPV with irregular interval cash flows, try the XNPV function in excel.

Surprising Fact About NPV Excel Function

Surprisingly, excel’s NPV function does not directly calculate the net present value. Instead, it just calculates the present value of future cash flows (PVCF) component. To calculate the NPV, simply subtract the initial outlay/investment from the resultant PVCF.

=NPV(rate, value1, [value2], …) – Initial Investment Amount

Syntax and Arguments

=NPV(rate, value1, [value2], …)

The NPV excel function has the following arguments:

This function is available in all the excel versions. You can specify a maximum of up to 254 value arguments in Excel 2007 to modern excel versions.

Examples of Excel NPV Function

In this section of the tutorial, we would learn how to use the NPV excel formula with the help of a practical example.

Suppose a prospective investment project requires an initial fund investment of $800,000.

Also, this investment is expected to generate the following revenue figure during its life of 10 years. Discounting factor is 10%.

For calculating NPV in excel using the above data, we need to first calculate the discounted future cash flows.

To calculate the present value of discounted cash flows, simply use the following formula:

=NPV(B13,B2:B11)

As a result, excel will return the PVCF value as 10,72,284 (rounded off).

Now, subtract the initial investment amount from the PVCF to derive the NPV of the investment proposal as shown below:

=NPV(B13,B2:B11)-800000

As a result, excel will return the NPV of the project as 2,72,284.

As a finance guy, I would conclude with this that since NPV is positive, the project is worth investing. 😉

Instead of selecting the series of cash flow values, if you wish you may even select the cash flow values individually, each separated by comms. Have a look at the below formula:

=NPV(B13,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11)-800000

However, the above formula is quite complex to understand and can accept a maximum of 254 value arguments.

Do Not Miss These Points

To make sure that the NPV excel function returns a correct value, it is important to keep the following points in mind before using the NPV function for your project appraisal.

Thank You 🙂

Exit mobile version