Re-balance Utility

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

Re-balance Utility

Postby WoodSpinner » Fri Jun 16, 2017 8:36 am

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.

[i]**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 Fri Jun 16, 2017 11:53 am, edited 2 times in total.

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

Re: Re-balance Utility

Postby 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: 1209
Joined: Sun Jun 24, 2012 7:19 pm

Re: Re-balance Utility

Postby 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: 38129
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Re-balance Utility

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

Re: Re-balance Utility

Postby WoodSpinner » Fri Jun 16, 2017 11:04 am

Link is fixed. Thanks for the directions

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

Re: Re-balance Utility

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

Re: Re-balance Utility

Postby 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: 38129
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Re-balance Utility

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

Re: Re-balance Utility

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

Re: Re-balance Utility

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

Re: Re-balance Utility

Postby WoodSpinner » Mon Jun 19, 2017 6:38 pm

Polite Bump,

Wondering if anyone sees this as a useful utility?

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

Re: Re-balance Utility

Postby 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: 2069
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Re-balance Utility

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

Re: Re-balance Utility

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

Re: Re-balance Utility

Postby 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


Return to “Personal Finance (Not Investing)”

Who is online

Users browsing this forum: afan, Baidu [Spider], DomDangelina, guitarguy, kalynda, Pranav, Sanjuan, zakath47 and 85 guests