If you want to add a cell displaying total lifetime federal taxes to the Taxes View, add additional columns calculated from ones in the View, etc., you can do this using a linked workbook. Open a new workbook, on the worksheet, click on the cell you want to bring data to, and then type = and go to the Pralana spreadsheet and click on the cell you want to pull the data from and hit enter. The cell will show the link to the file and the cell referenced like: ='[Pralana_filename.xlsm]View4'!$B$11
I created a copy of the Taxes View, after I had added the RMDs, Roth Conversion, and Net Worth columns, by selecting the first year: ='[Pralana_filename.xlsm]View4'!A12 and copying that down the column until all of the years showed up and then copying all of those cells across until all of the columns had data. Above this, I added a couple of extra rows and in there added Total Taxes summing up each of the tax columns. Above that I then linked to each of the individual column headers. Now I can also add additional columns or rows with calculated cells wherever I want.
This can obviously be repeated on additional sheets for each view. Because the Pralana workbook uses "Views", it doesn't matter which one you are looking at in it, .. you can look at any of them in the new Enhanced Reporting Workbook at any time. Also, selecting the Scenario reflects in all of the reporting views in the Enhanced Reporting Workbook. It will affect all the "Views" at once as it does behind the scene in Pralana.
You can also make new sheets freezing the data by simply adding a sheet, Click on the corner just above the 1 and left of A on the sheet to copy. This will select the whole sheet. Right Click Copy, go to the new sheet, click the same place on it and right click, Paste Special -> Values and Number Formatting. This will be a frozen copy of the view as of that moment. You can repeat this for each scenario and then make a sheet that combines/compares all 3 scenario values for that view.
Note the referenced cell will be something like $B$11 When you copy this formula, the $ signs lock in the column/row/both depending on which has the $ in front of it. Search for "Excel Cell References : Relative Reference, Absolute Reference, Mixed Reference" on how the $ affects copying the cell...
I hope this helps everyone get even more out of this tool.
Thanks for posting this tip.
I have been using Tabular Projections > View Management in PRC to accomplish something similar. I start by un-hiding all of data columns in View Management. Then I copy and paste the entire PRC View Management data table for a specific scenario into a separate Excel worksheet. To the right of the data set copied and pasted from PRC I add additional columns to do analysis not performed by PRC.
The section below shows the columns I have added to help me understand the cumulative impact of doing versus not doing Roth Conversions on asset values at year-end.
Each row shows the cumulative change in assets values at the end of each year (years not shown) so that I can value a specific Roth conversion scenario at any point in time.
I always start by modeling a "Do Nothing - No Roth Conversions" base case in PRC and copying and pasting that data into the separate Excel worksheet. Once I get the worksheet set up with the additional columns I copy the combined data set it to separate section of the worksheet and label it "Scenario 1 Baseline -Do Nothing". Then I go back in PRC and modify the base case scenario data to reflect another Roth Conversion Strategy I want to model. Then I re-copy and re-paste the data for the revised scenario into a copy of the combined baseline data set I created for the base case. By doing that the cumulative columns for the revised scenario automatically update based on the revised data set copied and pasted into the worksheets from PRC.
I want to develop a similar method to measure and analyze cumulative income at year end, but I haven't had the time to figure out the best way to accurately calculate that using PRC data given the cash flow calculations done by PRC. One of my goals when evaluating Roth IRA conversions is to measure both pre-tax and after-tax income so that the cumulative maximization of after-tax income can be considered along with the maximization of after-asset values at any year end. One can maximize assets by not spending them, but maximizing after-tax spendable income may be a desirable goal for many retirees. I would love to hear suggestions form others on how pre-tax income might be simply calculated.
One final note: In my worksheet, I establish the 28.00 % shown for the "Assumed Rate of Accrued Income Tax Liability on Tax Deferred Savings" as a variable I can simply change within my worksheet. This makes is easy to recalculate the after-tax value of tax deferred savings based on different tax rate assumptions.
| Scenario 1 - Baseline - Do Nothing - Withdraw from Taxable Account first | |||||||||||||||||||||||||||||
| Totals | Calculations of Ending Plan Value | ||||||||||||||||||||||||||||
| Total Savings | Assumed Rate of Accrued Income Tax Liability on Tax Deferred Savings | Accrued Income Tax Liability on Tax Deferred Savings | After Tax Value of Savings | Cumulative Net Change in Taxable Accounts | Cumulative Net Change in T-IRA Accounts | Cumulative Net Change in Roth IRA Accounts | Cumulative Net Change in All Accounts | Cumulative Federal Taxes Paid | Cumulative State Taxes Paid | Cumulative Total Taxes Paid | Cumulative Total Taxes Paid + Accrued Tax Liability on T-IRA | ||||||||||||||||||
| $3,000,000 | 28.00% | $560,000 | $2,440,000 | $560,000 | |||||||||||||||||||||||||
| $3,119,887 | 28.00% | $593,600 | $2,526,287 | ($24,113) | $120,000 | $24,000 | $119,887 | $15 | $98 | $113 | $593,713 | ||||||||||||||||||
| $3,247,004 | 28.00% | $629,216 | $2,617,788 | ($49,636) | $247,200 | $49,440 | $247,004 | $15 | $174 | $189 | $629,405 | ||||||||||||||||||
| $3,381,771 | 28.00% | $666,969 | $2,714,802 | ($76,668) | $382,032 | $76,406 | $381,771 | $15 | $228 | $243 | $667,212 | ||||||||||||||||||
| $3,553,862 | 28.00% | $706,987 | $2,846,875 | ($76,083) | $524,954 | $104,991 | $553,862 | $794 | $264 | $1,058 | $708,045 | ||||||||||||||||||
| $3,735,734 | 28.00% | $749,406 | $2,986,328 | ($76,008) | $676,451 | $135,290 | $735,734 | $2,118 | $300 | $2,418 | $751,824 | ||||||||||||||||||
| $3,928,517 | 28.00% | $794,371 | $3,134,146 | ($75,929) | $837,038 | $167,408 | $928,517 | $3,442 | $336 | $3,778 | $798,149 | ||||||||||||||||||
| Scenario 2 - Roth IRA Conversions 24% / 28% Tax Brackets - Aggressive Roth Conversions Deplete T-IRA by Age 75 - Tax rates not smooth - Taxes paid too soon. | |||||||||||||||||||||||||||||
| Totals | Calculations of Ending Plan Value | ||||||||||||||||||||||||||||
| Total Savings | Assumed Rate of Accrued Income Tax Liability on Tax Deferred Savings | Accrued Income Tax Liability on Tax Deferred Savings | After Tax Value of Savings | Cumulative Net Change in Taxable Accounts | Cumulative Net Change in T-IRA Accounts | Cumulative Net Change in Roth IRA Accounts | Cumulative Net Change in All Accounts | Cumulative Federal Taxes | Cumulative State Taxes | Cumulative Total Taxes | Cumulative Total Taxes Paid + Accrued Tax Liability on T-IRA | ||||||||||||||||||
| $3,000,000 | 28.00% | $560,000 | $2,440,000 | $560,000 | |||||||||||||||||||||||||
| $3,031,367 | 28.00% | $502,908 | $2,528,459 | ($112,633) | ($203,900) | $347,900 | $31,367 | $69,979 | $18,654 | $88,633 | $591,541 | ||||||||||||||||||
| $3,064,745 | 28.00% | $441,444 | $2,623,301 | ($231,895) | ($423,413) | $720,053 | $64,745 | $139,830 | $37,308 | $177,137 | $618,581 | ||||||||||||||||||
| $3,100,261 | 28.00% | $375,291 | $2,724,970 | ($358,177) | ($659,675) | $1,118,113 | $100,261 | $209,544 | $55,961 | $265,505 | $640,797 | ||||||||||||||||||
| $3,169,438 | 28.00% | $310,856 | $2,858,582 | ($460,506) | ($889,800) | $1,519,745 | $169,438 | $279,115 | $73,229 | $352,344 | $663,200 | ||||||||||||||||||
| $3,266,404 | 28.00% | $273,097 | $2,993,307 | ($545,338) | ($1,024,654) | $1,836,395 | $266,404 | $331,496 | $84,049 | $415,545 | $688,642 | ||||||||||||||||||
| $3,369,284 | 28.00% | $222,515 | $3,146,769 | ($600,000) | ($1,205,305) | $2,174,589 | $369,284 | $383,781 | $94,868 | $478,649 | $701,164 | ||||||||||||||||||
