Circular Reference Row by Row

[reapeat]

Hello all!

I am at a loss to figure this one out and I can't find a thread to explain it. It may be because I'm not searching by the correct terminology. But here goes...

I have a speadsheet that includes circular references. This is intentional, as each successive row (next row down) is dependent on the answer from the row above it. The spreadhseet is intended to apply decision rules to historical investment data to determine ideal retirement withdrawal rates. In the event the current withdrawal rate exceeds the initial withdrawal rate AND the previous year's investment growth is negative, then the next year's withdrawal amount is equal to the previous year's withdrawal amount. Otherwise, the next year's withdrawal amount is equal to last year's withdrawal amount plus or minus an inflation adjustment.

The problem is that the current withdrawal rate is calculated based on the ending balance of the portfolio (which is calculated from the withdrawal amount). In certain years, the decision rule is applied (where the previous year's growth is negative AND the current withdrawl rate is above the initial withdrawal rate) which means it is supposed to make the withdrawal amount equal to last year's withdrawal amount. But when this happens, it effectively lowers the current withdrawal rate under the initital withdrawal rate and removes the decision rule (allowing the withdrawal amount to increase back to what it was when increased by inflation. This of course triggers the decision rule again and it creates a loop.

What I would like to do is create a way that once the rule is applied and the withdrawal amount is lowered, the current withdrawal rate becomes locked and it won't allow the rule to be reversed. It's almost like I need to be able to make the cells in the current withdrawal column only have the ability to calculate once (or be altered once each). Then the next row could be calculated. Then the next.

If anyone knows a way to do this or has some ideas, I would greatly appreciate it!

Jeff

 

[AtWork]

Im not sure the coding for something like this, nor am i sure the logic would work, but it seems you may want to try to incorporate a copy paste value as the worksheet runs so that as the formulae work themselves out you end up with just the answer values. this would probably make it a "one time use" type spreadsheet but maybe using a template file to start from would help with that. Hope this helps.