Re-balance Utility - New Version (v2)

[WoodSpinner]

[Utility updated, see below. --admin LadyGeek]

See Rebalancing Utility Version 2 post below
All,

I put together an Excel Utility to help me, my DW and DD understand how to perform a number of key activities during retirement. It is designed to align the Portfolio to the Tax Efficient Placement of Funds.

• - Re-balance between Equities, Bonds, and Cash to meet our target Asset Allocation
  - Roth Conversion - what assets to Sell or move In-Kind and where to move them
  - RMD Distribution - what assets to Sell or move In-Kind and where to move them
  - Fund Expenses - what assets to Sell and where to move the Cash
  - QCDs - what assets to Sell or move In-Kind

It deals with 3 types of accounts:

• - IRA/401K (Tax Deferred)
  - Roth (Tax Advantaged)
  - Taxable

3 Types of Assets

• - Equities
  - Bonds
  - Cash

Version 1 is fairly straightforward, only enter information into fields that are in Purple

There is a large Button - Perform the Magic which invokes an Excel Macro to perform the function requested.

A couple of quick tips:
- This can easily be hooked into an Asset Tracking spreadsheet to automatically gather the current actuals and Asset Allocations which greatly simplifies the process.
- It is a great tool to help illustrate some key Retirement concepts to your Significant-Other if they aren't as financially savvy or interested.

Entry Screen Enter fields in Purple


Calculation Results


You are welcome to download and use this utility if you like Re-Balance V1.

**Note: This utility does depend on an custom set of Excel Macros and you will have to Enable this in Excel

I am very interested in your feedback:

• 1. Is it Easy to Use?
  2. Did it work as you expected?
  3. Will you make use of this again?
  4. What should I add in the next version?

Feel free to Private Message me with any questions, concerns, or suggestions.

I hope you find this useful! It's now part of my Retirement toolset.

WoodSpinner

[LadyGeek]

Hi,

The link is broken. You can edit your post using the "pencil" in the top-right corner. Then, bump the thread to say it's been fixed.

Be sure the document has been first been "published", then shared as "anyone with a link can view." Just in case: Share files from Google Drive.

Also, be sure to remove all personal info using "Inspect Document". Tip: Be sure there's no personal info in the VBA macros - Excel won't check for this.

The wiki has some background info: Rebalancing

Instead of PM, let's have a discussion here - everyone can participate.

Update: Fixed post as noted below.

[whaleknives]

The link is broken. You can edit your post using the "!" in the top-right corner. Then, bump the thread to say it's been fixed. . .

I think you mean "Edit post" pencil icon.

[LadyGeek]

Yes, I did. It's fixed.

[WoodSpinner]

Link is fixed. Thanks for the directions

[LadyGeek]

I downloaded the spreadsheet and am using MS Excel 2016. With this input:

Distribution Type: Roth Conversion
Distribution Method In-Kind
Rebalance? Yes
Distribution Amount $s: $2,000.00
Expenses to Fund $s: $2,100.00
QCDs to Fund $s: $2,000.00

Target asset allocations not equal to 100% are putting your macros into an endless loop or just hanging. I recommend adding an error check for asset allocation <> to 100%, don't allow the macro to run until this is done.

Cell A9 has a typo. "Target Aset Allocation %" should be "Target Asset Allocation %". Also, save the program with the cursor in Cell A1. Otherwise, you'll miss the top part of the worksheet.

Hit Escape to stop execution. If that doesn't work, close the window and select "Restart the program" to jump into the debugger.

[WoodSpinner]

LadyGeek,

Both of the issues you identified have been resolved -- many thanks!

[LadyGeek]

Next round:

Typo in D4, "Distriutions" should be "Distributions".

Could you provide some instructions, perhaps add an "Instructions" sheet and a few examples? Feel free to link back to this thread for reference.

For example, "This spreadsheet adjusts your asset allocation as you make withdrawals from your retirement accounts... You can select 3 types of distributions... You can fund a Qualified charitable distributions..."

When I zero-out the assets (B13:D15), cell D4 shows the error message "Not enough money in the IRA/401K for Distriutions and/or QCDs", but D5 does not. Shouldn't D5 be showing the error message?

Row 4, "Distribution Amount $s:", add a description in C4 "from IRA/401k"
Row 5, "Expenses to Fund $s:", add a description in C5, "from Roth and taxable"

I'm unclear on the use of the Escrow account. Isn't this taxable (savings) that's part of your cash account?

[WoodSpinner]

LadyGeek,

Will do--Out of town for weekend but will implement your suggestions.

Stepping back, do you think this will be a useful tool?

It works for me, just not sure if I am atypical.

TIA

[WoodSpinner]

LadyGeek,

I have implemented your suggestions as requested plus done some cleanup on the Error Handling.

I appreciate your eagle eye.

TIA

[WoodSpinner]

Polite Bump,

Wondering if anyone sees this as a useful utility?

[LeeMKE]

I love trying out new tools. Unfortunately, my version of Excel won't run the ActiveX code in the spreadsheet, so I'm out of luck (Excel for Mac 2011)

Looks interesting. I just can't get it to run my data.

[AlohaJoe]

It looks like a good effort. It looks like it is limited to just stocks/bonds/cash as the assets for Roth conversion/RMD/rebalancing. If someone has more of a slice-and-dice allocation, do you have suggestions on how they'd use it?

[WoodSpinner]

I love trying out new tools. Unfortunately, my version of Excel won't run the ActiveX code in the spreadsheet, so I'm out of luck (Excel for Mac 2011)

Looks interesting. I just can't get it to run my data.

Wow -- that is really interesting. Let me see if I can put together a different type of button to invoke the macro.

[WoodSpinner]

It looks like a good effort. It looks like it is limited to just stocks/bonds/cash as the assets for Roth conversion/RMD/rebalancing. If someone has more of a slice-and-dice allocation, do you have suggestions on how they'd use it?

For Version-1 it gives you a high level strategy and you would need to figure out what makes sense based on the details of your holdings.

For an enhancement possibility, I have a couple of questions:

• 1. How do you make your decisions today?
  2. What are the key pieces of information about each asset that you use?

The underlying code is pretty flexible but I need to get a better handle on a good approach before I can figure out how difficult it would be to code.

TIA

[WoodSpinner]

All,

Based on earlier feedback, I have updated the utility, added significant logic and improved the usability.

Download the utility from here. Note: this utility does require Excel and uses a set of custom built VBA macros.

This utility is designed to perform a number of key activities during retirement in a manner that is designed to align the Portfolio to the Tax Efficient Placement of Funds.

- Re-balance assets held in an IRA/401K, Roth, or Taxable accounts.
- Roth Conversion - Determine which assets to Sell or move In-Kind
- RMD Distribution - Decide which assets to Sell or move In-Kind
- Fund Expenses - Determine which assets to Sell and from which account
- QCDs - Decide which assets to Sell or move In-Kind

Version 2 is fairly straightforward, only enter information into the fields that are in Purple


Next you need to enter information about the Assets in your Portfolio and which accounts they are located.


There is a large Button - Perform the Magic which invokes an Excel Macro to perform the function requested.


Expenses and QCDs are moved to an area I call Escrow Accounts for convenience--in reality you will relate this to your portfolio and plans.


Based on the Actions you Requested, the utility will optimize an execution plan and display the results:


A couple of quick tips:

• - This can easily be hooked into an Asset Tracking spreadsheet to automatically gather the current actuals and Asset Allocations which greatly simplifies the process.
  - The Re-balancing Threashold parameter is very useful for focusing the analysis on the assets that are significantly out of balance. It is also useful if your IPS indicates a need to Re-balance based on a Percentage difference from your Target Allocation.
  - It is a great tool to help illustrate some key portfolio management concepts to your Significant-Other.

Approach used for the Actions Requested:

Not sure if this utility will be of interest to anyone but DW and I have found it extremely helpful.

If you have any feedback or suggestions, please feel free to post via this thread.

Enjoy

[LadyGeek]

For continuity of the discussion, it's best to keep all the information in one spot. I merged WoodSpinner's update back into the original thread.

[WoodSpinner]

Bump after Lady Geek combined posts

[mav12]

Hello admins,

Where does one find the link to the V2 ?
Thank you

[WoodSpinner]

You can find it here ....

https://drive.google.com/open?id=0B7GFv ... lk1Mkw4ekE

Let me know how it works for you. We have been using it with good success. A few instances where it couldn't work out a solution and I have to manually attack it.

Thanks

WoodSpinner