How do calculate Present Value in Google Spreadsheet?

Question

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.

Answer 1

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})}$

Answer 2

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})$