XNPV Function in Microsoft Excel

From ForcaWiki, the simple encyclopedia

The net present value of the lemonade stand assumed money arrived in tidy, evenly spaced chunks, like exactly once a year. Real life is rarely that polite. A lemonade stand might earn a little in July, a little more the following March, and more still the August after that, on no neat schedule at all. XNPV is the Microsoft Excel formula built for exactly that kind of messy, real world timing.

Counting days instead of years

Plain NPV shrinks each future payment down based on how many whole years away it is. XNPV is more precise: it counts the exact number of days between each payment and the very first one on your list, then shrinks each payment down by that exact amount of time, continuously, rather than in yearly jumps. Wait fewer days, and a payment shrinks less. Wait more days, and it shrinks more.

A worked example

Picture the lemonade stand again, but with real dates this time instead of clean, once a year payments:

• January 1, 2024: you spend 50 dollars setting up the stand.
• July 15, 2024: you earn 30 dollars, 196 days later.
• March 3, 2025: you earn 20 dollars, 427 days later.
• August 20, 2025: you earn 25 dollars, 597 days later.

Using an assumed rate of 10 percent per year, XNPV adds all of this up, carefully shrinking each payment based on its exact date, and lands on about 17.78 dollars. That is still a good, positive deal, just like the tidier version in the net present value example, but XNPV gets there using the real calendar instead of a rough yearly guess.

How to use it

1. Open Microsoft Excel and enter your dates in one column, for example A2 through A5, as real Excel dates such as 1/1/2024, 7/15/2024, 3/3/2025, and 8/20/2025.
2. In the column next to it, enter the matching cash flow for each date, for example B2 through B5 as -50, 30, 20, 25. The very first number must be negative, since it is money going out to set things up.
3. Click on an empty cell where you want the answer to appear.
4. Type =XNPV(, then type your yearly rate as a decimal, such as 0.1 for 10 percent, followed by a comma.
5. Select your cash flow numbers, B2:B5, add another comma, then select your dates, A2:A5, and close the parenthesis, so the formula reads =XNPV(0.1, B2:B5, A2:A5).
6. Press Enter. Excel calculates the answer instantly, about 17.78 for this example.

Fun facts

• The "X" in XNPV is a hint about exact dates, separating it from the plain NPV function, which only assumes even, once a year gaps.
• The earliest date in your list becomes "day zero" automatically. It does not need to be today, it is simply whatever date happens to come first in your data, and every other payment is measured forward from there.
• XNPV has a partner function, XIRR, the exact date version of IRR, the same way XNPV is the exact date version of plain NPV.