XNPV function

[David]

The XNPV function gives different answers (off by about 0.04%) than when
calculating NPV on an annual basis using a 360 day year. Without using XNPV,
if I take the number of years between the time periods using "=days360(start
date,end date)/360", the solving for PV, I get a slightly different answer
than with the xnpv function. Do you know what the difference is? Thanks.
David Pardue

 

[Fred Smith]

Yes, XNPV uses the proper number of days in a year (365 or 366). Why wouldn't
it? Obviously, there would be a difference using 360 day years.

 

[David]

Thanks Fred. However, that does not account for the difference unless the
rate applied to each day is different than the annual rate divided by the
number of days in the year. Banks play the game of charging interest for the
actual number of days, but calculating the daily interest as the annual rate
divided by 360, a trick that adds a few basis point to the annual effective
rate. When using the true annual interest rate, the PV should be the same
whether calculated with 360 days or 365 days. Could the XNPV function do the
same as some banks where it discounts based on actual days at a daily rate of
annual/360? That might be the reason for the difference, but it would
produce an inaccurate answer.