How do I create a predictive balance formula?
I'm not sure if this is even valid/possible, but I'm trying to create an accounting schedule which foresees upcoming payments and reflects the balance after the transaction. This image is a practice sheet with fictional values. In theory, I'd want the formula in Balance to always take the previous balance and add it to the amount, but there doesn't seem to be a way to make a column formula out of this.
The most important part is this: I want to write the formula in such a way that I can **reorder any row** and the balance will still be calculated correctly. For example, I want these rows to appear in the order that they actually occur. For example, say I've predicted row 6 to occur 7/16, but it actually takes place on 7/12 - I want to move that row up between rows 4 and 5 so that it is sorted by date, and then I want the balance calculation to accurately reflect the change over time.
What would be the best way to achieve the desired output?
Should I create a separate sheet and do the balance addition on the separate sheet?
Help Article Resources
Check out the Formula Handbook template!