Google produced some different documentation with a formula: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_XNPV_fu... XNPV = sum_i(pay_i/(1+rate)^((d_i-d_0)/365)) where pay_i is the i'th payment/receipt, d_i is the i'th date (in terms of whole days), and i runs from 0. So if I want to hack XNPV to compute sum_i(pay_i*exp(-(d_i-d_0)/madays)), then I equate the terms: pay_i/(1+rate)^((d_i-d_0)/365) = pay_i*exp(-(d_i-d_0)/madays) Solving for rate in terms of madays, we get rate = exp(365/madays)-1. (This formula is unstable when madays is >> 365.) In particular, if madays=3, then rate=6.9x10^52 ! No wonder I'm getting insane numbers ! So, #1 Although the XNPV documentation _said_ that at least one number had to be negative, the software (thankfully) doesn't check that & computes the correct answer anyway. #2 It _is_ possible to hack XNPV to compute a moving average, although the "interest rates" required to do it are absurd.
I was hacking a spreadsheet today & wanted to compute a moving average. I wasn't terribly particular about which moving average, but didn't find any moving averages in the list of functions that the spreadsheet program (OpenOffice Calc) offered.
So I started looking for other functions that could be hacked to compute a moving average. Since the various "present value"-type functions offered a "discounting interest rate", I figured that I might be able to hack one of them to provide a moving average for a series of values.
The first problem is the lack of rigor in the definition of these financial functions. I speculate that all OpenOffice has to do is to match whatever Microsoft Excel does; the documentation is completely irrelevant. Furthermore, Microsoft doesn't have to define their functions either; merely by being the de facto standard, their functions do what they do, and any attempt to change them would destroy the financial world. Which leaves us to experiment with these functions like some phenomenon in the natural world -- we will never know for sure what these functions were attempting to compute, but we can make models of what we think they might have been trying to compute and test these models against the actions of Excel/Calc.
I went to Wikipedia, and even that description left a lot to be desired in rigorously defining what function we're trying to compute.
For example, when discounting, does one discount using simple interest or compound interest?
A mathematician instantly goes to the limit & suggests continuous compounding -- i.e., exponential weighting. Thus, given a (constant) instantaneous interest rate i, $100 today (t=0) is worth $100*exp(i*t) at time t, whether t is positive _or_ negative.
But I didn't see exponentials in any of the definitions of net present value.
Also, the net present value functions found in spreadsheet programs all seem to want some positive values and some negative values. While making investments (negative values) and collecting returns (positive values) may have been the initial impetus for computing net present values, there's no mathematical reason for requiring both positive and negative values in order to be able to compute net present values.
(On the other hand, when one is computing _internal_ rates of return, the interest rate is undefined in the case where all of the values are positive, or all are negative.)
---
I've been able to cob together an approximation to a moving average built upon the XNPV function, but I get some pretty insane discounting interest rates, which makes me believe that I'm not modeling XNPV very well.
Suppose, for example, that I have a series of 30 numbers and I'd like a moving average of approx 3 of these numbers. If we set up a series of dates of 30 consecutive days, then we will need a pretty hefty interest rate to discount everything past approx 3 days. We'd need a discount rate of the order of magnitude of 33% per day, or on the order of 12,000% per year.
However, when I plug in numbers of this sort into XNPV, I don't get the effect of a moving average of approx 3 days.
Has anyone else managed to figure out what these spreadsheet functions actually do?