Circular Reference and slow calculation

[thirstyformore]

I have a spreadsheet that projects the growth of two accounts: A taxable account and a pretax account. Growth of these accounts is projected for thirty years. The spreadsheet is set up to show withdrawals first from the taxable account and then from the pretax account. Thus, when the taxable account is totally used up, withdrawals start flowing from the pretax account.

The issue I am having is essentially a circular reference. When withdrawals are taken from the pretax account, they are subject to income tax. Consequently, the withdrawals must be “grossed-up” for income taxes. Normally, I would not have a problem with this, but the tax rates are not level. In other words, depending on the amount withdrawn (as well as other existing taxable income) the tax rate may change (i.e., the rates are graduated).

I could use iterations to do the calculations, but I have tested it with as many as 5000 iterations and in some cases it did not provide a final answer.

The spreadsheet for both the taxable and pretax account works as follows:

1. The following columns are used for the pretax account–
Column A Beginning Balance
Column B Interest/ Dividend Income and account growth
Column C Account withdrawals (transfers to taxable account)
Column D Ending Balance

Columns A+B-C=D

2. The following columns are used for the taxable account
Column E Beginning Balance
Column F Interest or Dividend Income
Column G Growth
Column H Deposit from the pretax account (received from pretax account withdrawal)
Column I Income tax (on Interest and dividend income and pretax withdrawals)
Column J Withdrawals
Column K Ending Balance

Columns E+F+G+H-I-J=K

Note, all rows underneath these columns represent projected years.

I am new to VBA programming but I set up a macro that seems to work.

I created a formula in a column L to show a 1 if the taxable account goes below zero or zero if it does not. If the column shows a one, then a macro sets column K (the taxable account’s ending balance) to zero by changing the cell in column C (the pretax withdrawal amount). The macro searches column L for a 1 for thirty rows (rows 20 through 49). This macro searches 5 different worksheets in the workbook to compute this calculation.

Basically, the macro performs a goalseek and by forcing the taxable account’s ending balance to come to zero, it forces the withdrawal from the pretax account to take income tax into consideration.

The macro works, but it can take as long as 15 minutes to calculate. This is especially maddening if you make an input error.

Does anyone know of a better way to get the results I am looking for? Any thoughts are greatly appreciated.

 

[just_jon]

Do you have a lot of in-the-cell formulas which need to be running while your macro runs in order for your macro to compute correctly? If not, you might want to try --

Application.Calculation = xlCalculateManual

 

[thirstyformore]

In the Cell calculations

Thanks just-Jon for your response. Unfortunately, I do need the cells to calculate or the macro will produce incorrect results.