The net present value (NPV) is a calculation used when deciding on whether or not to make an investment, by determining whether or not a project or investment will generate value over time by discounting future cash flows to the present value.
When manually calculating the NPV a complex formula is needed, but Excel has two functions which can easily calculate the NPV.
These functions are NPV and XNPV, with the main difference between the two being how they treat dates in their calculations.
Download File
If you would like to follow along download the attachment below.
What is NPV
Net Present Value (NPV) measures the present value of future cash flows minus the initial investment. It helps evaluate whether a project is profitable based on a discount rate (often the cost of capital).
The mathematical formula for calculating NPV is:
Thankfully, Excel significantly simplifies this allowing you to calculate NPV with the following function:
=NPV(discount_rate, value1, [value2], ...)
rate – The discount rate (also known as the required rate of return or cost of capital).
value1 – A series of future cash flows (inflows and outflows), occurring at equal time intervals (e.g. monthly, quarterly, yearly).
value2 (Optional) - Any additional cash flows
How does the NPV function work
To use the NPV function you will need a dataset showing a cash flow, you will not need to include dates as the function will default to assuming the cash flows occur at regular intervals.
Once the components of your calculation are laid out you can calculate the NPV with the NPV function.
This will involve selecting the discount rate in cell C2 for the rate, and selecting the future cash flows in cells A3 to A6 as the value1.
In order to get an accurate NPV value the initial investment needs to be removed. As there is no component in the NPV function for the initial investment this needs to be included outside the function (see image below).
This would return an NPV of 388.77.
What is XNPV
XNPV is a more flexible version of NPV that allows cash flows to occur at irregular intervals.
This is useful when cash flows do not happen at equal time intervals.
The syntax for the function is:
=XNPV(discount_rate, cash_flows, dates)
rate: The discount rate
values: The series of cash flows
dates: The corresponding dates for each cash flow
How does the XNPV function work
Whereas the NPV function assumes equal time periods between cash flows XNPV needs to know the non-continuous dates you want to use in the calculation.
To use the XNPV you will need a dataset that includes the dates you expect to receive the future cash flows.
Once the components of your calculation are laid out you can calculate the NPV with the XNPV function.
Like with the NPV function, the initial investment needs to be included at the end.
This would return an NPV of 706.12.
Key Differences Between NPV and XNPV
Conclusion
Excel has two functions that calculate the net present value, NPV and XNPV.
If the cash flows for an invested are expected to be at regular intervals, like monthly, quarterly or annually, then the NPV function is useful for structured cash flows.
When you have irregular cash flows, then the XNPV function should be used as this allows for the specific dates of the cash flows to be entered.
Comentarios