The Net Present Value (NPV)
The Net Present Value (NPV) is a financial indicator used to evaluate the profitability of an investment.
The NPV formula is:
To calculate the present value based on future cash flows, use the following formula:
Future Value is the expected net cash inflow or outflows, r is the discount rate, and t is the number of time periods.
The expanded formula of the NPV is:
The Excel NPV Function
The NPV function is defined by the following syntax:
=NPV (rate, value1, [value2], ...)Arguments:
| Argument | Required/Optional | Explanation |
|---|---|---|
| rate | Required | Discount rate over one period. |
| value1 | Required | First value(s) representing cash flows. |
| value2 | optional | Second value(s) representing cash flows. |
The Excel XNPV Function
You can also calculate the NPV with the XNPV function. The XNPV function has the following syntax:
=XNPV(rate, values,dates)Arguments:
| Argument | Required/Optional | Explanation |
|---|---|---|
| rate | Required | defines the discount rate |
| values | Required | represents cash flows |
| dates | Required | cashflow dates |
Read More: XIRR vs IRR in Excel
How to Calculate the Net Present Value with the Excel NPV Function
Steps:
- Create a dataset with cashflow over a period of time.
- Enter the following formula:
=NPV(D11,D5,D6,D7,D8,D9)D11 = Annual discount rate
C5, C6, C7, C8, C9 = Cashflow over a specific time interval
- Press ENTER to see the Net Present Value.
You can cross-match the result with a basic arithmetic formula: calculate the Present Value over the period of time and sum the values.
How to Calculate the Net Present Value with the Excel XNPV Function
Steps:
- Enter Date and Cashflow in separate columns.
- Consider the Discount Rate.
- Use the following formula:
=XNPV(C11,C5:C9,B5:B9)C11 = Discount Rate
C5:C9 = Cashflow in different periods of time.
B5:B9 = Date
- Press ENTER to see the Net Present Value.
Read More: How to Convert Percentage to Basis Points in Excel
Differences between the XNPV and the NPV Function
- The values in the NPV function are equally spaced in terms of the period of time.
- The Net Present Value calculation with the NPV function does not require dates.
- The XNPV function returns more accurate results, as it takes a set of dates.
Download Working File
Related Articles
- How to Calculate Profitability Index in Excel
- How to Calculate Tracking Error in Excel
- How to Calculate WACC in Excel
- How to Calculate Mileage Reimbursement in Excel
- How to Calculate Time Weighted Return in Excel
<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thanks a lot Taryn!!!
I m a Finance Student and need this formulas a Lot!!!!
You have very easily explained the concepts.!!!
Looking foreword for more:):):):):):)
You are most welcome. We are intending to do a financial functions tutorial series so lookout for that 🙂 Best of luck with your studies as well 🙂