### Title

Compound Interest: How do I calculate a previous related field?

### Post

I need to perform a calculation to determine the amount of money an investment would be at each month.

I have a table that contains the following:

ID Date Return Amount

1 1/31/2010 5.00% ?

2 2/28/2010 2.50% ?

3 3/31/2010 -5.00% ?

The calculation uses a set starting amount. ex. 1,000,000.

First Calculation would use the starting amount: (1,000,000 * 5.00%) + 1,000,000 "Answer: 1,050,000"

The following Calculations would need to use the Previous Amounts: (1,050,000 * 2.50%) + 1,050,000 "Answer: 1,076,250"

I want to automatically run this with out hard coding anything. I was trying to use the Get Nth Record function, but I must be doing something wrong.

If anyone has a better way of doing this, please let me know.

Get Nth Record will work but only if your records are sorted in ascending order.

Let ( N = Get ( RecordNumber ) ; If ( N = 1 ; gInitialAmount ; GetNthRecord ( Amount ; ( get ( RecordNumber ) - 1 ) ) ) )

gInitialAmount would be a global field for the initial investment. It could be replaced with a reference to a field in a related table or you could modify the above expression to have a zero record for the initial amount.