A very useful feature and timely too. Thanks for adding it
I tried to make mid-year adjustments, today, against my initial balances, a few questions came up. Not sure if its user error or not! Perhaps those of use that have used this feature can comment. I am running PRC2022.2.10-0rck7c version of Pralana Gold.
1. When making adjustment for my Regular Investment Accounts, I find the adjustment excessive, in that the negative adjustment is nearly half of my initial balance! Didnt expect that significant of an adjustment since these accounts are down about 9% YTD. All other investment category (IRA, RoTH) adjustments seem inline with my expectations. Ran the adjustment excercise twice (in a reopened data file) just in case I fat-fingered my data input. Still remains an odd adjustment for the regular investment accounts. Any one else see this behavior?
2. As a separate issue, I noticed that when I run the adjustment process twice on the same day (adjusting the previously made adjustment), that there was still a small adjustment made (small, like hundreds of dollars). While there was a small adjustment, I guess since I ran this excercise back to back on the same day (within minutes of each other), I would have expected NO further adjustment. Not sure if the proration is by day or day and time! Any thoughts?
3. Tagging on Q2....When making future "adjustments to adjustments", assuming that this allowed, do I simply run the Mid-Year Adjustment process again and Pralana will calculate and rewrite the adjustment previously made? Or, should I delete the old adjustments (cell is NOT locked) first and proceed as if it was the first time making a Mid-year adjustment? Does deleting old adjustment values create any issues?
4. When entering an adjustment, I noticed that both Spouse Tax-deferred and Roth IRA input dialogs lost focus. That is, the input dialogs did not display on top of all other windows but instead were "buried". This happened consistantly.
Hi Scooteriam, I'll try to address your questions by number below:
1. I set up and ran a test and did not see any odd behavior. Specifically, I set each of the TD accounts, the Roth account and the regular taxable account with initial balances of $250K and then told PRC that as of today (June 14) each of those accounts had an actual balance of $225,000, which is down 10% YTD. The calculated corrections were roughly $40K for the husband's TD account, $33K for the wife's TD account, $33K for the Roth account and $57K for the taxable account. These differ because the expected annual returns are different. The actual formula for the correction = balance.actual - (fraction.of.the.year.completed x annual.increase.amount.expected.in.this.account + starting.balance). So, a big factor here is how much you're expecting the account balance to change during the year, including growth and new contributions. Also, the tool assumes that the account balance changes in a linear manner throughout the year and this is probably not the way it works in reality, so this can lead to some error in this attempt at a mid-year correction.
2. Proration is on the basis of days of the year (365 increments per year). Logically, subsequent executions of the adjustment algorithm should yield zero changes;HOWEVER, the amount of change in the account balance is a function of the starting balance because growth = ROR x balance at the start of the year. As a result, subsequent executions of the adjustment algorithm will produce additional small changes.
3. It shouldn't be a problem to repeatedly run the adjustment algorithm; it'll make additional corrections based on the current date.
4. I don't understand your message on this one. I tried to replicate this behavior on both Windows and Mac machines and didn't observe anything unusual. What system configuration are you running?
Stuart Matthews
Thanks very much Stuart for having a look. I appreciate it!
1. I took things back to basics to try to get a handle on this problems. Still stumped!
- Reinstalled a fresh copy of v2.11
- Created a new export file and used simple inputs with initial balances of 250K for each account type. No other changes to default figures such as RoR, AA etc.
- Ran the mid-year adjustments process
- Used current balance of $225,000 for each account type and accepted the adjustment figure offered by the program.
- Result: I found that the regular investment account type made a much different that expected adjustment, approximatly 10x lower than expected >>> (3,527) was the actual number. See attached image.
Turning to my my actual data (export) file, I reran this mid-year adjustment process again using the fresh copy of v2.11. The results for the Regular Investment Accounts showed a much higher than expected adjustment. I validated the RoR settings and they dont seem to support such a high adjustment amount, given the formula you shared. The problem only seems to be with the Regular Investment Account type. I can share with you, privately, both export files I am using.
2. Understand. Thanks!
3. Perfect. Thanks!
4. I traced this issue back to using extended monitors. Using a single monitor (my laptop), the input dialogs show up, as expected, on top of all other windows. When I have Pralana showing on my extended monitor, the input window is displayed on the primary monitor (my laptop) and hidden in the taskbar. So....I found the source of this "window focus problem" :). Perhaps there is a windows setting here, but now that I found the source of the behavior, no big deal!
FWIW,
- As far windows environments, i am running Windows 10 Pro, build 19044.1741. I am running MS Excel 2019 MSO (Version 2205 Build 16.0.15225.20028) 32-bit
- I also run a number of self-built macros for the purposes of extracting Quicken's Lifetime Planner data. But, I have not (to my knowledge - Im not expert in this area) alterned any default macro settings in Excel.
- I have noticed this mid-year adjustment issue going back to some of the first versions that contained this feature. I cannot remember exactly which one.
Thanks for your help, Stuart!
@scooteriam I guess I need to ask you to email me your export file (mail@pralanaconsulting.com) so I can see exactly what's going on in your case. Thanks!
Stuart
@smatthews51 This seems like a very useful new feature. However, I'm confused about how it works.
The user's manual instructs to enter current balances. However, my understanding is that the goal of this tool is simply to allow entering values to reflect unexpected market performance. Current balance = [opening balance] + [unrealized gains] + [dividends. interest, and distributed capital gains] + [deposits & withdrawals]. Deposits should already be calculated from the Income tables and withdrawals calculated automatically based on cashflow, so it seems like the adjustment entered here should be based only on [unrealized gains] + [dividends. interest, and distributed capital gains]. So, if the input for this calculation is in the format of current balance, then the data entered should be [opening balance] + [unrealized gains] + [dividends. interest, and distributed capital gains] and should not include the value of [deposits & withdrawals].
Example: Opening balance of $1,00,000, withdrawals of $100,000, unrealized loss of $50,000, dividends paid of $5,000, so actual current balance is $855,000, but in order to input unexpected market performance the value entered in response to the question of "What is the current balance?" should be $955,000 ($1,000,000 -50,000 + 5,000), not the actual current balance of $855,000. Is this correct, or am I missing or misunderstanding something?
@black186 What you're missing is that this is a simple mechanism that doesn't have any knowledge of how you get from your starting balance to your ending balance. In any given year there could be a variety of events that cause you to miss your estimates, such as market performance, some windfall income, or some unexpected expenses. An easy way to compensate for all of this mid-year is to simply adjust the starting balance and that's all this feature does. And it's definitely not perfect because it doesn't deal with any of your details. It simply assumes that if you're halfway through the year, for example, your account balancer should be halfway between the starting balance and the projected ending balance. If it's different, based on your current balance, it calculates the adjustment to the starting balance.
