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 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
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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