We need the Excel XNPV function to calculate the net present value of a given investment with a given discount rate when the cash flows come at an irregular intervals. Here are pro tips on using the Excel XNPV function in excel to get the NPV given irregular cash flows
Figure 1: How to use XNPV to find NPV with irregular cash flows
General syntax of the formula
=XNPV (rate, values, dates)
Where;
- Rate- this is the discount rate to be applied on the cash flow
- Value – values that represent the cash flows
- Dates- fates corresponding to cash flows.
Understanding the formula
- The above formula can help us calculate the NPV for an investment when the cash flows are irregular given a certain interest rate.
- To work with the function, we need to first arrange the dates in a chronological order.
- The negative cash flows will represent the cash paid out, while the positive value represent cash received.
- In our example above, we have our formula in cell F5, which is
=XNPV(F4,B3:B7,C3:C7) - It is important to note that the XNPV does not discount the initial cash flow.
- All the other subsequent payments are discounted based on a 365-day year.
- If you want to discount a given valuation date, you should set up the XNPV so that the first cash flow which is associated with the valuation date is zero.
- It is also necessary to note that the XNPV function can only work correctly when valid excel dates are used, and should be arranged in a chronological order.
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment