Calculate a Compounding Value

[Dwight]

Hello:

I need help calculating a compounding value in Access. Attached is a database with a table containing some sample data which I hope proves useful.

Using self-join queries I know how to calculate the percentage change between two values in a data series. However, this method (as far as I can tell) does not work for compounding. I suspect this must be accomplished using VBA.

Here is what I am after:

If I have monthly percentage changes I would like to calculate an index that begins at 100 and compounds using these monthly percentage changes.

So if month 1’s return = 5%, month 2’s = 3% and month 3’s = -1% I would like an index that begins at 100 and then has the values 105, 108.15, 107.0685.

Alternatively, the VBA code could use the raw values in my table to generate the percentage changes and then proceed to generate the compounding figure.

Ideally, I would have a public function that I could then call from a query. If someone could provide such code or direct me to a similar example I would be very appreciative.

Full disclosure: I posted this message on a competing forum but did not receive any responses so after a reasonable waiting period I am now trying this forum.

Thank you,

 

[namliam]

I have done it with 3 query's I hope it is what your looking for... However i did not find your question to match the data.... no query's which you do mention....

Anyway have a look at qryEndResult and see if thats what you need....

Regards

 

[Dwight]

Thank you Mailman. It does indeed appear to do what I need.....and I even understand it which is an added bonus.

I didn't include the queries I mentioned because I was sure they were a dead end. Previously, I had always first calculated the individual period percent changes and then tried to compound them. But it never worked.

And obviously, it gave me tunnel vision. Thanks for opening my eyes.

Dwight

 

[Dwight]

Mailman:

The calculation you provided is a Total Return figure - which works fine. But how would I calculate a compounding value if I had the percentage changes rather than the actual values?

This is where I always got hung up before.

I have attached the same database with a new table called PctData.

Thanks

 

[namliam]

Thats more tricky... doable... see attachment... but note: what happens if you click in the endresult query on the percentage field....

Greetz

 

[Dwight]

Well all I can say, is thank you but that seems inadequate. This will be very helpful to me.

I see the effects of the timer (what happens when I click) but don't understand why I need that in the code.

 

[namliam]

You need that in code because you dont have a starting point. You want to start at 100% but there is no 100%. If you create an extra entry for every index, with 100% and a date smaller than the first one available. Then you could maybe use the same methode...

Greetz