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.