Excel tips: how to use the XNPV function

QUESTION

Can Excel calculate the Net Present Value (NPV) of cash flows over irregular periods?

ANSWER

An NPV calculation is a measure used to compare long-term investments or strategies with different cash flows over time. It is a measurement involving the time value of money

A discount rate is applied to future net cash flows to convert them all into present values. These discounted cash flows are added up to arrive at the NPV. The higher the discount rate, the less the future cash flows are worth today.

In Excel, the NPV function calculation differs from the common definition of NPV, and you need to make sure you are using it correctly. Excel’s XNPV function calculation matches the standard definition of NPV. Excel’s NPV function requires regularly spaced cash flows – each month, each quarter or each year. The first cash flow should not be discounted, but the Excel NPV function assumes the cash flow occurs at the end of the period and it discounts the first cash flow.

The XNPV function copes with irregularly timed cash flows. It uses actual dates to determine the timing of cash flows, thus providing a more accurate result.

In Figure 1, our example has a seven-year cash flow. I have hidden most of the columns to reduce the image size. It is structured with 24 monthly columns (B-Y), followed by eight quarterly columns (Z-AG) and finishes with three annual columns (AH-AJ). The NPV function does not work with this type of structure. You must use the XNPV function. I will use the term NPV in the remainder of the article to refer to a Net Present Value calculation, not the function.

Row 7 in Figure 1 has the net cash flow for the period. Row 8 has the cumulative net cash flow. Note: the dates in row 2 are all the first of the month but are formatted using the mmm-yy custom number format.

The XNPV syntax is: XNPV(Rate,Values,Dates)

Rate is the annual discount percentage rate to be used in the calculation. This is sometimes treated as a hurdle rate – a rate to be achieved or exceeded.

Values is a range that contains the cash flows. Within the Values range, inflows should be shown as positive and outflows shown as negative. This range should not contain any blank cells. Blank cells result in the #NUM! error. The first value in the range is not discounted.

Dates is a range that contains the corresponding dates for the Values range. The first date in the range must be the earliest date in the sequence and is treated as the discount date.

A positive XNPV value means that, after discounting all the future net cash flows, the value of the inflows exceeds the outflows. In general, most outflows are at the start of the project – for example, a factory is built or shares are purchased.

The formula in cell B12 is: =XNPV(B11,B7:AJ7,B2:AJ2)

With a 5 per cent discount rate (cell B11), the overall NPV for the net cash flows is $10,155. The undiscounted total net cash flow is $13,833 (cell AJ8). Row 9 in Figure 1 demonstrates how the NPV varies as the project progresses. Initially, it is negative, up to the Dec-19 quarter. The NPV becomes positive in the next quarter and then increases steadily.

The formula in cell B9 is: =XNPV($B$11,$B$7:B7,$B$2:B2)

This formula has been copied across. Cell B9 demonstrates that the first cash flow is not discounted.

Internal rate of return

The discount rate that leads to a zero NPV is called the internal rate of return (IRR). Excel has a flexible function that works the same as XNPV, called XIRR. XIRR handles irregularly timed periods.

The XIRR syntax is: XIRR(Values,Dates,Guess)

The Values and Dates arguments mean the same as the XNPV function.

Guess is an optional argument – you can supply a rate you think is close to the rate that will be achieved. If omitted, 0.1 (10%) is used.

Excel will start with this rate and will iterate 100 times, attempting to find a rate that achieves a zero XNPV.

The XIRR is calculated in cell B13 in Figure 2. The formula is: =XIRR(B7:AJ7,B2:AJ2)

I have included a second XNPV calculation in cell B14 that uses the calculated IRR from cell B13 – see Figure 2.

The formula in cell B14 is: =XNPV(B13,B7:AJ7,B2:AJ2)

If the net cash outflows vary between positive and negative, it may be mathematically possible to have more than one IRR rate that will return a zero XNPV. You should always confirm the calculated XIRR by using it as the discount rate for a XNPV calculation. Using a different Guess argument may result in a different IRR result.

In terms of the timing of the discounting, the first date in the cash flow is deemed to be the discount date, or now. In the example in Figure 2, the date that is treated as now is 1/7/2017. This becomes more important if you are using XNPV to compare projects with differing start times. You should ensure all the cash flows being compared use the same start date.

Note: All dates used in XIRR and XNPV calculations are truncated, which means that if there is a fraction in the date, it will be removed, not rounded. A fraction in a date represents a time – for example, .75 represents 6pm.

Increased IRR accuracy

The XIRR only performs 100 iterations to arrive at a rate. If you need extreme accuracy for the IRR calculation, you can use Excel’s Goal Seek feature. This feature is on the Data ribbon, in the What-If Analysis drop-down. This can perform more than 32,000 iterations. I will demonstrate this technique in the companion video (see intheblack.com/thismonth).

Note: This is a manual process and does not dynamically calculate the IRR. Both XNPV and XIRR were added in Excel 2007. Prior to that, they were included in the Analysis Toolpak add-in.

The companion video and Excel file may assist your understanding.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]