Re-balance Utility - New Version (v2)

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
WoodSpinner
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re-balance Utility - New Version (v2)

Post by WoodSpinner » Fri Jun 16, 2017 8:36 am

[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
Image

Calculation Results
Image

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
Last edited by WoodSpinner on Sat Jul 15, 2017 9:01 pm, edited 4 times in total.

User avatar
LadyGeek
Site Admin
Posts: 40504
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Re-balance Utility

Post by LadyGeek » Fri Jun 16, 2017 10:02 am

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.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
whaleknives
Posts: 1210
Joined: Sun Jun 24, 2012 7:19 pm

Re: Re-balance Utility

Post by whaleknives » Fri Jun 16, 2017 10:10 am

LadyGeek wrote: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. :D
"I'm an indexer. I own the market. And I'm happy." (John Bogle, "BusinessWeek", 8/17/07) ☕ Maritime signal flag W - Whiskey: "I require medical assistance."

User avatar
LadyGeek
Site Admin
Posts: 40504
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Re-balance Utility

Post by LadyGeek » Fri Jun 16, 2017 10:34 am

Yes, I did. It's fixed. :D
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

WoodSpinner
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Fri Jun 16, 2017 11:04 am

Link is fixed. Thanks for the directions

User avatar
LadyGeek
Site Admin
Posts: 40504
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Re-balance Utility

Post by LadyGeek » Fri Jun 16, 2017 12:17 pm

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.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

WoodSpinner
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Fri Jun 16, 2017 2:07 pm

LadyGeek,

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

User avatar
LadyGeek
Site Admin
Posts: 40504
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Re-balance Utility

Post by LadyGeek » Fri Jun 16, 2017 3:50 pm

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?
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

WoodSpinner
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Fri Jun 16, 2017 11:44 pm

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
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Sun Jun 18, 2017 11:09 pm

LadyGeek,

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

I appreciate your eagle eye.

TIA

WoodSpinner
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Mon Jun 19, 2017 6:38 pm

Polite Bump,

Wondering if anyone sees this as a useful utility?

LeeMKE
Posts: 1582
Joined: Mon Oct 14, 2013 9:40 pm

Re: Re-balance Utility

Post by LeeMKE » Mon Jun 19, 2017 11:49 pm

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.
The mightiest Oak is just a nut who stayed the course.

AlohaJoe
Posts: 2429
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Re-balance Utility

Post by AlohaJoe » Tue Jun 20, 2017 1:10 am

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
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Tue Jun 20, 2017 9:18 am

LeeMKE wrote: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
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Tue Jun 20, 2017 9:26 am

AlohaJoe wrote: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
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Rebalance Utility Version-2

Post by WoodSpinner » Sat Jul 15, 2017 6:32 pm

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
Image

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

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

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.
Image

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

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:
Image
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 8-)

User avatar
LadyGeek
Site Admin
Posts: 40504
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Re-balance Utility

Post by LadyGeek » Sat Jul 15, 2017 8:25 pm

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.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

WoodSpinner
Posts: 259
Joined: Mon Feb 27, 2017 1:15 pm

Re: Re-balance Utility

Post by WoodSpinner » Sat Jul 15, 2017 8:56 pm

Bump after Lady Geek combined posts

Post Reply