B
Ben
I'm trying to do time value investment calculations in a small Access
database I've been building.
For the sake of this discussion, we're talking about a source table
with two columns, Period and PercentReturn, where Period is the last
day of a given month (and the primary key), and PercentReturn is how
much an investment is up or down in a given month. So for an
investment that started in January of 2003, the beginning of the table
would look something like:
....
1/31/2003 2.2%
2/28/2003 -1.1%
3/31/2003 2.4%
4/30/2003 6.4%
5/31/2003 2.8%
....
Now here's where it gets a bit dicey: I want to add a year-to-date
return column, let's call it YTDPercentReturn, based on a "daisy
chain" geometric progression. Here's the trick -- the YTD logic is
such that it needs to access the previous cell in the SAME COLUMN.
The query logic is, in essence:
YTDPercentReturn = (1 + PercentReturn) * (1 +
LastMonthsYTDPercentReturn) - 1
How would I do this? Perhaps via a subquery or VBA?
Any help would be greatly appreciated.
Ben
database I've been building.
For the sake of this discussion, we're talking about a source table
with two columns, Period and PercentReturn, where Period is the last
day of a given month (and the primary key), and PercentReturn is how
much an investment is up or down in a given month. So for an
investment that started in January of 2003, the beginning of the table
would look something like:
....
1/31/2003 2.2%
2/28/2003 -1.1%
3/31/2003 2.4%
4/30/2003 6.4%
5/31/2003 2.8%
....
Now here's where it gets a bit dicey: I want to add a year-to-date
return column, let's call it YTDPercentReturn, based on a "daisy
chain" geometric progression. Here's the trick -- the YTD logic is
such that it needs to access the previous cell in the SAME COLUMN.
The query logic is, in essence:
YTDPercentReturn = (1 + PercentReturn) * (1 +
LastMonthsYTDPercentReturn) - 1
How would I do this? Perhaps via a subquery or VBA?
Any help would be greatly appreciated.
Ben