"ncw" wrote:
I believe this difference constitutes a nontrivial error in the XNPV
formula.

Does it? (Rhetorical.)


The XNPV value can be significantly different from the value computed
using the sum of the PVs

As I noted, that depends on how you compute the discounted cash flows
("PVs").


I am confident that the vast majority of financial professionals and
academics would agree that XNPV function should use the expression
(1+rate/365)^days instead of (1+rate)^(days/365) in the function.

You're free to have an opinion. It's not uncommon for people to take strong
stands on matters they know little about.

I am not so confident that there is a consensus, much less a "vast
majority", given that financial professionals and academics cannot agree on
how to annualize a period IRR (multiply v. compound). And I think that
would be much easier to agree upon than your issue.

My first impression was similar to yours -- not what a "vast majority" might
say, but simply which I would choose to do.

However, after a millisecond of thought, several factors lead me to think
that XNPV might be doing it correctly -- or at least, one correct way ;-).

First, note that the formula used by XNPV is similar to that used by XIRR.
That formula seems to make some sense for XIRR since it derives an annual
rate. Of course, both could be wrong. But....

Second, the XIRR formula seems somewhat consistent with my interpretation of
the (US) Truth in Lending and Truth in Savings regulations -- especially the
latter.

TIS computes APY by taking the simple rate of return (interest / principal)
and amortizing by years and fractions thereof (365 / days). That is
analogous to what XNPV does, namely discounting by years and fractions
thereof (days / 365).

TIL is more ambiguous to apply by analogy. Arguably, it allows for
fractional unit rates, for example rate/365 for daily compounding. But for
a unit period of a year -- arguably, the unit period for XIRR and XNPV --
fractional periods are computed by days/365.

Of course, TIS and TIL have nothing to with NPV and IRR, which are the
purview of financial analysis. And since I have neither time nor enthusiasm
to research financial analysis texts to see if any demonstrate how to do
such exact computations -- and I wouldn't be surprised if none does -- I
cannot offer nor dispositively dispute an opinion on what the "vast
majority" might conclude, if anything.