Compound Interest: How do I calculate a previous related field?
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.