XNPV Problem Dates
If you make use of XNPV in Excel (and we recommend that you do) you must be careful of some XNPV problem dates. Having said that, the issue is typically the user and not Excel.
We have performed model reviews where the developer has referred to cells that contain only the year in them e.g. 2013, 2014 etc. To a human this is reasonable and you would understand that the meaning is a full year between cash flows.
However, if you point a XNPV to these types of ‘dates’ if will assume that the cash flows are 1 DAY apart. This is to do with the way Excel handles dates, where a date is just a formatted way of saying how many days from the 1st January 1900 (to understand Dates better click here for more). The magnitude of this spreadsheet error can be significant.