Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Postby BigFoot48 » Mon Apr 21, 2014 11:29 am

buzzbud wrote:Thank you Bigfoot48 for this truly wonderful spreadsheet. To me, it was like finding the Holy Grail. My wife and I are now able to make rational decisions about our financial future. You saved us from having to pay an extra $50,000 for our 2014 taxes. I've been walking on a cloud since I found your spreadsheet yesterday and entered our data. It is too cool.

I do have a question. On the Base Case Page, row 80 shows Exemptions of 3,950 and row 81 shows Standard Deductions of 6,200. We are married filing jointly so shouldn't that be 7,800 and 12,200? I am using the latest Model v14.3.


buzzbad I'm happy that you have found this model to be useful! If you are making decisions of that magnitude you should supplement the model results with other sources of Roth conversion information, tax advice and your own calculations.

Make sure you have an "m" in cell B56 on the Setup page to use the Married - Filing Jointly rates. The Sample data that comes with the model uses that and shows the correct Exemption and Deduction amounts. Let me know if that fixes your results.

I'm currently working on an update that won't significantly change the calculations (adding rounding which changes results by a few dollars) but will consolidate and restructure the input items to make them easier to enter and reference, and improve the Results page to emphasis key results for the basic retiree portfolio forecasting function, the Roth conversion impacts, the alternative SS benefits, and other selectable options. Look for that update within a few weeks.

I'm also going to rename the model to reflect the additional functions added since it was released two years ago. This is my current idea but I'm open to suggestions: Retiree Financial Planning Model
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

buzzbud
Posts: 2
Joined: Mon Apr 21, 2014 10:06 am

Re: Retiree Roth Conversion Decision Model

Postby buzzbud » Mon Apr 21, 2014 3:29 pm

BigFoot48 I found the answer in the Tax Tables Page. Thanks again for this great spreadsheet.

Sidney
Posts: 6606
Joined: Thu Mar 08, 2007 6:06 pm

Re: Retiree Roth Conversion Decision Model

Postby Sidney » Mon Apr 21, 2014 3:38 pm

buzzbud wrote:BigFoot48 I found the answer in the Tax Tables Page. Thanks again for this great spreadsheet.

It is important to keep in mind that the tax brackets are not always the marginal tax rate. Many retirees have significant dividends and at some point, you can find yourself in the 25% tax bracket but see your marginal tax rate jump to 30% -- which isn't even a bracket!
I always wanted to be a procrastinator.

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu May 01, 2014 12:40 pm

A new version, 14.5, is now available. This is a major update and I recommend all users adopt it. Download via: [Replaced by newer version. See post #1 for link] Calculations have not changed and the results are about the same as the previous version 14.3, with rounding accounting for any minor differences.

Note: Dropbox link updated May 5 after Dropbox security action.

Fixed a problem at 2PM. Download again if you grabbed a copy earlier today. Sorry about that.

An Interim update (no calculation changes) to the above version was made on 5/2/2014.

The major changes are a renaming of the model to Retiree Portfolio Model to better reflect its scope (and allow it to be referred to as RPM!), the consolidation of user input and selection items to the Setup page (eliminating the Roth Conversion page), putting option selections, like including the Roth conversions, at together at the top of the page, adding a Comparison page to show all differences between the Base and Compare (use to be Roth) spreadsheets, a much improved results page with a focus on analysis of data, and a rounding function allowing rounding of results to $1, $100, $1000, etc. (Note in Excel this rounding function may require adding a ToolPak and may create errors - let me know immediately if you have an error. Works fine in LibreOffice.)

All major changes:
Re-defined the model as the Retiree Portfolio Model to highlight the features added sine the original Roth conversion analysis, such as Social Security start date comparisons, and tax rate changes.
Completely re-structured the Setup page to place similar factors next to each other, provide switches for comparison items at the top, and moved Roth conversion amounts from its separate page to be part of the Setup page.
Restructured and improved the Results page to better present the data, adding analysis sections and additional graphs.
Changed Roth Case label to Compare Case to better represent the many uses for this modeling technique.
Added a Comparison page that shows the differences for all case items. All normally hidden rows are shown on this page.
Added option to round calculations to the nearest $1, $100, $1,000, etc.. Example data default changed to nearest $100. Slightly reduced final results.
Lump Sum Events details added to Base and Compare yearly spreadsheets.
Corrected marginal tax rate tracking when rate changes from married to single.
Changed "include taxes" option to be "exclude taxes" to be selection y/n consistent with other option choices.
Fixed and clarified signs and titles in Results page to be consistent and informative.
Will be using "Interim Changes" on Readme page to track minor updates made that are not significant enough to warrant a numbered update release.

Because of the scope of this update it is likely some errors may have been introduced. Let me know immediately if you have any problems.

As usual, all suggestions, error finds, or criticisms are welcome. (Note: 19,940 thread views at this point!)
Last edited by BigFoot48 on Sat May 10, 2014 3:47 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
MN-Investor
Posts: 202
Joined: Mon May 02, 2011 9:04 am
Location: Twin Cities

Re: Retiree Portfolio Model

Postby MN-Investor » Wed May 07, 2014 9:58 pm

I am getting a disabled link message when I try to access the spreadsheet.
The key to success - Save early, save often, invest well.

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

Re: Retiree Portfolio Model

Postby LadyGeek » Wed May 07, 2014 10:09 pm

MN-Investor wrote:I am getting a disabled link message when I try to access the spreadsheet.

This may be dropbox's fault. longinvest had a similar problem with his spreadsheet: Re: Variable Percentage Withdrawal (VPW)

BigFoot48 - See: Web vulnerability affecting shared links Consider changing over to Google Drive.
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
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed May 07, 2014 10:45 pm

A new working link has been inserted in the OP and recent update post. I don't believe this public link is the type of security risk that Dropbox feared when they canceled all links. It would have been nice if they would have sent me an email, but no, maybe it will come tomorrow.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat May 10, 2014 3:41 pm

A new version, 14.6, is now available. This is another major update and I recommend all users adopt it. Download via: [Replaced by version 14.65.] Other than additional options, calculations have not changed and using the same input factors the results should be the same as the previous version 14.5.

The most significant improvement is an optional automatic IRA withdrawal feature that will keep the Taxable account at or above $0 throughout the modeling period. This can be turned on or off, and works with manual withdrawals. Benefits of this include allowing one to quickly determine the maximum amount of living expenses that will result in a low ending portfolio, or at what earnings rate the portfolio will be exhausted. [Update - 5/14 - I'm working on a mod that will allow the user to select the minimum amount to be kept in the Taxable account, rather than letting it go to $0. Look for it in the next release.]

Other new features are allowing withdrawals from the Roth account, and providing the option to use calculated Roth conversion and withdrawal amounts, rather than manually input ones. This doesn't give the same precision in using up low tax brackets, but is a way to quickly test alternatives.

I've also changed the Example data to better illustrate the SS benefit comparison, to match the IRA and Roth earnings percent to avoid skewing results, and to generally be simpler.

Because of the complexity of the automated withdrawal calculations, problems may arise that haven't been revealed in my testing. Please PM me with any issues.
Last edited by BigFoot48 on Thu May 22, 2014 2:25 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu May 22, 2014 2:24 pm

A new version, 14.65, is now available: Replaced by newer version. See post #1. This is an optional update as basic features and calculations have not changed, but it does contain a major revision to the look and feel with better formatting of the Setup page, along with a number of added features including allowing a minimum balance to be maintained in the taxable account and some mini-calculators including a very basic 2014 Federal tax calculator.

Changes:
Reformatted pages for easier viewing and better layout.
Added a minimum Taxable account limit when using the Automated Withdrawals feature. Set to $5,000 in example data.
Added an Account Average Return Calculator for estimating the overall return from an account containing cash, stocks, bonds and other investments.
Added a simple current year Federal income tax calculator, including taxable SS benefits, to the Setup page.
Added life expectancy calculator to Setup page for general information.
Added Excel data validation to selected input items. Some now have drop-down lists. Removed many related self-made error/info messages.
All pages/sheets are now protected to prevent inadvertent changes. Select Tools-Protection-Unprotect Sheet to unprotect. There is no password.
Improved monitoring and automatic fixing of conditions that lead to negative balances due to IRA withdrawals exceeding the IRA balance.

A number of formula changes were made to help prevent negative balance, so please let me know if you run into any issues. (21,541 views)
Last edited by BigFoot48 on Wed Jul 23, 2014 3:39 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

suee
Posts: 1
Joined: Mon May 26, 2014 10:34 am

Re: Retiree Portfolio Model

Postby suee » Mon May 26, 2014 10:44 am

BigFoot et al: has anyone tried using this model to analyze getting an Immediate Annuity (IA) for a retiree vs. just spending down your portfolio? it seems to me the model wouldnt require any changes, just inputing two sets of data, with and without an IA, then compare them?

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Mon May 26, 2014 12:12 pm

suee wrote:BigFoot et al: has anyone tried using this model to analyze getting an Immediate Annuity (IA) for a retiree vs. just spending down your portfolio? it seems to me the model wouldnt require any changes, just inputing two sets of data, with and without an IA, then compare them?

I haven't thought of that, but it might be an interesting and useful "special case" to compare against. But many of these alternatives, like Roth conversions and delaying SS benefits, have value way beyond the calculated possible financial impact.

Giving a Roth to heirs, for example, or insuring a surviving spouse has the largest inflation-adjusted SS benefit as possible, may be extremely valuable decades in the future, with the calculated impact on the portfolio really a secondary issue. With an annuity it would seem that having that assured (assuming the provider doesn't fail!) benefit has an intrinsic value that also may exceed the results of a planning calculation.

Anyway, I will consider this for a future release if there is interest. With the current model, I suppose one could use the inherited IRA amount, with it's withdrawal schedule (RMD age set to 120), along with the lump-sum expenditure amount, to simulate an annuity, but those amounts are included in both cases.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

trueblueky
Posts: 1087
Joined: Tue May 27, 2014 3:50 pm

Re: Retiree Portfolio Model

Postby trueblueky » Sat Jun 28, 2014 11:51 am

Bigfoot, is there an easy way to input IRAs for a couple with different ages?
We're six years apart, so RMDs are different.

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Jun 28, 2014 1:21 pm

trueblueky wrote:Bigfoot, is there an easy way to input IRAs for a couple with different ages?
We're six years apart, so RMDs are different.

When I created this for myself two years ago I only programmed in one IRA, and one inherited IRA, because that's all I had or expected! So as of now, I suggest just combining the two in some fashion to approximate the RMD impact in the year the second one would have started.

I have added this to the list of improvements which I hope to implement in July, so look for an update coming soon that will include the option for additional IRAs along with other portfolio and life modeling flexibilities.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu Jul 03, 2014 2:17 pm

Attention all users. While working on the next version I came to realize the possibly significant impact the use of the "Automatic Balancing" feature was having on taxable income and taxes. These automatic calculations, which create withdrawals in traditional and Roth IRAs in order to keep a positive balance in the taxable account, necessarily had to be excluded from tax calculations to avoid a circular calculation situation. In reviewing the example case data I realized this may result in a significant understatement of taxes in the years when the withdrawals were being made in the tIRA accounts. In doing a Roth comparison it would tend to make the Roth case look more favorable than it really is.

I have turned off this feature in the current version and revised the example data. I recommend everyone turn this feature off. It will either be fixed in the next version, or sufficiently documented so that users will be aware of the impact.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Jul 04, 2014 10:57 am

One more correction to the existing model pending the release of a significant update was made today to 14.65. In reconciling the pending update to older versions I discovered that inherited IRA withdrawals and RMD were not being included in taxable income. That has been fixed in this version and if you have inherited IRAs you might want to update, or just wait for the new version coming out this month.

Teaser: File and Suspend, Single Premium Immediate Annuity, and fixed (more or less) automatic balancing.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

cdbma
Posts: 17
Joined: Fri Feb 28, 2014 3:40 pm

Re: Retiree Portfolio Model

Postby cdbma » Wed Jul 16, 2014 6:34 am

BF48,
As you know, I'm a big fan of this tool. it just keeps getting better and better. I am posting here, rather than PM, because this might be of interest to other users.

My portfolio is most likely very similar to others. I have some taxable accounts, and a tIRA, and a Roth. Now in retirement, I live off of interest, dividends, and CG distributions, as well as withdrawals from my taxable accounts, as required. My taxable accounts (hopefully!) increase in value, while they pay out dividends and CGDs on a quarterly basis. I'm trying to figure out how to "see" these distributions, as they are a key part of how I pay my bills. I'm guessing that they're buried on the overall gross income of the taxable accounts, and any withdrawals just come out of that income - whatever it may be.

Thanks in advance for any tips on this.

cdbma

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Jul 16, 2014 9:16 am

Thanks for your question. Each account, taxable, IRAs, and Roths, have their annual earnings calculated based on the return rate selected for each, and are shown in the Account Earnings and Income section on the "case" pages. (Details of IRA #1 and #2, etc., are normally hidden but can be viewed by un-hiding the rows.)

A new summary of earnings data will be in the next version in a Results page section titled Portfolio Account Activity. I am also testing a new summary, similar to what i-ORP provides, that presents the data in a vertical form and allows viewing of most of the entire modeling period without scrolling (of course, depends on the size of your screen!).

I've also added a "Detail of how selected earnings are calculated' section at the bottom of each case page to reveal how the earnings amounts for the Taxable and IRA #1 accounts are calculated. (The method is basically taking the beginning of year balance and the end of year balance (before earnings), averaging them, and applying the earnings rate.)

Also, as you suggested in a PM, I'm fixing the AGI amount to include the entire taxable account earnings before deducting the "non-taxable earnings" factor, to better reflect how LTCGs etc. are shown in the 1040. The factor will still be deducted in determining taxable income. Update: I ended up not doing this as explained below.

Hopefully these features and coming changes will provide you the data you need. Thanks for the comment.
Last edited by BigFoot48 on Wed Jul 23, 2014 3:43 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Jul 23, 2014 3:33 pm

A new version of the Retiree Portfolio Model is now available. As this includes significant improvements and a few bug fixes, I recommend all users update to it.

A new version, 14.8, is now available. See post #1.

Interim misc changes made to current version pending release of a new version:
7/30: Itemized Deductions feature checked and updated with rounding and improved title.
7/28: Added IRA RMD/withdrawals to Summary page.

The most beneficial changes are the addition of the File and Suspend Social Security tactic, and a Single Premium Immediate Annuity investment option. Both of these turned out to be more difficult to model than I had anticipated, but I am 95% sure they are working correctly. Thankfully, the ORP retirement model added F&S this year so I was able to confirm my results to his. (He also has a very nice write-up on how the tactic works.) But who knew SPIA income is taxed three different ways depending on what account was used to buy it?

I've also renamed the Compare case to the Full case, as this is the one that contains all of the optional retiree choices, like Roth conversions and SPIA, which can be compared to the Base case that is bare-bones. The Full case is now the prominently featured one in the model.

Although I have verified the results against the prior case, with this many changes and new programming there is some likelihood that a problem will appear in certain data configurations. Please let me know of any issues.

Update on previous posts on excluding the amounts determined by the taxable account's non-taxable earnings factor: After reviewing the 1040 form I determined that the current method of excluding this amount from the AGI and the taxable SS benefit calculation was correct. This factor reduces taxable account earnings by an estimate of undistributed gains, LTCG, tax-free interest, qualified dividends, etc. that are included in the earnings calculation (average account balance x percent), but do not enter the AGI amount. So, while a crude approximation, I think it remains appropriate to reduce taxable earnings by it in calculating AGI in the model.

All of the changes:
Doubled the number of Lump Sum Events. Moved "Inherited By" input cell to Lump Sum section.
Added a second traditional IRA to Portfolio Balances. Owner is selectable for RMD calculations. Note: Not available for Roth conversions.
Added the SS benefits File & Suspend strategy to the SS benefits calculations. Improved SS benefit model to allow either person to receive spousal benefits.
Added a Single Premium Immediate Annuity feature that will calculate the impact of buying an annuity on income, taxes and RMD.
Added the option to transfer the ownership of existing tIRAs as a result of the death of the owner to the surviving spouse with new RMD calculations.
Added details of how earnings are calculated for selected accounts at the bottom of the case pages.
Added automated IRA withdrawals to SS benefits taxation calculation and Federal tax calculations. (Versions 14.6 & 14.65 may have understated tax expense!)
Added several Base case account and calculation amounts to Setup page to assist in setting and observing impact of input choices.
Added RMD calculator for most common ownership status.
Added a Summary page containing account balances and earnings and expenditures for both cases and a comparison.
Simplified SS benefit calculation. Just insert your FRA benefit amount and the age you want to start and everything is calculated.
Corrected taxable income and taxable SS benefits calculation to include inherited IRA withdrawals.
Fixed inherited IRA showing up at start of inherited year. End of year correct. Start of year should not include it.
Fixed instructions in state tax on SS Benefits cell note to indicate calculation method being used, which is Federal AGI multiplied by state rate, not Fed taxable income multiplied by state rate.
More formatting enhancements and cell note editing.
New example data to make use of some of the new features.
Changed name of Compare case to Full Case and focused on it as the default retirement model.
The model is no longer compatible with Google Sheets for unknown reasons, although it appears to be related to cell names not being read correctly.
26,138 views as of this date
Last edited by BigFoot48 on Fri Sep 26, 2014 12:29 pm, edited 9 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Jul 26, 2014 11:17 am

Version 14.7 has been modified and re-released - hopefully for the last time. All users should update to it.

What I discovered when running my own portfolio/etc. through it was that the revised SS calculation was not handling existing benefits correctly. So I ended up redoing the entire calculation and adding additional options to control results, such as a selection to indicate current benefits are being taken so benefit adjustments due to taking early or delaying are not included, and an option to shut off the automatic calculation and use of the spousal benefit. I tested 16 SS benefit alternatives against ORP's calculation and we're in agreement, so it seems to be working.

Hopefully this will be a stable version, but let me know of any problems as this has been a significant update and stuff happens.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Jul 30, 2014 6:49 pm

Rounding and better labeling of itemized deductions has been added to the existing version. If you would like a slightly better itemized deduction feature, download the current version 14.7 again, otherwise there's no need to get the revised version.

This post was revised from the original message after examining the itemized deduction feature in more detail. No significant update to the model was required.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Sep 26, 2014 12:24 pm

A new version is now available. This includes some nice additions and improvements but is not an essential upgrade. Most users may want to wait for the January update which will include the 2014 tax rates.

A new version, 14.9, is now available. See post #1.

Changes in this version:
Improved how account forecast earnings are calculated by adding an asset allocation method, and a means of changing allocation and the resulting earnings in a future year, such as by reducing stock allocation at age 80.
Added the option to not escalate Federal standard deduction and personal exemption amounts, allowing testing of future tax calculations with tools such as TaxCaster.
Modified the SPIA purchase and earnings default. It now is included in the Full case, and optionally in the Base case. Was vice-versa.
Changed the Alternative Tax Rate schedule to force all rates to use the same year, although this can be overridden (see cell comment).
Fixed tIRA 2 to use its own annual return rate, rather than the return rate of tIRA 1.
Inherited IRA 2 earnings changed from using existing tIRA 1's rate to tIRA 2's.
Improved and updated menus, instructions and cell help comments.

The addition of being able to use asset allocations to forecast future earnings, and adjust the allocation at a future year, is the most significant update. The previous earnings rate forecast method, average rate by account, is a rather crude, blunt force method. The optional asset allocation method is a refined, blunt force method. As the model forecasts over a 1 to 40 years period I'm not sure an even better method would make any difference in giving the user a forecast of future portfolio results.

Image

Now that the model has asset categories, I did a test of earnings calculation in RPM vs Simba's Backtest results for a 40 year period of 1972-2013. Beginning with a $10,000 investment the results were:

Simba results vs RPM:
Total Stock Market - CAGR 11.02%, $655,441 vs $650,013
Total International - CAGR 10.55%, $552,614 vs $562,622
Total Bond Market - CAGR 7.56%, $184,601 vs $187,287
3 Fund Portfolio 40/20/40 - CAGR 10.13%, $446,539 vs Avr Rate 9.5%, $377,187

The 3-Fund result shows the limitation in using asset class CAGR averages for a portfolio to arrive at the average rate to be used for the account's return. However, when I assigned each fund to a different account in their allocation amount, i.e. $4,000 to Taxable as TSM, $4,000 to tIRA as TBM, and $2,000 to tIRA2 as TI, the results were: 3 Fund Portfolio 40/20/40 - CAGR 10.13%, $446,539 vs $447,399.

So what does this mean? It means until the model breaks down each account into its asset components you can't use historic return rates and expect the model to duplicate them. Just set conservative, logical (to the classes included) account earnings rates and take advantage of being able to adjust them (your allocation) at a future age.

Enjoy. Report any issues, suggestions or comments. Otherwise I'll be back in January.
28,636 views
Last edited by BigFoot48 on Sat Oct 11, 2014 9:28 am, edited 3 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
zed
Posts: 332
Joined: Wed Jan 07, 2009 3:01 pm
Location: A river runs thru it

Re: Retiree Portfolio Model

Postby zed » Fri Sep 26, 2014 1:44 pm

BigFoot48,

Think multi-media. When are you going to post youtube videos on using the modeler :wink:

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu Oct 09, 2014 8:50 am

A new version is now available. This is being released to fix several problems in the calculation of Social Security benefits. While these problems will not impact couples with independent SS benefits or singles, it may affect those analyzing starting benefits at age 62 or using the automatic spousal benefit calculation feature. I recommend anyone using the model to analyze SS benefits and who want to do it before the 2015 version is issued in early January, download it. Others may want to wait.

Download the current version 14.9 - 10/9/2014: update pending

Updated 10/12: Fixed age checking rule in SS section that didn't allow values over 70. This was fixed and anyone already receiving benefits from 62 and up can include them. Note "Benefits have started" should be set to "y" if 70 or older. (Many thanks to BH earlyout for reporting this to me.)
Oct 15: Fixed false first year RMD for traditional IRA 2 that appeared if IRA2 amount was $0 and RMD amount if that was the RMD start year for IRA 2, i.e. age = 71.


Changes in this version:
Several corrections to the Social Security benefits calculations were made.
1) The end age for spouse benefits was using the spouse's age rather than the "you" age and resulted in one extra year of spouse benefits than intended. Using the provided example data, this correction reduced SS benefits by about $45,000 at age 85 and reduced both cases' final portfolio balances by about $58,000. Users may see similar changes depending upon the ages of "you" and "spouse".

2) The formula used to calculate spousal benefits taken before FRA was incorrect, overstating the benefit. In the example data, if the supplied spousal benefit amount is zero and the automatic calculation of benefits used, age 62 benefits were $12,200 (using the formula of "Your" FRA x 50% x 75% + 2.5% COLA) but the correct amount is $11,400 (using "Your" FRA x 35% + 2.5% COLA).

3) The alternative SS benefit option was not including automated spousal benefits in yearly forecasts, potentially underestimating income when this option was used.

Improved and updated menus, instructions and cell help comments.

This problems were discovered while working on the 2015 version which I hope will include the option for users to set age 67 as their default Full Retirement Age for Social Security calculations.

Image

Think multi-media. When are you going to post youtube videos on using the modeler
Good idea and I think it would be useful and if I ever have time from fixing or improving the model will try and work it in!
Last edited by BigFoot48 on Fri Oct 24, 2014 11:36 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Oct 15, 2014 7:06 pm

A person using the model with slightly different data than the test data has found a problem with an error checking field and a first-year RMD amount for the traditional IRA 2 entry when the IRA is $0. I also found an additional problem with this one calculation if the RMD occurs in the first year of the model.

I have fixed these problems and the model under version 14.9 can now be re-downloaded. Users with a starting age 70 or greater should get the revised version but other users may not be impacted by these problems.

I plan to spend some time reviewing all the calculation for proper operation in all expected age ranges but don't expect any other issues to be found. At least, hope springs eternal!

Please let me know of any issues, questions or problems found with the model. Thanks.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 129
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Fri Oct 24, 2014 12:33 am

I just found the RPM spreadsheet and it looks great. I downloaded it and started playing around with it.

Maybe this is just a dumb newbie question, but I don't quite understand what it is telling me or how it is working. First, a bit of background...
I was trying to a model where I put my current age as 59, and wanting to retire at 60 (i.e. next year). I'm starting with 40k in the taxable account, and a total of about 1,000,000 in the various IRA's.
I have expenses of 120k per year, and I entered pension income on Line 3 of 70k per year beginning at age 60.
I expected to need 50k per year starting next year (to make up the difference between 70 and 120). So I entered in 50k for IRA withdrawals, thinking that would cover the delta. However, when I do that, it says the taxable amounts are negative? It turned out I had to put in about 100k withdrawals to keep the balances positive. Can you explain either why that is or what I am doing wrong?

Thanks!

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Oct 24, 2014 9:05 am

There are no dumb questions - only dumb programming mistakes but let's not talk about those.

There are a few things you need to consider:
1. Income taxes are in addition to the expenses you enter, so those are adding to your total spending. And with $50,000 in IRA withdrawals and your pension taxes will be at $30,000 immediately (single rate).
2. Expenses escalate at the rate selected and with a starting amount of $120,000+taxes the inflation can be a big number, but even setting inflation at 0% only extends the viability of your assumptions by about 5 years.

Plugging the numbers you gave into the model I see you running out of money in about 15 years and thus the negative balances and warning messages. You're basically trying to fill a $50,000++ spending gap every year with a $1M IRA and that's clearly going to run out of funds in around 20 years, with the earnings somewhat offsetting the inflation increase in expenses.

Will you be getting SS benefits? That would help, but basically in this model you will have to adjust your living expenses to meet match the assets available to fund them - just like in real life!

Tip: Use the "Use automatic withdrawals" setting to let the model determine IRA withdrawal amounts that will keep interim taxable account balances positive. (May not work perfectly but helps.)

For another quick look at your numbers you might want to try ORP: http://www.i-orp.com/

Thanks for using the model and asking about the results, and I hope you find it useful.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 129
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Fri Oct 24, 2014 12:58 pm

Thanks for the clarifications.... The biggest issue is the taxes .... I included the estimated taxes in my expenses, so that is being double - booked.

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Oct 24, 2014 2:21 pm

You can change the setting in Options to exclude income taxes if you want but the model will do a good job of forecasting taxes over the years such as when RMDs start.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Oct 24, 2014 3:44 pm

A new version of the model is now available. A PM by a user asked me about a schedule which lead me to discover a programing error with the new asset class earnings rates. This error was impacted the change year feature by using the changed year rates in the beginning years and the beginning rates after the change year.

If you are using this changing-rate feature, you will want to update or wait for version 15.0 in January.

Download the current version 14.93 - 10/24/2014: replaced by newer version

Changes since version 14.9:
Fixed use of the new asset class earnings rates which had been reversed in the model if the change year was used.
Fixed data checks that prevented SS starting ages from being greater than 70.
Fixed false first year RMD for traditional IRA 2 that appeared if IRA2 amount was $0 and RMD amount if that was start year, i.e. age = 71.
Corrected end age for existing Roth and inherited IRA input withdrawals - it was missing.
Fixed calculation for year to start single tax rate based on the passing of a spouse. It was off by one year.

My goal is to review the calculations for all line items before the next version is released, but I do have confidence that features that have been in the model for more than 6 months are calculating and operating properly.
Last edited by BigFoot48 on Sun Sep 20, 2015 5:23 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 129
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Sat Oct 25, 2014 12:37 am

I have another question about the right way to model future events...

I have a scenario where I will retire next year, and then 3 years later I expect to have to bump up expenses to cover my kids' college expenses. I also would like to bump the income, since I will be withdrawing money from a 529 plan to cover about 75% of the expenses. This "bump up" would last for 4 years.

Is there a good way to model this kind of a scenario?

Thanks

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Oct 25, 2014 1:36 am

You could use the Roth IRA account to represent the 529 account since withdrawals are not taxed and set the withdrawals to cover the 4 year period. The temporary expense bump is more of a problem. The model can increase (or decrease) expenses at a specified year, but there's no way to reduce them back down using the input tools. However, you could "unprotect" the case pages and manually enter a lower number in the year expenses need to go down. This will replace the formula in that cell but future years will escalate correctly from the lower expense number.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 129
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Sat Oct 25, 2014 9:08 pm

I decided to try to model this in a slightly different way. I entered just the delta between the income and expense as one time events ... two of them as "negative income" and 2 of them as "expenses". That seems to be making reasonable changes in the results. Is there anything I might be missing by doing it that way?

Thanks!

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sun Oct 26, 2014 2:42 am

sandramjet wrote:I decided to try to model this in a slightly different way. I entered just the delta between the income and expense as one time events ... two of them as "negative income" and 2 of them as "expenses". That seems to be making reasonable changes in the results. Is there anything I might be missing by doing it that way?

Thanks!

I like that solution to your need. The expense categories, such as in the Lump Sum Event area, Roth withdrawals, and elsewhere can indeed use either positive (withdrawals) or negative (inflow) numbers and function just fine within the model. The place where this would probably not work is in categories that result in taxable events, like IRA withdrawals. Just examine the yearly expense totals to confirm its doing what you want.

I'm also adding to version 15 more model period totals to each account withdrawal entry on the Setup page (where appropriate) so you can instantly see the results of entries like you're using.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

pankuchb
Posts: 4
Joined: Wed Oct 30, 2013 8:55 am

Re: Retiree Portfolio Model

Postby pankuchb » Tue Nov 11, 2014 12:22 pm

Hi BigFoot48,
You have created a very useful spreadsheet. I would find it even more useful if I could see the rows and columns identifications on the spreadsheet. This would be especially useful for checking to see how you're making a calculation. Is there some easy way that I can make the columns and rows visible as it was in previous editions of your spreadsheet?

Thanks
Searcher

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Tue Nov 11, 2014 2:37 pm

I have been turning those off to have a cleaner appearance to certain pages, but you can turn those back on by selecting Tools > Options and checking Row and Column Headings and Gridlines. Each page has independent settings.

The 15.x model will have macros and I will see if I can automate that process.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 129
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Tue Nov 18, 2014 11:23 pm

I have another question about how to best use the spreadsheet.
Here's the scenario(s) I want to model ... I want to model the differences between starting retirement over the next 3 years (ie 2014,15,16). In the second or third scenario I will not be drawing any $$, but rather adding $$ to the various accounts. If I do change the first year to 2015, 2016, etc, I get a warning about the tax brackets. Should I be concerned about that?

What is the right way to model those scenarios?

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Nov 19, 2014 11:07 am

sandramjet wrote:I have another question about how to best use the spreadsheet.
What is the right way to model those scenarios?

Interesting use of it. The warning can be ignored. It merely reminds the user that no matter what start year you put in, the first year will use the current calendar year's Federal tax rates and factors. In your proposed use, this will mean your results for 2016 as the first year (for example) will be using 2014 rates (still likely applicable) and 2014 exemptions/deductions (understated as they go up every year), meaning the taxes will be slightly higher in future years than they should be.

I think this is a trivial impact and can be ignored for your use. However, be sure to adjust the single rate change year in section "7. Income Taxes", as changing the start year can create an error in that change year if it is not adjusted. (The change year is to model the impact of the death of a spouse on taxes if joint-return had been used.)

However, if someone really wanted the extra work, the tax factors for the 2015, 2016, etc. starting year models could be changed to what are being forecast for those years in the current 2014 model. Just change the factors on the Tax Tables page and the "trivial" impact will be eliminated. You will end up with a spreadsheet for each year and you can compare the results to see how deferring retirement affects results.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 129
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Wed Nov 26, 2014 2:02 am

I have another couple of questions...
The first is somewhat of a nit, but when I look at the graphics on the setup page, the "Taxable Account Balances" plot has the base as blue dots, the full as red dots; but all the other charts on that page seem to be just the opposite. Is this intentional? It seems it would be better if they were all consistent.

The second one may be a user error ... but when I tried to do a SS analysis, I ran a calculation in which I put an "end age" in for myself and left the spouse end age blank (she has no benefit other than spousal) I thought I would see that at my end age the SS benefit would drop back from mine+spouse to just "survivor" (i.e. my basic benefit). But it appears that it simply drops back to the spouse benefit. Is there something I am doing wrong to get it to use the survivor benefit? Or does the model not include this? Or am I misunderstanding how survivor benefits work?

Thanks!

btraven
Posts: 46
Joined: Tue Jul 02, 2013 3:27 pm

Re: Retiree Portfolio Model

Postby btraven » Wed Nov 26, 2014 2:58 am

I haven't looked at the model yet, but can tell from the posts that it does many useful things pertinent to my situation. Does it include the case of taking long term captial gains at the 0% Federal rate as an option?
Thank you.

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Nov 26, 2014 9:58 am

the "Taxable Account Balances" plot has the base as blue dots, the full as red dots; but all the other charts on that page seem to be just the opposite.

Is there something I am doing wrong to get it to use the survivor benefit?

Does it include the case of taking long term captial gains at the 0% Federal rate as an option?

I welcome all nits! Yes, those graph colors should be consistent and I will fix that for the next release which will likely be Jan 1.

The entry to correctly trigger the SS survivor benefit is a bit counter-intuitive. To end a benefit you must enter the age the other person will be when you want that benefit to end. So for the typical situation, if the "you" person is assumed to die at age 90, then 90 would be entered as the end age for "spouse" benefits. The cell comments describe this method.

I have considered changing this input method but still think it makes entering the right age more likely as a person can just think "I'm going to die at 90 and that's when spouse benefits will end so I'll enter 90 there". Do note that the SS benefits are independent of each other and, as you found out, entering an end age in the "you" benefits will result in spouse benefits ending when the spouse is that age. (SS benefits section has been extensively re-programmed for the next version to insure they are independent and any combination of data will work correctly.)

Capital gains taxation at different rates has not been modeled. To somewhat compensate for the fact that the entire yearly earnings in the taxable account will not necessarily be subject to the marginal rate, a "taxable account non-taxable earnings" adjustment is available. Entering a percentage there will reduce taxable account earnings and crudely approximate what the average taxable portion might be. This reduction applies to every year of the modeling period.

As I'm trying to make the model understandable and useable by the average person, I'm not sure providing a breakdown of taxable earnings by capital gains and ordinary income tax rates would be used by many, or provide useful modeling forecasts considering the number of variables involved. However, I'm always open to a persuasive argument for improving the tax calculation!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

keith.e.simpson
Posts: 2
Joined: Mon Oct 06, 2014 9:15 pm

Re: Retiree Portfolio Model

Postby keith.e.simpson » Sat Nov 29, 2014 12:37 pm

Hello BigFoot48,

Thank you for developing the Retiree Portfolio Model. It's very helpful to understand/analyze assets and cash flow through our retirement years!

I am using the tool as a pre-retiree, mid-50's and married. The addition of a couple more rows for "pensions and annuities; other income" would be helpful in section 3, "Retirement Income." I would use these to add spousal income streams, including pre-retirement income streams separately for myself and my spouse.

A more complex feature would be to allow additional contributions to traditional IRA's and ROTH's from these input streams (and thus making the tool more applicable to pre-retirees). Implementing this feature may not be merited, though.

I have been studying and using the Retiree Portfolio Model for a few months now, and I would be happy to assist in making the tool easier to use/understand for a general user.

Again, thank you!


Keith Simpson

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Nov 29, 2014 3:07 pm

Keith, thanks for your offer, as viewing the model from a new user's point of view becomes more difficult as time goes on. Feel free to PM me with any suggestions!

I intended for the model to be for retirees (as it started out as my model) and want to keep it as simple as possible (may have crossed the Rubicon on that already) but can see how additional other income streams might be useful for both pre-retirees and retirees. I will consider that for the next major release.

I don't see contributions to IRAs getting modeled though. However, in a brief test, I think if an negative amount is entered as an IRA withdrawal amount it will act as an addition. [OOPS - that doesn't work as withdrawals are taxable, and negative withdrawals are not tax reductions) You might want to test that. If so, I will document that as a feature in the next version. (I have already changed for the next release the "withdrawal escalation" factor to just be "change", as it can be either an increase or a decrease.)

Thanks again and glad you are finding the model useful.

Update: I liked the idea of adding additional income items so made the change in the next release. (More importantly - it was relatively easy to do!)

Image
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

keith.e.simpson
Posts: 2
Joined: Mon Oct 06, 2014 9:15 pm

Re: Retiree Portfolio Model

Postby keith.e.simpson » Sat Nov 29, 2014 8:08 pm

BigFoot48,

thanks for your reply, and greatly appreciate a couple additional "income streams" for Section 3! I figured that would be simple. Also assumed that the feature to flow funds into IRA/ROTH as a pre-retiree would be more complex and maybe not warranted in this tool.

I'll PM you separately with thoughts/ideas about describing the tool for average users.

Cheers,

Keith

theta
Posts: 62
Joined: Fri Feb 21, 2014 3:50 pm

Re: Retiree Portfolio Model

Postby theta » Sat Dec 06, 2014 10:41 am

Bigfoot48,

First, thank you for developing this wonderful tool. I do not think it is an exaggeration to say it is the only tool of its kind, in attempting to answer so many retirement planning questions. There are so many moving parts, as you well understand.

Now, on to my problem/question. I am trying to get my SS benefits input correctly into the spreadsheet. My situation is that I will file-and-suspend on 7/1/2018, but restrict my application to received spousal benefits. My spouse will file on 7/1/2018 (the month after she turns 62) and receive 73.33% of her PIA. In turn, then because I have filed for spousal benefits and because I have reached full retirement age, I will receive 50% of her PIA.

In the spreadsheet, however, when I turn on the file-and-suspend flag, it forces me to make both of our retirement ages 70.
Which zeroes out any SS income until 2021. (After 2022, the SS numbers in your spreadsheet match the numbers in my Bedrock Capital SS simulator, so I believe they are accurate from that point forward.)

What is the best way for me to get the right numbers into the spreadsheet for 2018, 2019, 2020 and 2021? (Because there are four years worth of income, it adds up to a fairly healthy sum, which could affect some of the other results.) I am happy to manually enter them, but the cells appear to be locked. I'd figure out how to unlock them, but I hesitate to take that action for fear of totally screwing up something else in the spreadsheet...

Any suggestions? And thanks again for all of your years of work on this tool.

...theta

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Dec 06, 2014 1:26 pm

theta, I'm glad you are finding the model useful.

You are describing the "restricted application" (RA) strategy. BH Mike Piper discusses it and the other strategies for married couples in this article. (In fact his RA example reads like yours!). http://www.obliviousinvestor.com/social ... d-couples/ As a result of this article I have been working to get the RA strategy working in the model and I'm hoping it will be in the 15.0 release next month.

In the meantime, I suggest you turn off page protection in on case page you are using, and enter those four years' amounts, overriding the formulas on the total SS benefit line. Normally this will not cause a problem with future years calculations, but just carefully review the numbers, and remember you did this.

...deleted confusion....

Thanks for you question and "I'm working on it!".
Last edited by BigFoot48 on Sat Dec 06, 2014 11:19 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

theta
Posts: 62
Joined: Fri Feb 21, 2014 3:50 pm

Re: Retiree Portfolio Model

Postby theta » Sat Dec 06, 2014 2:05 pm

Thanks Bigfoot48, that (turning off page protection) did the trick! I await version/release 15 to give that a try.

I'm pretty sure (though I also am no SS expert) that the 50% number is correct. Here's the wording from the SS website on spousal benefits:
"If you begin receiving benefits at your full retirement age, your benefit as a spouse can be equal to one-half of your spouse’s full retirement amount."

Maybe there's a true SS expert around here somewhere you could run it by? :-)

thanks again,
...theta

theta
Posts: 62
Joined: Fri Feb 21, 2014 3:50 pm

Re: Retiree Portfolio Model

Postby theta » Sat Dec 06, 2014 3:55 pm

Bigfoot48,

Here's a little more on the Restricted Application 50% number:

http://www.bogleheads.org/forum/viewtopic.php?f=2&t=123764

...theta (not sure I inserted that url correctly, but you get the idea)

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Dec 06, 2014 4:12 pm

theta wrote:Bigfoot48,

Here's a little more on the Restricted Application 50% number:

http://www.bogleheads.org/forum/viewtopic.php?f=2&t=123764

...theta (not sure I inserted that url correctly, but you get the idea)

Well, the very learned people that responded to that thread did not disagree with the OP that if a person files for SS at 62, and the spouse is 66, the spouse can get 50% of the person's FRA as spousal benefit, even though the person filing will not be receiving at the FRA rate.

delete

UPDATE: Well, I just looked at the model again and it is calculating this correctly, with the spousal benefits at 50% if started at 66. So, please disregard my previous comments.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 129
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Tue Dec 09, 2014 11:18 pm

I have another question about the appropriate way to model a situation...
My wife and I each have a traditional IRA, as well as Roth IRA's. But my wife also has a variable annuity that I'd like to be able to figure out how to include in the planning. What would be an appropriate way to model this, and possibly compare alternative options in terms of what I do with that annuity?

Since we're getting near Christmas, I'd also like to add an item to my "wish list" :happy . I really like the inputs for the Roth conversion section where I can do either the quick and easy calculated or the year by year conversion AND withdrawals entry. I think it would be really nice to have a similar option for the other sections, such as withdrawals from the tIRA's or annual expenses so that I could try out different withdrawal scenarios that might not be represented by a simple amount + escalation sort of thing.

Thanks again for this cool tool.

User avatar
BigFoot48
Posts: 2466
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Dec 10, 2014 1:23 pm

sandramjet wrote:But my wife also has a variable annuity that I'd like to be able to figure out how to include in the planning. What would be an appropriate way to model this, and possibly compare alternative options in terms of what I do with that annuity?

I really like the inputs for the Roth conversion section where I can do either the quick and easy calculated or the year by year conversion AND withdrawals entry. I think it would be really nice to have a similar option for the other sections, such as withdrawals from the tIRA's or annual expenses so that I could try out different withdrawal scenarios that might not be represented by a simple amount + escalation sort of thing.

I would use one of the income input entries to record the VA income and one of the Lump Sum items to record when and if it is cashed in. The Lump Sum items can be turned on and off so you could observe the impact on the portfolio that way. (These will be called Special Events in the next version.)

I will take your idea under study but tend to think the user inputting and monitoring values tends to provide more insight into one's portfolio, rather than automatically calculating results with some obscurity over how the amounts were arrived at. I also want to keep the model as simple as possible (too late?) to allow novice users to more easily understand the calculations and results.

Thanks for the input and glad you are finding it useful.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser


Return to “Personal Finance (Not Investing)”

Who is online

Users browsing this forum: advice789, Apostrophe, BeneIRA, crestone, doug1022, Gatorbh, magazinewriter, neilpilot, Nova1967, Pranav, RudyS, Silk McCue, slazers, zakath47 and 73 guests