Excel NPV Function to calculate Net Present Value

This Excel tutorial explains how to calculate net present value using Excel NPV Function.

You may also want to read:

Excel VBA NPV Function

Net Present Value

Net Present Value is to calculate the present value of future cash flow. For example, your client will give you $10000 when the project completes after 1 year, assume there will be inflation, the future $10000 will be less than the present $10000. Assume the inflation is 4%, the present value of $10000 is 10000/(1+4%) = 9615.384615    This calculation is just a simple maths, you don’t even need to memorize any formula.

If your company needs to invest $9900 today in order to gain $10000 after 1 year, then you should not take this project because $9615 you will gain is less than $9900 you invest today.

If you receive $10000 after 1 year, and $5000 in the second year, then

Net present value = $10000/(1+4%) + $5000/(1+4%)2

= 9615.38 + 4622.78

= 14238.17

Excel NPV Function

Instead of calculating the Net Present Value manually, Excel NVP Function provides a quick way to calculate the Net Present Value.

Syntax of NPV Function

NPV(rate,value1,[value2],...)
Rate  Required. The rate of discount over the length of one period.
Value1, value2, .. Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income.

Example

Using the above example, if the discount rate is 4%, while the cash flow return is $10000 after the first year, $5000 after the second year

Formula Result
=NPV(0.04,10000,5000) HK$14,238.17

For the Value parameter, you can also input a Range. For example

Excel NPV Function

Outbound References

https://support.office.com/en-us/article/NPV-function-8672CB67-2576-4D07-B67B-AC28ACF2A568?ui=en-US&rs=en-US&ad=US&fromAR=1

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *