Let say I have this kind of data:

1 01/1/1980
2 01/2/1999
3 03/12/2000
-1 03/6/2005
-5 07/07/2007


how can I calculate the Present Value (PV) to them in respect to current date, let say with 5% interest rate, in spreadsheet? Current date means today.

[Update] I am probably misunderstanding terms PV and FV. I am trying to find some ready function similar to this one. Whatever method you use it must work with the above data. Please, stop spam.

-
Just emulate en.wikipedia.org/wiki/Net_present_value#Example –  whuber Jan 28 '11 at 22:20

This sounds more like you want the Future Value. The Present Value is usually defined by valuing cash flows which occur in the future, not in the past. But it amounts to the same equations.

Anyways, you multiply the values in each period by $(1+r)^{t_0-t}$ and just add the results for your PV. $r$ is your interest rate (with annual compounding), $t$ is the actual time of the cash flow (measured in years), and $t_0$ is where the Present is (also measured in years).

So to update from 01/01/1980 to 01/01/2011 is just $1\times (1+0.05)^{2011-1980}$.

Now if you want to get pedantic and include the days in your calculations, you just need to count the "left over" days and divide them by $365$ (or $365.24$ if you've gone insane with details), and put this as a decimal or fraction addition to $t$

So for updating from 03/06/2005 to 01/01/2011 we have $31+28+31+30+31+3=154$ days between 01/01/2005 and 03/06/2005 so this gives an update of $-1\times (1+0.05)^{2011-(2005+\frac{154}{365})}$

-
Its not a slow thing to do it this way. In excel it wouldn't take more than 5 minutes (and you wouldn't need to manually work out the days, just use a date format, and it will have a "number" hiding behind it, which you can use in the calculations). And you should clarify in the question precisely what it is that you want (which I believe you have done, and thank you for doing it). Otherwise you get people like me telling you something you already know. :) –  probabilityislogic Jan 27 '11 at 0:31
You cannot use the google function (FV (..,..,)) to update, because it requires evenly spaced intervals, and equal payments, both of which you do not have (at least not in the example). So you could "in theory" apply the function individually, but it is just as quick to "reinvent the wheel" (especially as you don't understand the syntax of the FV function). If you know how to calculate FV, then you don't need a special function to do it for you, unless it would save you a considerable amount of time. –  probabilityislogic Jan 27 '11 at 4:21
Accepted because it noticed the main flaw in the question, messing up with FV and PV, I feel the non-reinventing-wheel-faster-way still open. More in my reply. –  hhh Mar 13 '11 at 16:28

The question does not state the precise intervals or yields so the $H_{0}$ hypothesis must be conservative with infinite intervals and yields with pessimist approximations, .*logic's suggestion won't qualify. Confidence interval not calculated. So:

$lim_{ m \rightarrow \infty } \left[ 1+\frac{r}{m} \right] ^{mt}=e^{rt}$,

where r is the rate p.a. and t is the time. The sum is $\sum_{k=1}^{n} x_{k} e^{rt}$. If you have data of different signs, you must calculate positive numbers to one sum and negative numbers to one sum, this way you get proper upper/lower bounds. The exponent is $r_{k} MINUS(timestamp_{1}, timestamp_{2})/365$, the MINUS -function returns days between the timestamps and $r_{k}$ is the rate. The terms FV and PV are:

$FV = x_{0} \left( 1+r \right)^{n} + x_{1} \left(1+r \right)^{n-1}+...+x_{n}$

$PV = x_{0} + \frac{x_{1}}{1+r} +...+ \frac{x_{n}}{\left( 1+r\right)}^{n}$

so $FV$ is with the sum -formula, while you just take reciprocal with $PV$.

Example

1 01/1/1980
2 01/2/1999
3 03/12/2000
-1 03/6/2005
-5 07/07/2007


Work in progress.

Trying to find one-liner to operate over the data: $if (term_{k} > 0) : sum (positive_{k}e^{MINUS(t_{k},t_{0})/365})$

-
This solution requires no less calculation than the one I gave (unless $e^{rt}$ is less calculation than $(1+r)^{t_0-t}$, I hardly think so). Another thing is that the interest rate in the PV and FV calculations are not consistent in r for in one place you are annually compounding, and another place you are continuously compounding. your equations imply that $(1+r)^{t}=e^{rt}$. $r$ in the exponential should be adjusted to $log(1+r)$. –  probabilityislogic Jan 27 '11 at 17:25
how does the continuous compounding give proper bounds when there are both positive and negative terms? I understand how it does when there are only positive terms. But including negative terms means you have minimum negative (max absolute) plus maximum positive cash flows, how does this put a "bound" on the sum? –  probabilityislogic Jan 27 '11 at 17:36
another question, in your answer, why did you use an example which was different to the example data given in the question? "100EUR with 5% after 3 years" is an odd example, given that there is already example data in the question. I think if you try to use your method on the example data, and then mine (using the same degree of accuracy in time, whole years in both, decimal years in both,etc.) you will find that the take the same time to do, except that my method gives the exact FV, yours an approximate one. –  probabilityislogic Jan 27 '11 at 17:54
prob.*: updated. –  hhh Jan 28 '11 at 21:15