Forum Discussion
Can I use an Excel sheet like a subroutine
Interesting. It might be fun to sit down together some day. I'm a life member of AAII and used to actively use their Stock Investor Pro database; had developed an elaborate spreadsheet where I could compare any two companies in the same industry with one another and average for the category, on all kinds of metrics.... Then I moved from the Windows environment to Mac....
As a life member I still get the printed magazine from AAII, but frankly pay almost no heed to it.
I am active on the Motley Fool, where I've also been a member since the 1990s......pay a lot more attention there, although recently mostly to Options, which are fun. I do my options investing (for that matter all my other investing too) within the tax-sheltered environment of a traditional IRA, so there are no tax consequences until withdrawals, and then it is what it is. But I have created spreadsheets to assess various options strategies, to track the positions I have.... I'm having a ball doing it.
To your goal...have you looked at all into Excel's Scenario Manager tool? What you're trying to do might stretch the boundaries of that tool, but it might serve the purpose. I've not used it enough to give you any specific suggestions. Perhaps some others here in the techcommunity can.
Gary -- What you've created is ambitious. Your reference to not having done much Excel work since the late 80s makes me think you must be approaching (if not already in) retirement. Also as one who first started using (at the time) Lotus 1-2-3 in the 80s.....I suspect we're near contemporaries.
In any event, I've created cash flow models with the goal of doing what you're trying to do. Looking at yours, my reaction is that you're getting a little too granular--far more detailed in that tax sheet than is necessary (or even helpful) for projecting future income and expense cash flows. [I say that as a person who back in the 80s, before Turbo Tax and all other such tools, wrote a complete spreadsheet--just for the fun of it--to calculate my tax returns. I'd transfer the resulting numbers to the paper forms!!]
But for modeling cash flows, my own suggestion would be to take a far more macro view of things. Just use higher level "tax bracket" kind of figures, forgetting about the specifics of short-term or long-term cap gains, etc.
At the risk of being presumptuous, I'm attaching a sample of the spreadsheet I created. It was helpful at the time I created it, prior to actually entering into full retirement, to develop confidence that we could handle it all.
I now (at age 79) make use of a simpler workbook to project RMDs based on assumptions about IRA growth, etc.. Plus I have a very detailed income and expense tracker that incorporates all the transactional data from our checking accounts and four credit cards, then uses a Pivot Table to summarize actual monthly income and outgo by detailed budget categories. So it gets entirely granular. But for projections, you don't need that level of granularity.
Feel free to come back with further questions, or push for the answer to your original question if you choose to disagree with my more macro approach.
Thank you mathetes,
The purpose of the workbook is to study the tax consequences of various types of investment strategies over time. I'm retired in my late 50's so I still have several options available. Budget/expense is just a load where money comes out of the system. The real question pertains to how to spend down the accounts while aggressively investing and avoiding excessive taxes (nice theory I know). AAII (American Association of Individual Investors) has studied this question a few times using a macro approach and concluded it is "generally best" to spend down from your taxable brokerage accounts, then IRA's then Roth accounts "in most cases". In my case my brokerage accounts yield modest qualified dividends and long term capital gains which qualify for the 15% tax rate. Further, different types of investments have different tax consequences, investment strategies and risk. You have correctly identified my tax sheet as it details the handling of each income type and their limits so I can assess this trade space. In its current form it is easily revised and can be incremented by year to some degree.
What I would like to do is study various investment, income, risk and withdraw strategies and the tax consequences as the tax rules are applied. I have it stuck in my head that the first sheet would calculate the types of income from various accounts and investment types, then the tax sheet would calculate and return the applicable tax for each year (like a subroutine). Then the first sheet would substract living expense then forecast the next year etc. Investment types and withdraw strategies would change with time. In this way I hope to look at net income, risk and taxes as I change the investment types and timing.
I'm thinking I will need to write a macro function to handle it this way but I was hoping someone might see an easier, more obvious approach.
Interesting. It might be fun to sit down together some day. I'm a life member of AAII and used to actively use their Stock Investor Pro database; had developed an elaborate spreadsheet where I could compare any two companies in the same industry with one another and average for the category, on all kinds of metrics.... Then I moved from the Windows environment to Mac....
As a life member I still get the printed magazine from AAII, but frankly pay almost no heed to it.
I am active on the Motley Fool, where I've also been a member since the 1990s......pay a lot more attention there, although recently mostly to Options, which are fun. I do my options investing (for that matter all my other investing too) within the tax-sheltered environment of a traditional IRA, so there are no tax consequences until withdrawals, and then it is what it is. But I have created spreadsheets to assess various options strategies, to track the positions I have.... I'm having a ball doing it.
To your goal...have you looked at all into Excel's Scenario Manager tool? What you're trying to do might stretch the boundaries of that tool, but it might serve the purpose. I've not used it enough to give you any specific suggestions. Perhaps some others here in the techcommunity can.
I think I became an AAII life member in the early 80's. I focus on dividend stocks in a brokerage account. Seems to be working very well, I am thinking I might significantly shift my investment approach this way but I really need to understand the tax consequences. The limits to the 15% qualified dividends and capital gains becomes the limiting factor. I've dabbled in options a bit but I wanted to study the dividend stock first.
Thanks for the tips. I will make a study of Scenario Manager tool along with VBA. The analyst on AAII must use one of these. I have tried to study a few of their sheets to see what they are doing in the past but did not understand what I was seeing.
Thank you for your input,
