Home

RSP User`s Manual - Wagner Math Finance

1. The input management fee schedule is applied to the total current portfolio value to determine the portfolio management fees due from your client for the current year This amount will be combined with your client s annual expenses and the current tax bill to determine the spending needs for the year Rollover Profit Sharing and 401 K Portfolios Beginning at retirement age the current value of each Profit Sharing and 401 K account is transferred to the corresponding account in the Deductible IRA portfolio This is a tax free rollover Convert IRA to Roth IRA Amounts in the Deductible IRA and Non Deductible IRA portfolios are converted to the Roth IRA portfolio according to the schedule of percentages input on the Detailed Inputs sheet This percentage is applied to each Deductible IRA and Non Deductible IRA account and the resulting dollar amounts are withdrawn from those accounts and deposited in the corresponding accounts in the Roth IRA portfolio Withdrawals from the Deductible IRA accounts will be taxed as ordinary income The capital gain on withdrawals from the Non Deductible IRA accounts also will be taxed as ordinary income This capital gain or loss is calculated by subtracting the pro rated cost basis from the withdrawal amount The total cost basis then is updated by subtracting the pro rated cost basis No penalty will be applied to these withdrawals Withdraw Required Distributions Required minimum distributions R
2. Profit Sharing is an employer contribution so it will be made in full no matter what your client s income may be 401 K Deductible IRA Non Deductible IRA and Roth IRA are individual contributions so they only are made if the income inputs for that year will support them The income that supports the 401 K Deductible IRA Non Deductible IRA and Roth IRA contributions is the sum of the following items on the Detailed Inputs sheet Earnings and Other Taxable Income If the sum of these contributions exceeds the sum of these income inputs then the contributions will be reduced These reductions are made in the following order Roth IRA Non Deductible IRA Deductible IRA and 401 K This revised plan along with the unmodified Profit Sharing savings is deposited to the appropriate tax deferred portfolios Deposits to the Deductible IRA and 401 K portfolios will be deducted from regular income for the purpose of calculating the federal tax bill Amounts deposited to each Non Deductible IRA account will be added to that account s cost basis Compute and Pay Taxes The next step in the cash flow analysis is to compute this year s taxable income and the resulting taxes incurred The key issue to note here is that we have not yet made any discretionary withdrawals nor have we determined the income from dividends or capital gains distributions Therefore any taxes incurred due to taxable income from these sources will be paid in the fol
3. 3M 4M 5M 6M Final Portfolio Size Projected Retirement Spending Year 2010 Dollars 125 000 100 000 2 c 75 000 E amp 50 000 w E d Spending Percentile 25 000 Na 0 2010 2020 2030 2040 2050 2060 2070 Year Name Demo Date 4 23 2010 Case Base Projected Wealth Year 2010 Dollars 2 500 000 2 000 000 1 500 000 1 000 000 Portfolio Size 5 th Percentile 10 th Percentile 500 000 15 th Percentile 25 th Percentile 50 th Percentile 0 2010 2020 2030 2040 2050 2060 2070 Year 27 The output graphs are identical in format to those in earlier versions of the program We show the probability distribution as either a density or a cumulative with key percentiles the ones you chose picked off key statistics on a timeline as to spending achieved and the time history of key percentiles of total wealth We show the same results side by side in both nominal and inflation adjusted dollars Clicking one of the Summary Table buttons nominal or inflation adjusted will produce a tabular report Detailed Tabular sheet with detailed year end statistics arithmetic means and standard deviations on income spending taxes and the investment portfolio Separate year end statistics are provided for the taxed and tax deferred portfolios including portfolio amounts transactions deposits and withdrawals allocations of
4. 85 0 06 0 39 0 66 0 85 0 96 0 99 1 00 gt 500 000 100 0 00 0 11 0 33 0 64 0 86 0 97 1 00 Probability 75 0 22 0 65 0 83 0 94 0 98 1 00 1 00 Total Wealth 85 0 03 0 28 0 54 0 78 0 93 0 98 1 00 gt 750 000 100 0 00 0 08 0 27 0 58 0 83 0 96 1 00 Example If you experience 1 25 Ya higher returns each year then there is 100 chance you will have more than 250 000 at age 75 The Complete Analysis button launches a simulation that summarizes the Standard Analysis Portfolio Sensitivity and Spending Sensitivity in a single concise report If the new Return Sensitivity studies also are well received by our users we will add these analyses to this report in a future release of the software 30 XYZ Incorporated your firm name goes here Report far April 23 2070 of Portfolio Value at Target Year 2070 Client Demo Your Wealth Distribution has these characteristics Case Fase 4895 chance of completely exhausting your portfolio 5095 chance of having more than 127 951 50 chance of having less than 127 951 Portfolio Sensitivity at 2070 Chance of Having Selected Target Amounts by Notional Portfolios VOW NOW A E CQ OS ED Uu 2 50 000 0 48 0 64 0 60 0 48 0 32 0 13 0 02 500 000 0 45 0 62 0 57 0 45 0 29 0 10 0 01 730 000 0 42 0 59 0 55 0 42 0 25 0 08 0 01 Spending Sensitivity at 2070 Chance af Having Selected Target Amounts by Adjusied Spending Schedule 1596 10 596 Original 596 10 1596 Decrease D
5. Intemational Shot Term Intermediate Expected Volatilit One Year Large Value Large Growth Small Value Small Growth Large Small RealEstate Fixed income Fixed Income Municipals Money Market Retum Y Vi A 0 15 15 1 1 0 1 10 096 8 8096 12 92 15 2 15 09 15 0 5 0 5 0 15 0 15 0 0 0 0 0 0 0 0 0 B 14 0 14 0 14 0 14 0 14 0 14 0 0 0 2 0 2 0 2 0 10 0 8 44 12 10 14 2 12 0 12 0 12 0 12 0 120 120 0 0 6 0 6 0 6 0 10 0 772 10 46 12 0 D 0 0 0 096 0 0 10 0 L3 8 8596 896 10 0 10 0 10 096 10 096 10 096 10 096 0 096 10 096 10 096 10 0 7 00 96 9 8 E 8 0 8 0 8 0 8 0 8 0 8 0 0 0 14 0 14 0 14 0 10 0 6 28 7 26 7 6 F 6 0 6 0 6 0 6 0 6 0 6 0 0 0 18 0 18 0 18 0 10 0 5 56 5 75 5 5 G 4 0 4 0 4 0 4 096 4 096 4 096 0 0 22 0 22 0 22 0 10 0 4 8496 4 36 3 6 H 2 0 2 0 2 0 2 0 2 0 2 0 0 0 26 0 26 0 26 0 10 0 4 12 3 27 23 I 1 0 1 0 1 0 1 0 1 0 1 0 0 0 28 0 28 0 28 0 10 0 3 76 2 95 2 0 J 0 0 0 0 0 0 0 0 0 0 0 0 0 0 30 0 30 0 30 0 10 0 3 40 2 85 2 2 10 Analysis Controls This sheet launches the analysis either the basic analysis the sensitivity tests or the complete analysis Standard Portfolio Spending Return Complete lt lt HOME Analysis os ga Analysis Simulation Controls Number of Replications 3000 Standard Output Display Controls Densit
6. Savings accounts have the specified Taxed Allocation It then attempts to rebalance the entire portfolio to achieve the specified Global Allocation by transferring funds among accounts in each of the following portfolios in turn Deductible IRA Roth IRA 401 K and Profit Sharing If the desired Global Balance remains unachieved the program makes a pass through the Personal Savings accounts in a final attempt to achieve the Global Balance While the Non Deductible IRA accounts are tallied when determining the Global Balance the program never transfers funds within that portfolio as the tax consequences of making these transfers could be considerable Thus the algorithm achieves the stated Global plan and attempts to achieve and at least approximates the Taxed plan If you do not care what your Taxed Allocation is you can eliminate the secondary goal by setting the Taxed entries to NONE The Deferred Taxed scheme is comprised of two independently planned allocations for each year a Tax Deferred Allocation and a Taxed Allocation The program first attempts to rebalance the Tax Deferred portfolio to achieve the specified Tax Deferred Allocation by transferring funds among accounts in each of the following portfolios in turn Deductible IRA Roth IRA 401 K and Profit Sharing While the Non Deductible IRA accounts are tallied when determining the Tax Deferred Balance the program never transfers funds within that portfolio as the tax consequences
7. The Account Management button is used to create other market profiles or to call them up to replace the current performance data on the sheet This button also is used to edit correlation data and the default rebalancing scheme or to consolidate performance data for up to five investments Each market profile is labeled with a Source and Description The Source is the name of the Excel workbook that stores the performance data while the Description provides some brief details regarding the makeup of that profile The market profiles are stored in separate files in the Accounts sub folder If a colleague sends you one of these files just put it in your Accounts sub folder and you may begin using with it in your copy of RSP If you revise that profile Save it and give the file back to your colleague Warning Be very careful when sharing market profiles The onus is upon you and your colleagues to coordinate the saving of changes to shared files Note that a market profile can be defined to suit advisors who engage in active portfolio management The eleven slots can be used alternatively to represent market sectors such as Technology Energy Transportation etc The user will have to supply appropriate return volatility correlation numbers 7 Current Portfolio Not to say it should not be done in practice Advisors should do whatever it takes to stretch their clients dollars Another example is loss harvesting which can save a lot of
8. a sample of size 29 the 5 significance level is 1645 The KS statistics for these four data sets are respectively 0953 0776 1252 and 153 all passing at that 596 level For visual evidence we show the fit between the empirical cumulative and the normal with the same mean and variance for each of the four data sets in the four inserted figures Cumulative Probability Cumulative Probability Fit of Ibbotson Large Cap Data 1972 2000 to the Normal 1 e H 5 E R e 30 25 20 15 10 5 0 5 10 15 20 25 30 35 40 Return 96 Fit of Ibbotson Small Cap Data 1972 2000 to the Normal 1 pm e E B E 0 4 40 30 20 10 0 10 20 30 40 50 60 Return 35 Cunmulative Distribution Cumulative Probability Fit of Ibbotson Longterm Corp Data 1972 2000 to the Normal 10 20 30 Return 96 Fit of Ibbotson Tbill Data 1972 2000 to the Normal 6 8 10 Return 40 50 36 Appendix B Use of the lognormal distribution We use the lognormal distribution to represent the multiplicative effect of returns exp X where X is normal with mean m and standard deviation s We might as well have used 1 X as the multiplier since exp x is approximately equal to 1 x but exp x is guaranteed to be positive and 1 x is not and we need to protect against that possibility for users who like to stress the program by entering enormous volatility values As a technical matter we actually ma
9. and set the rates to zero and then remember to change them back after completing the test The Penalty on Early Distributions button allows you to disable the automatic IRS penalty on tax deferred distributions occurring before age 59 12 This should be used when your client is making substantially equal periodic distributions from their tax deferred portfolio under IRS Section 72 t You will notice that we do not treat age at death as part of the Monte Carlo Many programs do this Just as portfolio returns are subject to random variations we could incorporate a model for death using actuarial tables The reason we do not is that we believe it to be bad financial planning If you include consideration that a 55 year old client may die next year then spending all his money in year 1 would be acceptable Averaged over all the scenarios in the Monte Carlo this will then result in higher spending than is desirable if he happens to live to be 85 We feel it is prudent to have a plan that is consistent with the scenarios where the clients live longer than expected 2 Client Cash Flows Here we enter essential information about income expectations savings and spending plans Fewer of these entries are needed if Accumulation Mode was selected on the previous sheet All these are then ported to schedules on later sheets where the entries may be edited and fine tuned To view the entire list of hidden sheets click on Format gt Sheet
10. discipline of doing the Save Case As at the top so that they don t forget The program comes loaded with a sensible sample case that you can refer to as you read the manual Each case you create with RSP is labeled with Client Name and Client Case For a given client you may save several Client Cases under that Client Name representing different scenarios you have worked up to show the client or analyses from previous years that you want to preserve Older versions of RSP had a limit of 250 cases per workbook there is now for all practical purposes no limit The client cases are stored in separate files in the Cases sub folder They must be named according to the following convention RSP3 ClientName ClientCase xls where ClientName and ClientCase are specific to the client and case If a colleague sends you one of these files just put it in your Cases sub folder and you may begin working with it in your copy of RSP After you finish working with that case Save it and give the file back to your colleague These files also can be e mailed to our Technical Support staff RSPsupport pa wagner com to assist in trouble shooting problems and general questions Warning Be very careful when sharing RSP Client Case Files The onus is upon you and your colleagues to coordinate the saving of changes to shared files If you want to save a case you must do an RSP Save Case If you want the last case you worked on to be visible the next time you ope
11. gt Unhide A dialog box with a scrollable list of the hidden sheets will pop up Military analogs are often insightful In a campaign that may run longer than expected it is prudent to have supplies and ammunition for that contingency in the pipeline Rather than less of them in case the campaign finishes early Judgment needs to be exercised about some of these data entries If there is a single amount of cash expected from sale of a business in a certain year then enter that amount on a later sheet If part time work is anticipated for the first five years of retirement then enter it on this sheet and delete the out year entries on the later sheet So you do whichever will require less editing 11 NOTE Dollar Values are in Year 2010 Dollars Client Income FirstPerson Second Person Pre Retirement Indexed Earnings 150 000 25 000 Pre Retirement Indexed Other Taxable Pre Retirement Non indexed Taxable Pre Retirement Indexed Tax Exempt Pre Retirement Non indexed Tax Exempt Su RESI Ea Retirement Indexed Earnings Retirement Indexed Other Taxable Retirement Non indexed Taxable Retirement Indexed Tax Exempt Retirement Non indexed Tax Exempt Social Security 827876 27 876 Client Contributions FirstPerson Second Person Personal Savings Profit Sharing 401 K Deductible IRA Non Deductible IRA Roth IRA 401 K Company Match 100 096 Client Expenses Couple Survivor Pre Retirement Indexed
12. included a slot for entering your client s Capital Loss Carry Forward from last year s Federal tax return In most cases this value would be insignificant although some of our users have inherited clients with sizable prior losses This value is not used if you select Accumulation Mode or if you have disabled the Automatic Payment of Taxes The final entry on this sheet is the VaR Level which is used to calculate the Value at Risk as described in Appendix D 4 First and Second Details These sheets contain schedules for entries already made on the previous two sheets and blank columns where no entry was made First Details refers to entries for First Person and Second Details refers to entries for Second Person All entries are available for editing here using standard Excel editing techniques Here you can refine salary growth for example or have spending in retirement gradually taper off with age You can replace the contents of any cell by selecting that cell and typing another number You can copy down by grabbing the handle in the lower right corner of a cell and dragging it downwards as far as appropriate Or you can double click on the handle and that entry will be copied to the bottom of that column You can also edit the Excel formula bar to perform more sophisticated maneuvers So for example you might plan for gradually reducing spending by setting the value in a cell equal to the previous cell multiplied by 995 and t
13. is still positive but that might be because spending is drastically below the desired You may also experiment with withdrawal caps of your own devising Just edit the numbers in the column by hand 6 Account Performance The next several sheets deal with portfolio characteristics what does the client now have how is it invested how will accounts be used to support spending and how will it be rebalanced over the years These structures in RSP are vastly more detailed and powerful than in the earlier versions of the program At first sight it may seem daunting and over elaborate but it is actually very simple very powerful and incorporates functionality that experienced users have asked for The big difference is we now allow for a more elaborate market profile Previously the world of investments was summarized simply by having Equities and Fixed Income taxed or not taxed Now the world of investments or the market profile can be described by as many as eleven asset classes and the user may set up as many different asset class breakdowns as desired RSP comes loaded with two of these Standard Asset Classes and Size Value Asset Classes We also now supply carefully chosen default numbers for returns volatilities and correlations for those two profiles The discussion for now will be about how to use the Standard Asset Classes There will be more below about how to choose and define alternative market profiles Do be aware howe
14. returns and unexpected expenses When they appear replanning is necessary Appendix F Some advice to practitioners Your first cut at a plan for a client will proceed by incorporating everything they have told you about earnings other income savings spending and how they are invested One run of RSP will reveal whether they are on close to being on or way off track If they are on track you will find ways to improve their investments and make them even better off It s the other two possibilities that are more often the case and which will require work and diplomacy From our experience helping advisors learn to use our program we have a few suggestions Propose a menu of remedies no one of which will seem that onerous The menu of options to work with is Consider postponing retirement and working a couple more years Take steps now to have a significant earned income in retirement by consulting or learning a new skill Save more pre retirement Spend less pre retirement 42 to spend less in retirement including a recognition that spending might even be additionally scaled back 5 or so each year after age 70 into a more aggressive portfolio especially in the earlier plan years Make small 2 adjustments in these and see if that package works If not make 3 adjustments Continue until you get the desired 8596 confidence If the client appears willing scale the spending back even more rather
15. that the planning period is longer but the final result should have the same kind of 85 90 success probability But you should then focus on an intermediate stage say the year of planned retirement and a wealth level at that age which is consistent with the long term plan Of course higher than 9096 confidence is even better With experience running the program you will see that many clients cannot afford 9596 confidence The way to achieve very high confidence may require drastic reduction in expectations of what they can spend or working many years past the point when they had hoped to retire This is the major eye opener we are all experiencing when risks are faced realistically as opposed to the former myopic view which assumed the expected return would occur each and every year There is more to say on this subject and additional material will appear from time to time at our website http mathfinance wagner com 1 System Requirements and General Advice The program requires a PC with a recent version of Microsoft Windows 95 or later and runs under Microsoft Excel 8 9 10 11 or 12 as included in Microsoft Office 97 2000 XP 2003 or 2007 Most of the functional capability of Excel is available and needed for entering inputs editing data and sizing windows for viewing and printing You also can customize by adding your own charts and worksheets The program will be used most efficiently by someone who is experienced wi
16. that accompany specific returns The user may experience fundamental changes in his personal situation with new responsibilities a change in marital status or a sale of some assets for much less than estimated in the planning A prudent individual will review everything every year even if there is no big change in the situation The program makes it easy for the advisor to do that Some folks take prudence beyond that and want more frequent reviews That should be resisted Before launching into use of the program we make some cautionary remarks The most crucial unknowns that the wealth manager will have to enter are the gross returns and volatilities of their client s investment portfolios although we now supply some carefully chosen default values for certain specific asset classes Also in looking at results you should tend to want rather high assurance that the plan will work If there is a 75 chance of your client being solvent at age 85 and he thinks he will live to that age that is not high enough assurance If there is a 75 chance of being solvent then there is a 25 chance of being broke And if he s sure he ll live to be 85 then he has a fair chance to live to be 90 Experience in running the program has evolved two basic rules of thumb For someone who has just retired or is near retirement and expects to live another 25 30 years you should look for a plan which has 85 90 success probability For someone younger than
17. to cover those slots used for municipal bonds or bond funds As with earlier versions of the program dividends and CGDs are paid as a fixed percentage of the balance of the respective fund The return is drawn from a lognormal distribution with parameters average return and volatility standard deviation So the growth in per share value is essentially return dividend CGD An Appendix goes into more detail about how this process is modeled Suffice it to say here our tax model wants to know what portion of the total return is subject to immediate taxation Default values for dividends and CGDs are given for the standard asset classes based on advice from our consultant also updated periodically at http mathfinance wagner cony 2003 Tax Reform The Jobs and Growth Tax Relief Reconciliation Act of 2003 has been incorporated into RSP Versions 3 10 and beyond and has necessitated a change in the way dividends are input Prior to this version dividends and short term CGDs were combined while long term CGDs were a separate input The slot you use for dividends now depends on the tax exempt status of the asset class If the dividends are tax exempt like municipal bonds or bond funds then set the Taxable ST CGD entry to N and include the dividend rate in the Annual ST CGD entry If the dividends are taxable at capital gains rates then set the Taxable ST CGD entry to Y and include the dividend rate in the Annual LT CGD entry If the d
18. to meet the desired spending If the spending needs remain unmet discretionary withdrawals will be made from the Taxed 401 K Profit Sharing Deductible IRA Non Deductible IRA and Roth IRA portfolios according to the Withdrawal Priority specified on the Current Portfolio sheet Within each portfolio withdrawals are made according to the Withdrawal Priority specified on the Account Performance sheet Withdrawals from accounts with the same priority will be made proportionately If the user has selected Accumulation Mode this step is simplified during pre retirement The current year s annual expenses including management fees you have entered for your client are withdrawn from the portfolio according to the Withdrawal Priorities described above These typically are zero but your client may have particular pre retirement spending needs that must be satisfied by dipping into their portfolio If the user has overridden the schedule of required minimum distributions causing required withdrawals to begin during pre retirement then these withdrawals will be used to satisfy the client s spending needs before making discretionary withdrawals from the portfolio Withdrawals from the Profit Sharing 401 K and Deductible IRA accounts will be taxed as ordinary income The capital gain on withdrawals from the Non Deductible IRA accounts will be taxed as ordinary income The capital gain on withdrawals from the Personal Savings accounts will be taxed
19. 00 000 t 1 500 000 5 5 th Percentile 1 000 000 10 th Percentile 15 th Percentile 25 th Percentile 500 000 50 th Percentile 0 2010 2015 2020 2025 2030 2035 2040 2045 2050 2055 2060 Year Check our website regularly for updates to the manual 44
20. 1 00 0 98 0 98 1 00 1 00 1 00 1 00 Total Wealth 85 0 92 0 93 0 93 0 92 0 91 0 89 0 84 gt 250 000 100 0 70 0 78 0 76 0 70 0 59 0 42 0 20 Probability 75 0 98 0 97 0 98 0 98 0 98 0 99 0 99 Total Wealth 85 0 85 0 88 0 87 0 85 0 82 0 76 0 63 gt 500 000 100 0 64 0 74 0 71 0 64 0 51 0 33 0 11 Probability 75 0 94 0 93 0 94 0 94 0 93 0 93 0 91 Total Wealth 95 0 78 0 82 0 81 0 78 0 73 0 61 0 41 gt 750 000 100 0 58 0 70 0 67 0 58 0 44 0 24 0 07 Example If you invest your portfolio in investment strategy D H then there is a 100 chance you will have more than 250 000 at age 75 12 There are advocates for both To us it seems easier for a client to understand and relate to a presentation in today s inflation adjusted dollars He knows what 1 is worth today but probably not what 10 is worth in the year 2030 28 Set the three Target Balances to dollar amounts that represent perhaps different levels of ambition Set the three Target Ages to different ages that span the client s life expectancy plus or minus a few years Looking at these numbers quickly reveals that changes to a more aggressive portfolio might help but not nearly enough For the spending sensitivity test percentage adjustments are applied across the board to the entire spending plan You should choose adjustments that make sense for the case under consideration If the client is in a marginal situation and current spending is likely to cause the portfolio to b
21. 716 661 5095 chance of having less than 3 716 661 Portfolio Sensitivity at 2060 Chance of Having Selected Target Amounts by Notional Portfolios NOW NOW A E GP C Gi D D Gu 2 50 000 0 93 0 94 0 93 0 93 0 90 0 85 0 73 500 000 0 90 0 92 0 91 0 89 0 85 077 0 59 750 000 0 87 0 90 0 88 0 85 0 80 0 68 0 47 Spending Sensitivity ai 2060 Chance of Having Selected Target Amounts by Adjusted Spending Schedule 1596 10 596 Original 596 10 15 Decrease Decrease Decrease Plan increase Increase Iicrease 250 000 0 99 0 98 0 96 0 93 0 89 0 84 0 79 500 000 0 98 0 96 0 93 0 90 0 86 0 80 0 75 750 000 0 96 0 94 0 90 0 87 0 82 0 76 0 72 43 Final Wealth Density Year 2010 Dollars Es HOME Table Year 2010 Initial Portfolio Value 1 360 000 Year 2060 A Portfolio Return and Volatility 3 496 12 196 y 0 120 Percentiles 0 100 41 146 0 080 0 060 475 879 gt 0040 884 208 0 020 1 504 594 0 000 3 716 661 0M 2M 4M 6M d10M 12M Final Portfolio Size Millions Projected Retirement Spending Year 2010 Dollars 100 000 75 000 50 000 25 000 Dezired Spending 30 th Percentile 20 th Percentile 10 th Percentile Retirement Spending Achieved 0 2010 2015 2020 2025 2030 2035 2040 2045 2050 2055 2060 Year Name Demo Date 4 23 2010 Case Remedy Projected Wealth Year 2010 Dollars 4 000 000 3 500 000 3 000 000 2 500 000 N I7 2 0
22. 75 000 70 O00 Pre Retirement Non indexed jl 0 Retirement indexed 100 000 85 000 Retirement Non Indexed Oj 0 There are one or two columns of entries depending on whether the one or two person case was selected on the previous sheet Some two person cases may be accurately and more quickly rendered as one person cases But if there are sources of income for both parties it is preferred to treat it as a two person case there is less chance for error and it will be easier to check and double check entries Notice that every source of income is either indexed for inflation or not and is either taxed or not This needs no further explanation Enter as appropriate You may enter earnings that will then be indexed for inflation and need to edit because there is room for salary growth beyond inflation especially for someone who is several 12 years from retirement Older versions of the program did not have a slot for untaxed income and that omission is addressed in RSP Social Security is indexed to represent your expectations as to how it will track with other cost of living increases An entry for that is on the next sheet You or your clients are responsible for estimating the starting SS amount we do not supply a calculation for that For the savings entries note that Profit Sharing does not come out of income it is an employer contribution All the other savings amounts do come out of income with proper tax treat
23. IS CONTROLS aei eaten ees tao oes Deve eec he Haein hk oer Ere Eee eS 24 IO UTT HEETSLA c Ie LES a ten M ERA DONDE EDO UNE DOSE EDEN LE bebe ER 27 IM REMINDERS 3l 1 ACCOUNT CORRELATION po tope aA tope ei pastos Ed 32 2 MANAGEMENT FEES ii sieben bet eiie tees 32 ST ACT ANBBESS se el seas estere eise dssdo eM odiosa 33 STATE TAR RUN eG OO ONE AGES 33 Do JA RIS oett eond vt rode tla Maat bees tb e or aie ren Ue bie the Sees e rac 33 6 PORTFOLIO CONSOLIDATION Pep Up 33 Ts CONFIGURATION cel icin eves AG ems 33 APPENDIX A MODELING OF INVESTMENT RETURNS eene 34 APPENDIX B USE OF THE LOGNORMAL DISTRIBUTION ecce 37 APPENDIX C CASH FLOW IN 37 APPENDIX D VALUE AT 41 APPENDIX E LONG TERM 42 APPENDIX SOME ADVICE TO 42 Part I Introduction RSP is a tool for retirement planning It is intended for professional advisors but can be used by individuals planning their own retirement as well The focus is to enable a user to estimate whether a savings plan combined with a current portfolio are sufficient to sustain desired spending levels during retirement RSP addresses the uncertainty in a plan by running Monte Carlo simulations of portfolio per
24. MDs are made from the tax deferred portfolio Profit Sharing 401 K Deductible IRA and Non Deductible IRA only according to the schedule of percentages input on the Detailed Inputs sheet This percentage is applied to the total value of these tax deferred accounts to determine the dollar amount of the RMD Withdrawals are made from the tax deferred portfolio according to the user specified portfolio and account withdrawal priorities until the entire RMD amount has 38 been satisfied The total amount withdrawn will supplement the income inputs and will be used to satisfy the annual expenses portfolio management costs and the first tax bill installment Any remaining amount will be deposited to the taxed portfolio Withdrawals from the Profit Sharing 401 K and Deductible IRA accounts will be taxed as ordinary income The capital gain on withdrawals from the Non Deductible IRA accounts will be taxed as ordinary income This capital gain or loss is calculated by subtracting the pro rated cost basis from the withdrawal amount The total cost basis then is updated by subtracting the pro rated cost basis Deposit to Tax deferred Portfolio If there is not enough available income to implement your client s planned pre retirement savings to the Roth IRA Deductible IRA Non deductible IRA and 401 K portfolios those planned values will be reduced to the level of available income These reductions are made according to the following rules
25. RSP User s Manual Daniel H Wagner Associates Inc RSP 3 The Retirement Spending Planner 1998 2010 Daniel H Wagner Associates Inc Version 3 22 40 Lloyd Avenue Suite 200 RSPsupport pa wagner com Malvern PA 19355 http mathfinance wagner com 610 644 3400 TABLE OF CONTENTS PART I x INTRODUCTION 0 0000000000000000000000000000000000000000000000000000000000000000000000000000 3 1 SYSTEM REQUIREMENTS AND GENERAL ADVICE cceeceeee eee nennen 4 2 WHATS AVAILABEEIN RSP 5 15 nho edoiiaa iibri 5 3 WORKING WITH CLIENT CASE 6 4 WORKING WITH ACCOUNT PERFORMANCE FILES eee 7 5 TECHNICAL SUPPORT csssssssssccccscccssssessccccseeeeasssssssccesseeeensnssssscccsseuenassssssscss 8 PART II THE VISIBLE SHEETS s sss Ws Ts IG I ack c Ae ELA RUM LEE E 8 2 CLIENT CASH COW seeps ceed alse eda DOR E PPM 11 Si CLIENT ASSUMPTIONS ee darte veia us 13 4 FIRST AND SECOND DETAILS sssssecccccecccesssseseccceeseecaausseeeeecesssseauaaneseeeeess 15 5 DEFAILED INPUTS wasii Bodo ae Ho UO HOUR Fer EDO hea hate hae 16 6 ACCOUNT PERFOR MANGE asic eh ch Us t tte ap a e 18 T CURRENT PORTFOLIO Ivo tet toi tetto tei bisce a totem 20 8 REBATPANGING PLAN 2 55 35 8 Bt eec tie eint 22 9 REBALANCING SCHEDULES duri e MURUS 23 ANALYS
26. Status Married Joint single C Married Separate Head of Household Survivor s Pre Retirement Federal Exemptions 3 550 Survivor s Pre Retirement Federal Standard Deductions 5 700 Survivor s Pre Retirement Federal Itemized Deductions 0 Survivor s Retirement Federal Exemptions 3 650 Survivor s Retirement Federal Standard Deductions 5 700 Survivor s Retirement Federal Itemized Deductions 0 Taxable Social Security Income 85 00 State Tax Rate Local Tax Rate 0 00 96 Capita Loss Cany Forward 2009 Federal Return Miscellaneous Fees Management Rate Value At Risk Parameters VaR Level 2 50 The block of entries for Survivor is relevant if the ages you entered imply one person will outlive the other one It may be foreseen in that event that tax exemptions and deductions will change as well as spending and you can make the appropriate changes You can enter a flat rate for the state and local tax rate You can also enter a graduated state tax rate if you access a hidden sheet to be discussed later Activate that schedule by typing in the two letter symbol for the state Similarly we have a mechanism for accessing a management fee schedule to be discussed later The program will add the State and Local Taxes incurred to the Itemized Deductions in any year that the client itemizes 7 If you think differently just edit by hand perhaps by inserting a different inflation factor in the Excel formula bar 14 We have
27. as capital gains Capital gains or losses are calculated by subtracting the pro rated cost basis from the withdrawal amount The total cost basis then is updated by subtracting the pro rated cost basis Even though no tax will be incurred during pre retirement in Accumulation Mode the cost basis will be updated to be available for tax calculations during retirement Rebalance Portfolio Rebalancing will be performed using either the Global Taxed scheme or the Deferred Taxed scheme as described in the Rebalancing Plan section Redistribution occurs first within the Tax deferred portfolio Deductible IRA Roth IRA 401 K then Profit Sharing and then within the Taxed portfolio Personal Savings in order to 40 minimize the tax consequences of redistribution Note that a final calculation of the tax bill will be made subsequent to this step with any unpaid or overpaid taxes carried over to the next year Apply Returns Randomly drawn asset class returns are now applied to the current portfolio amounts resulting in the growth or shrinkage of the portfolio At this point the dividends and capital gains distributions are calculated and recorded for tax purposes only The cost bases for asset classes that incur dividends or capital gains distributions will have these amounts added to them as they have already been taxed Compute Final Tax Bill The final step in the cash flow is to compute this year s total taxes incurred Any differe
28. cial Security payments will increase and one for everything else Some programs have a model for inflation also behaving randomly RSP does not The next block of entries has to do with deductions for federal income tax pre and post retirement If the client itemizes enter 0 for Standard Deductions and if the client does 13 not itemize enter 0 for Itemized Deductions If both entries are positive you will get an error message Note that these entries will be ported to a schedule and indexed up by the inflation entry you made This represents our best estimate as to how these allowable amounts will grow as the years go by and inflation occurs All these tax entry boxes go away if you elect to disable the tax model on the Client Basics sheet The program has a model for Social Security and Medicare taxes and no entries for them are required Earlier versions of RSP did not do this NOTE Dollar Values are in Year 2010 Dollars Inflation Assumptions Annual Inflation Rate 3 00 index Rate for Social Security 2 80 Tax Assumptions Couple s Filing Status Married Joint Couple s Pre Retirement Federal Exemptions 7 300 Couple s Pre Retirement Federal Standard Deductions 11 400 Couple s Pre Retirement Federal itemized Deductions 0 Couple s Retirement Federal Exemptions 7 300 Couple s Retirement Federal Standard Deductions 11 400 Couple s Retirement Federal itemized Deductions 0 Survivor s Filing
29. de potential numbers distracting irrelevant and not to be dwelt upon They might cause the client not to focus on what is important minimizing downside risk On the other hand showing very low percentiles like 1 or 2 might be alarming Few people can afford 98 or 99 safety A plan that is 95 safe is very good and everyone should be pleased to have attained that level There is not much point in mentioning that it is not 98 safe Those last few percent come at great cost which you can find out by doing some experiments Sensitivity Analysis Controls 250 000 500 000 Target Balances 750 000 Rebalancing Plans Global Taxed The remaining inputs on this sheet are relevant only to the sensitivity studies The Target Balances are three amounts that represent different levels of your client s ambition regarding the dollar value of their estate The Target Ages are three different ages that represent key milestones in a client s life such as retirement age and the age at the end of the analysis The sensitivity studies will produce a table containing the probability of your client s portfolio value being below the three Target Balances at each of the three For those who are not familiar with this for the cumulative F x is the probability wealth is less than or equal to x and for the density f x is the probability that wealth is equal to something in the vicinity of x 25 Target Ages for seven different varia
30. drawn if it is to be depended on for a number of years A safe withdrawal rate depends both on the portfolio composition and on the number of years it has to last Assuming the portfolio has a healthy proportion of equities we have devised some good rule of thumb withdrawal rates that are activated if you click the Switch button toggling the text above that column to ON So if you activate the withdrawal caps here is what happens If the cap for that year is x and the desired spending input would require withdrawal of more than x of total portfolio then spending is forced to be reduced by enough to satisfy the cap restriction That reduction will be evident in the Spending Achieved chart on the Detailed Charts sheet if it occurs in enough of the replications Since this is a Monte Carlo program it is conceivable that in some replications larger than minimum withdrawals are needed to support spending we then do not recompute the withdrawal schedule as should be done to avoid a penalty This will not happen in most sensible plans and is not an inaccuracy that should cause concern 17 RSP comes with the withdrawal cap OFF set equal to 100 as the default mode This is the standard way to run the program There is danger in running the program with the withdrawal cap enabled if you do not thoroughly understand the implications You may be led falsely to think that desired spending is occurring and there is no problem because final wealth
31. e drained you want to set all the numbers to represent reductions in spending If the client is in a very comfortable situation you might include some increases in spending to show him that his spending can safely be increased somewhat For our test case it appears 15 spending reductions would solve the problem but we d prefer to find a package of more palatable remedies Spending Plan Strategies Year 2010 Dollars Age 15 10 96 5 96 Original 5 96 10 96 15 96 Decrease Decrease Decrease increase increase increase Probability 75 1 00 1 00 1 00 1 00 0 99 0 99 0 98 Total Wealth 85 0 99 0 98 0 96 0 92 0 87 0 80 0 74 gt 250 000 100 0 93 0 86 0 79 0 70 0 59 0 50 0 40 Probability 75 1 00 0 99 0 99 0 98 0 97 0 95 0 92 Total Wealth 85 0 97 0 94 0 90 0 85 0 79 0 72 0 64 gt 500 000 100 0 88 0 81 0 73 0 64 0 54 0 44 0 36 Probability 75 0 98 0 97 0 96 0 94 0 91 0 87 0 83 Total Wealth 85 0 93 0 89 0 84 0 78 0 72 0 63 0 55 gt 750 000 100 0 82 0 75 0 67 0 58 0 48 0 40 0 32 Example If you increase your spending plan by 5 then there is 99 chance you will have more than 250 000 at age 75 Our users tell us that the output from these tests make particularly effective presentation material to clients We expect that the output from the new Return Sensitivity studies also will be effective tools for reviewing Black Swan events with your clients If your client s current plan has not been hardened again
32. e looks a lot like his brother Basil then Tom probably looks a bit like Basil Mathematically given the two correlations of 9 then the correlation between X and Z cannot be less than 62 Anything higher than 62 is allowable This difficulty can actually arise even if correlations were calculated from real data due to round off errors It can also happen by copying only the first two significant digits from data that would work if you copied four digits So use the numbers we give you and if you want to change them you ve been warned 2 Management Fees The sheet ManagementFeeClassA is included as an example Note that you can edit it to create additional breaks and any marginal fees you care to insert Create additional Management Fee sheets by copying the default sheet to a new inserted sheet with the name ManagementFeesYourName Use the same format as ours Then use YourName as the Management Rate entry on the ClientAssumptions sheet and that schedule will be active for that case These sheets will not be hidden automatically by RSP If you want to hide them use Format Sheets Hide Saving Changes These sheets are not saved in your Client Case files so you must do an Excel Save to preserve any modifications you have made to them 32 3 Tax Tables Similarly you can edit the tax tables to change thresholds and marginal rates to experiment or to represent non US situations You will have to do this also if you choose to
33. ecrease Decrease Plan Jnerease increase Dicrease 32 50 000 077 0 68 0 58 0 48 0 39 0 30 0 23 500 000 0 73 0 64 0 55 0 45 0 37 0 28 0 22 750 000 0 69 0 61 0 51 0 42 0 34 0 26 0 20 The detailed charts are accessible from this report although many users may choose to present only this summary report to their clients Users may enter their company name on this summary report and may customize the formatting if they wish Users are encouraged to design and implement their own report formats That s one of the reasons for building RSP on Excel Remember the caution to do that without cutting any of our cells create your own harmless copies and have at them 12 Reminders Navigate with gt gt when you wish to port new entries with the tabs when you need to preserve edits Use Accumulation Mode if you want to model a specific pre retirement savings plan without bothering with entries for income spending and taxes If you choose Income Mode the pre retirement income must be sufficient to cover the savings plan otherwise the plan will be reduced to fit the income Then when spending and taxes are addressed withdrawals from the portfolio may occur If the ages of a married couple are more than ten years apart enter as a two person case Enter amounts in current dollars that you want to be indexed for inflation on First or Second Details enter absolute dollar amounts on Detailed Inputs In cases where both an Indexed and a N
34. efine ten standard portfolio allocations using the 11 asset classes typically progressing from most aggressive to most conservative Provision to redefine and store customized asset class schemes Social Security and Medicare FICA taxes calculated Ability to itemize federal deductions with state and local taxes added for each rep Ability to create graduated state tax tables Ability to disable taxes without modifying the tax tables Save only pre retirement mode as an option Required minimum distributions RMDs are calculated automatically with the ability to override the default schedule Client data can be saved in separate Excel workbooks making it easier to send problem cases to us and to share cases with colleagues Mechanism that simplifies annual updates of your client cases Ability to run small number of reps with a convention that 0 replications will use zero volatility Graduated advisor management fee Extended re balancing model separate for Taxed vs Tax deferred complex balancing involving the 11 asset classes Provision for tax exempt income category Concise summary report Sensitivity tests that compare portfolio allocations spending plans or unexpected market return scenarios Black Swan events Ability to convert IRA accounts to Roth IRA accounts Analyses can be run over a 75 year time window for clients aged 0 to 125 3 Working with Client Case Files The Client Cases button
35. fa colleague sends you one of these files just put it in your Accounts sub folder and you may begin working with it in your copy of RSP If you make changes to the account performance data Save it and give the file back to your colleague These files also can be e mailed to our Technical Support staff RSPsupport pa wagner com to assist in trouble shooting problems and general questions Warning Be very careful when sharing RSP Account Performance Files The onus is upon you and your colleagues to coordinate the saving of changes to shared files The only way that your market profile data will be saved in an Account Performance File is via the Save or Save As buttons on the Account Management dialog An Excel Save will not save these files nor will an RSP Save Case A Save Case will save the market If you do an Excel Save without having done RSP Save Case the entries on that sheet will appear when you reopen RSP but the Client Case File will not match what you see on the screen profile data to the Client Case File but not to the Account Performance File This allows you to customize this data for each client without changing your official data After saving an Account Performance File you must remember to load the revised market profile data into any client case that uses that file For each of these cases you must use Client gt Case to load the case then Account Management Open to load the revised mar
36. formance Information is entered as to current portfolio amounts and types of investment how much more income is expected before retirement what other benefits are expected during retirement a description of tax liabilities and what spending levels are desired Cash flows for the time period of interest are run with random returns on the investments and final wealth is noted Then the cash flows are rerun thousands of times and the resulting distribution of final wealth is plotted in various ways The latter reveal the probability that a financial objective expressed in dollars at some target age is achieved The program can be used to devise a plan for when retirement is possible with the level of spending that is desired It is also intended to address the case of someone who is already retired to check that current spending levels are prudent and sustainable Certain inflexible inputs can be held fixed while others are varied throughout a range of options to find fixes for plans that fall short of the objective Some of those variations can be done in automated fashion It is important to realize that such plans must be reviewed regularly preferably on an annual basis An unexpectedly bad year may cause the user to be in an entirely different situation where reductions in spending have to be considered Basic changes in the economic landscape may cause professional market analysts to advise new guidelines for assumptions about volatilities
37. g tasks makes required minimum distributions from tax deferred accounts pays management fees makes the designated deposit to or withdrawal from the portfolio accounts rebalances the portfolio and computes and applies returns to the accounts No tax consequences are considered during pre retirement in Accumulation Mode 37 In Income Mode by contrast schedules for income annual expenses and savings are entered A savings schedule is submitted from the starting year out to any year in the simulation but most sensibly out to the year in which both parties have retired A schedule of annual expenses also is submitted for every year in the simulation Detailed income inputs tax inputs and other miscellaneous inputs also are entered The simulation loop considers all tax consequences etc and has a rule based methodology to address shortages and surpluses For each year simulated certain activities take place investments have returns drawn from a lognormal distribution income from various sources occurs required minimum distributions from tax deferred accounts take place planned savings are deposited taxes and management fees are incurred and paid spending takes place and the portfolio is rebalanced These cash flows are tracked for all the years being simulated and key statistics to support our reports are saved The following sections of this appendix describe each of these activities in sequence Compute Portfolio Management Fees
38. hen copying that entry down reducing spending by an additional half of one percent each year Note the following extremely important feature All dollar amounts on the First Details sheet are expressed in current dollars Where appropriate these amounts will be indexed upwards for inflation subsequently They are presented here in current dollars to allow you to edit them with amounts that you are estimating in current dollars but expect to be larger due to inflation For example the client may anticipate paying for college tuition starting ten years from now and wants to enter that extra spending for a four year period starting ten years from now He may have an intuitive understanding of what college costs are today and can enter that number assuming it will be inflated by the same amount as other cost of living increases Other expenses or income may be known about in absolute terms such as the proceeds from insurance or a previously agreed upon note payment Those entries will be made later on the Detailed Inputs sheet not here Note The detailed expenses exemptions and deductions for a Couple are located on the First Details sheet these details for the Survivor are located on the Second Details sheet 15 Advanced Feature Some of our users like to create sophisticated Excel formulas that refer to cells on the detailed sheets In previous versions of RSP they were required to store these formulas on separate worksheets forci
39. ill need to be edited and after so doing when you move among sheets you should navigate by clicking on tabs along the bottom to preserve those edits RSP allows you to update an old case by typing in the current year That preserves many of the inputs that are not going to change and saves typing Clean Up License Client gt gt Workbook Info Cases Client Name Demo Case Base Date 416 2010 Year at Start of Analysis 2010 Current Year 2010 Two person Client Ages First Person Second Person Age at Start of Analysis 58 Age Now 58 50 Retirement Age Social Security Start at Age 66 6 Age to End Analysis 100 110 Cash Flow Controls Disposition of Excess Income amp Personal Savings Q Spending Pre Retirement Cash Flow Accumulation Mode S Income Mode Rebalancing Method Global Then Taxed ODeferred Taxed Automatic Payment of Taxes Automatic Payment O Ignore Taxes Penalty on Early Distributions 49 Apply Penalty Ignore Penalty Uncheck the Two person toggle for one person cases or two person cases where the income saving and spending assumptions can conveniently be combined Checking the Two person toggle replicates many of the entry fields on later sheets for the second person In any case use the Two person option if the age difference for a married couple is more than ten years so that Required Minimum Distributions are calculated correctly See
40. is of the KS test described in Kendall Stuart and Ord p 1192 Advanced Theory of Statistics Volume 2 OUP 1991 The table shows that the test statistic is within the 5 significance level of 0809 for a sample size of 120 for all but one of the funds Windsor II The problem with the monthly Windsor II data is not leptokurtosis however It does not have fat tails nor is it overly peaked its problem is a sparseness of values in the interval 02 67 This is a very quirky data set However the annual returns for the fund are quite normal looking If monthly returns fit or nearly fit the normal then quarterly and annual numbers will fit all the more so by a Central Limit Theorem argument Vanguard Fund Monthly Returns Standard Dev Max Discrep Explorer 1 545 5 586 0537 GNMA 0 632 0 950 0593 LT Corp 0 747 1 806 0444 S amp P 500 Idx 1 417 3 860 0522 1 402 4 536 0433 PRIMECAP 1 830 4 700 0433 Health Care 1 981 3 843 0626 Energy 1 174 5 755 0752 STAR 1 119 2 454 0680 Wellesley 0 954 1 955 0572 Wellington 1 133 2 657 0746 Windsor 1 388 4 375 0713 Windsor II 1 374 3 718 1011 US Growth 1 398 4 132 0547 International Growth 0 902 4 061 0372 34 As further evidence we include an analysis of annual return data from Ibbotson s Year Book from the years 1972 to 2000 for Large Cap Small Cap Long Term Corporate and Treasury Bills indices For
41. it is desirable to use the available labels A through J to represent a gradually more conservative sequence of portfolios Note that the last asset class Money Market is treated differently it is calculated so that the row adds up to 100 If you enter numbers that tote to more than 100 you will get an error message as you exit this page This sheet displays a collection of current portfolio statistics to help users see the implications of their inputs The Expected Portfolio Return and Volatility along with the current 1 year Value at Risk see Appendix D are provided for each standard portfolio However where the Value at Risk is displayed in dollars on the Current Portfolio sheet the value shown here is as a percentage of the amount invested in that standard portfolio 23 Advanced Feature Some users will prefer to use labels that are more descriptive than the single letters A through J Simply use Tools Protection Unprotect Sheet to unlock the sheet and then replace A through J with your own labels When you enter the name of one of these schedules on the Current Portfolio Rebalancing Plan or Analysis Controls input sheets you will need to enter these names exactly as they appear on the Rebalancing Schedules sheet You also may need to widen columns on the sheets that display these labels Commit to z HOME Changes Portfolio Account Rebalancing Investment Plan Default Schedules Intemational
42. ividends are taxable at regular income rates then set the Taxable ST CGD entry to Y and include the dividend rate in the Annual ST CGD entry 19 The Withdrawal Priority entry allows the user to specify which accounts and in what order are gone after to support spending and taxes Put 1 for the account you use in practice usually the cash account You can put several 1 s if there are several that you would use equally Then put 2 s for the accounts you would go to next and so on Bear in mind that elsewhere you will specify a rebalancing scheme so that these ready money accounts may be replenished annually In practice many advisors use a process where they put say five years worth of spending in cash and only replenish it when the market is up on some such strategy We do not model that detail nor is it needed for the RSP process The Tax Managed Fund entry allows the user to designate accounts that have tax managed alternatives These tax managed alternatives will have performance adjustments made but only to the accounts included in the Personal Savings portfolio The Management Fee represents the reduction in return that results when you use the tax managed alternative The Tax Efficiency measures how well the tax managed alternative is able to reduce the fund s taxable income the dividends and capital gains distributions If no Tax Managed Fund entry is set to Y the Management Fee and Tax Efficiency columns will be hidden
43. ke a transformation to the input mean return m and volatility s to cause the resulting lognormal distribution to have the desired first two moments A lognormal distribution with parameters 4 and o has mean 12 and variance s 1 gt 1 So we solve those two equations for the unknowns 4 and and use them to create lognormal which has all the desired properties Finally a comment on why we do not use resampled historical data The main reason is that we prefer to allow users to use their judgment as to what returns may average going forward As we write this for example some economists are forecasting market returns for the next five years being below what they have been for the past ten years by as many as 100 to 250 basis points A financial planner would not be able to take advantage of this expert opinion if we were locked into historical returns And with this approach they can in effect use historical returns by matching means and volatilities So you get to have it both ways with our approach Appendix C Cash Flow in RSP RSP allows the user to run the Monte Carlo simulation in two primary modes during the pre retirement phase Accumulation Mode and Income Mode In Accumulation Mode a savings schedule and an annual expenses schedule are submitted for each year before retirement representing deposits to and withdrawals from accounts for that year The simulation loop then does only the followin
44. ket profile data then Client Cases Save Case to save the case with the revised data 5 Technical Support The answers to many questions that users have regarding the use of RSP and the solutions to some typical problems that occur can often be found in either the user s manual or our RSP FAQ page http mathfinance wagner com PRODUCTS RETIREMENT RSP rspfaq html Users should consult these resources before contacting technical support If a satisfactory answer is not found there then contact technical support The very best way to get a prompt response to a technical issue is to send a detailed question or description of your problem to RSPsupport pa wagner com Be sure to include the version of Windows and Excel you are using and describe the conditions under which the problem occurs It would also be helpful to describe any recent maintenance performed on your computer especially any which occurred subsequent to the most recent successful use of RSP Finally attach the Client Case File that you were using to help us determine if there is an error in your input data If none of the above resources are satisfactory then call 610 644 3400 and ask for RSP 3 Technical Support The next available RSP 3 support technician will take your call If none are available leave a detailed message so that the appropriate person may be assigned to your call The remainder of the user s manual has three major sections a detailed page by page de
45. l be applied The transfers will be made according to the current allocations of the Deductible IRA and Non Deductible IRA accounts If you wish to spread the tax burden across multiple years then use conversion percentages that are less than 10096 For example to perform a full conversion but pay the tax bill over the subsequent 2 year period enter 0 for this year 50 for next year and 100 for the following year Also on the Detailed Inputs sheet is the Tax deferred Distribution schedule By default we enter the minimum percentages starting at age 70 for the first person assuming joint life for the two persons As allowed if their age difference is ten years or less we make the calculation as if the age difference is equal to ten years If the age difference is more than ten years we make the calculation based on actual joint life For a single person or the Survivor we calculate joint life assuming a fictitious partner who is ten years younger Clicking the Switch button at the top of this column will toggle between the ten year joint life Individual and the joint life using the actual age difference Joint Unless the two persons ages are more than ten years apart the actual schedule will be identical regardless of the switch setting In most cases there is no reason to switch to Individual so we recommend leaving it set to Joint 16 These numbers may be edited The user is responsible for those entries we do not enforce an
46. lowing year In any year that the client s itemized deductions are positive the State and Local Taxes incurred are added to the itemized deductions and exemptions input This step is not performed during pre retirement if the user has selected Accumulation Mode 39 Deposit to Taxed Portfolio If there is not enough available income to implement your client s planned pre retirement savings to the Taxed portfolio the planned value will be reduced to the level of available income The net income that supports the Personal Savings contributions is the sum of Earnings Other Taxable Income Social Security and Tax Exempt Income minus the sum of Annual Expenses unpaid taxes from the previous year management fees and the tax deferred contributions made If the Personal Savings contribution exceeds this net income then the contribution will be reduced If this net income exceeds the input Personal Savings contribution the excess will either get added to the contribution or spent depending upon the setting of the Disposition of Excess Income flag Amounts deposited to each Personal Savings account will be added to that account s cost basis Meet Expenses Spending The spending needs desired expenses plus current tax bill and portfolio management fees for a given year will be addressed by drawing from wage earnings social security and the other income sources first If required withdrawals have been made then they too will be used
47. ment e g Deductible IRA and 401K contributions are treated as tax deductible Note we have added a treatment of Non deductible IRA contributions In reality those Deductible and Non deductible IRA contributions amounts may all be in one fund but we keep them segregated It is important to note that the user is responsible for all contributions being allowable We do not have code for example to check whether a 2 000 limit for Deductible IRA contribution has been exceeded The 401 K Company Match is applied to the combined 401 K contributions of both parties in a two person case If the company match is significantly different for the two parties either enter an approximate blended rate or add the dollar amount of the match to the Profit Sharing contributions Client Expenses means all spending other than taxes Social Security and Medicare taxes will be withheld from earnings by the program and Federal State and Local taxes will be calculated and paid Note the block of entries for Survivor These are relevant if the ages you entered imply one person will outlive the other one It may be foreseen in that event that spending amounts will change 3 Client Assumptions This page contains necessary information about inflation the client s tax status miscellaneous fees and parameters that control the calculation of certain portfolio statistics There are two slots for assumptions about inflation One is for your estimate of how So
48. n RSP you also must do an Excel Save before closing RSP However the Excel Save is not necessary 4 Working with Account Performance Files The Account Management button on the AccountPerformance sheet accesses the filing system for Account Performance Files These files contain market profile data that describes the performance of the asset classes it contains Clicking the Account Management button presents you with the following file management choices Open load an existing Account Performance File into RSP Save saves the market profile inputs currently displayed in RSP replacing the data in the current Account Performance File Save As saves the market profile inputs currently displayed in RSP replacing the data in a new or different Account Performance File Save as Default saves the market profile inputs currently displayed in RSP replacing the data in the Default Account Performance File Delete removes an existing Account Performance File from RSP s database After installing RSP you should begin by creating one or more market profiles that you intend to use for all or most of your clients saving them via Save As Default The program comes loaded with sensible market profiles that can be used by those who do not have access to more detailed performance data The Account Performance Files are stored in separate files in the Accounts sub folder They have no naming convention although large firms may wish to impose one I
49. nce between this final tax bill and the amount paid previously will be paid or credited in the following year This step is not performed during pre retirement if the user has selected Accumulation Mode Appendix D Value at Risk The concept of Value at Risk VaR is sometimes used as a rough handy measure of the risk in a portfolio The output from RSP itself is of course the ultimate measure of risk But as an additional measure VaR can be a useful educational device It can be defined in many ways but here is how we implement it We calculate the amount of dollars that might be lost in a single year in an unfavorable market which might only occur x of the time or less Typical choices for x would be 2 5 or 5 Assume the client s assets are invested in a portfolio that has expected return 4 and volatility or standard deviation At the 5 level then the loss might be Al exp u 0 05 where A is the amount in the portfolio and is the standard normal cumulative distribution function A return of 1 05 u 1 6450 or less for the year would only occur 5 of the time Exponentiation converts to the lognormal multiplier for account growth and 1 minus that is the dollar gain or loss depending on sign In RSP a positive VaR indicates a loss while a negative VaR indicates a gain 41 Appendix E Long term planning Users often comment on the fact that a very long term plan like 40 or 50 years into the f
50. ng them re enter them when they switched between client cases RSP 3 provides hidden cells to be used to enter any user defined data or formulas These hidden cells will be saved in the Client Case File along with the other input data The hidden area is named FirstUserDefinedTable on First Details and SecondUserDefinedTable on Second Details To make these cells visible first use Tools Protection Unprotect Sheet to unlock the sheet and then enter the name of the hidden area in Excel s Name Box Now use FormatColumnUnhide to reveal the cells and you are ready to enter formulas or data 5 Detailed Inputs This sheet now has all the amounts on First and Second Details indexed for inflation and added together and a couple of new entries This is where you can incorporate amounts that are known in absolute dollars whether expenses savings or income However if an Indexed slot for an input is available on First or Second Details it is easier to enter absolute dollar amounts there The only instances where you must enter these absolute dollar amounts on the Detailed Inputs sheet are those situations where a different inflation assumption is required There is a schedule of IRA to Roth IRA Conversions on this sheet which will allow you to convert any percentage of the Deductible IRA and Non Deductible IRA accounts into your Roth IRA accounts The appropriate taxes on these transfers will be paid but no penalty for early withdrawal wil
51. not continue your maintenance contract You can even change the number of breakpoints by editing the numbers in Column 5 Other types of changes can be made but you may need help from our technicians Warning Do not change the integers in columns 1 or 3 try to create a new filing status or cut any of our numbers Saving Changes This sheet is not saved in your Client Case files so you must do an Excel Save to preserve any modifications you have made to it 4 State Tax Edit ours or copy our sheet use Excel s Edit gt Move or Copy Sheet and rename it with the name StateTax followed by the two letter abbreviation for the state Change the schedules for all four filing statuses using the same format as ours These sheets will not be hidden automatically by RSP If you want to hide them use Format Sheets Hide Saving Changes These sheets are not saved in your Client Case files so you must do an Excel Save to preserve any modifications you have made to them 5 ARL These are the IRS published average remaining lifetimes Ignore the first three columns They are there for a future contingency when we might need individual ARLs for male and female The numbers in the block to the right of that are joint numbers We use those to fill the Tax deferred Distributions column on the DetailedInputs sheet 6 Portfolio Consolidation This is a worksheet that can be used to consolidate numbers for up to five investments It will calc
52. nsitivity studies allow you to compare the impact of changes that your client makes to their plan RSP now has two types of Return Sensitivity studies that allow you to examine the effects of unexpected market performance or so called Black Swan events Selecting Fixed Return in Year 1 will start the simulation with a market wide fixed return to model such an event in the first year of the analysis period This attempts to answer the question if my portfolio has a significant loss this year will I need to change my future plans Selecting Annual Adjustment will reduce or increase the random returns by a constant value each year to model the effects of unexpected performance for the duration of the analysis This attempts to answer the question what if the market does not behave as well as your projections suggest Please note if you set up the exact same case that we have shown so far and run the program you could get output that differs slightly from the sheets in the manual That is because the version of the program you are using contains updated correlation data and perhaps some algorithmic enhancements 26 11 Output Sheets Final Wealth Density Year 2010 Dollars lt lt HOME a Table Year 2010 Initial Portfolio Value 1 360 000 Year 2070 A Portfolio Return and Volatility 7 196 9 096 0 600 Percentiles 0 500 0 Y 0 400 0300 0 gt 0200 0 0 100 j 0 0 000 127 951 0M 1M
53. of making these transfers could be considerable Finally the program rebalances the Taxed portfolio to achieve the specified Taxed Allocation 22 We have users who prefer the Global Taxed scheme and those who prefer the other We suspect that both of them are useful in certain circumstances The Rebalancing Plan sheet is initialized with formulas that refer to the previous year s entry with the first year s Global or Tax Deferred and Taxed entries set to C and G respectively This saves you typing by allowing you to enter a new schedule to begin in a specific year and subsequent years will be changed automatically lt lt Home gt gt Portfolio Account Rebalancing Investment Plan Age Age Global Taxed of of Rebalance Schedule Schedule Year First Second Y or N Prima Seconda 2010 58 55 Y C G 2011 59 56 Y C G 2012 60 57 Y C G 2013 B1 58 Y 2014 62 59 Y 2015 63 60 Y C G 2016 B4 B1 Y 2017 65 62 Y 2018 66 63 Y 2019 67 64 Y 2020 68 65 Y C G 2021 69 66 Y 2022 70 67 Y 2023 71 68 Y C G 2024 72 69 Y C G 2025 73 70 Y C G 2026 74 71 Y 2027 75 72 Y 2028 76 73 Y 2029 77 74 Y C G 2030 78 75 Y C G 2031 79 76 Y 2032 80 Y 2033 78 Y C G 2034 79 Y C G 2035 80 Y 9 Rebalancing Schedules On this sheet you define some standard portfolios comprised of the eleven chosen asset classes For consistency with some of our code
54. on Indexed slot are available on First or Second Details it is much easier to make the entries on those sheets Check that you have entered the cost bases correctly Setting them to zero will cause your entire withdrawal from the Personal Savings portfolio to be taxed at capital gains rates A zero cost basis in the Non Deductible IRA portfolio will cause those withdrawals to be fully taxable as regular income 31 Part III The Hidden Sheets There are several hidden sheets that allow you to customize certain features tweak our assumptions or generally experiment and fool around Some sheets get unhidden in the process of running the program others remain ever hidden until you unhide them by going to Format Sheets Unhide 1 Account Correlation RSP comes loaded with correlations for two market profiles If you create your own profiles you have to supply returns volatilities and correlations The latter can be tricky If the resulting matrix is not positive definite a technical mathematical property which basically means a legitimate correlation matrix then we have a situation where the simulation cannot proceed and you will get an error message How can a matrix of correlations fail to be legitimate Suppose you have asset classes X Y and Z Suppose X and Y have correlation 9 and Y and Z have correlation 9 Then the correlation between X and Z must be pretty high right If Tom looks a lot like his brother Joe and Jo
55. on the ClientBasics sheet accesses our filing system This sheet automatically is displayed when you open RSP and you can return to it at any time by clicking the Home button Clicking the Client Cases button presents you with the following choices New Case creates a new Client Case File using the input data from the Default Client Case File as a template Open Case loads an existing Client Case File into RSP Delete Case removes an existing Client Case File from RSP s database Save Case saves the inputs currently displayed in RSP replacing the data in the current Client Case File Save Case As saves the inputs currently displayed in RSP replacing the data in a new or different Client Case File Save As Default saves the inputs currently displayed in RSP replacing the data in the Default Client Case File thus creating a template to be used to create New cases After installing RSP you should begin by setting input values that you intend to use for all or most of your clients then doing a Save As Default When using RSP for a given client for the first time use New Case to create the first case for that client and use Save Case after you have entered the data for his baseline case For subsequent variations for that client it is most efficient to open that client s baseline case and do a Save Case As after getting down most of the client information so that you don t waste time with extra saves However some users like the
56. scription of everything in the program descriptions of the contents of hidden sheets and what they do for you and some appendices to discuss technical details Part II The Visible Sheets 1 Client Basics The Clean Up Workbook button is used in a case where an unexpected Excel error has appeared because you have gotten into some trouble If this occurs click End on the Excel error message then use the tabs to return to the Client Basics sheet Clicking Clean Up Workbook will re initialize the memory used by RSP s Excel add in without changing any of your input data The Client Cases button accesses our client case filing system as described above It allows you to Open existing cases Save your work do a Save Case As to save a case built off an existing case and to create a New Case derived from the current default case This button also allows you to do a Save As Default after you have created a template to be used to create new cases You can do your Saving at any time by returning to this sheet via the Home button or by responding to prompts that appear as you leave certain other pages We will mention here the important subject of navigation among the sheets When starting to enter a case you navigate to subsequent sheets by using the gt gt button in the upper left corner Navigating in that fashion ports entries and in some cases an entire schedule based on a single entry to later sheets Some entries in such schedules w
57. st the possibility of another unexpected downward market trend the output from these tests should help you open their eyes 29 Sensitivity to Unexpected Returns in First Year Year 2010 Dollars Age 15 96 10 5 96 Zero 5 96 10 95 15 96 of Loss in Loss in Loss in Growth in Growth in Growth in Growth in First Year 2010 Year 2010 Year 2010 Year 2010 Year 2010 Year 2010 Year 2010 Probability 75 0 98 0 99 0 99 0 99 1 00 1 00 1 00 Total Wealth 85 079 083 087 090 092 0 93 0 95 gt 250 000 100 0 48 0 53 0 59 0 65 0 69 0 72 0 76 Probability 75 0 93 0 95 0 96 0 97 0 98 0 98 0 99 Total Wealth 85 0 69 0 74 0 78 0 82 0 85 0 88 0 90 gt 500 000 100 0 41 0 47 0 52 0 58 0 63 0 67 0 71 Probability 75 0 81 0 85 0 89 0 91 0 93 0 95 0 96 Total Wealth 85 0 57 0 63 0 69 0 74 0 77 0 81 0 83 gt 750 000 100 0 36 0 41 0 46 0 51 0 56 0 61 0 65 Example If you experience 5 growth in year 2010 then there is 100 chance you will have more than 250 000 at age 75 Sensitivity to Unexpected Returns in All Years Year 2010 Dollars Age Retums Retums Retums No Change Retums Retums Retums of Get 5 96 Get 2 5 96 Get 1 25 96 to Annual Get 1 25 96 Get 2 5 96 Get 5 Ya First Reduction Reduction Reduction Returns Increase increase Increase Probability 75 0 71 0 95 0 98 1 00 1 00 1 00 1 00 Total Wealth 85 0 11 0 54 0 78 0 92 0 98 1 00 1 00 gt 250 000 100 0 01 0 16 0 41 0 70 0 89 0 98 1 00 Probability 75 0 41 0 82 0 93 0 98 1 00 1 00 1 00 Total Wealth
58. t a savings plan as part of a side analysis enter the agreed upon savings schedule in RSP and dispense with the other entries Select Accumulation Mode if that is what you wish to do Otherwise select Income Mode The entries requested on the next sheet will depend on that choice more of them are needed for the more detailed Income mode It is worth emphasizing if you select Income Mode the savings amounts entered have to come out of income Savings to the 401 K IRA Non Deductible IRA and Roth IRA will be reduced if the income entered is not sufficient Tax deferred savings will occur taxes will be paid spending as entered will occur and whatever is left is available for the personal savings to the taxed account as planned An exception if the Withdrawal Cap option is active spending may be reduced from the planned More on that option is in a later section 10 Accumulation Mode Note You can enter miscellaneous pre retirement expenses on any of the detailed schedules and those amounts will be withdrawn from the portfolio The Rebalancing Method button allows you to choose between two rebalancing methods the Global Taxed scheme and the Deferred Taxed scheme These schemes are described in detail in the Rebalancing Plan section The Automatic Payment of Taxes button gives you the option to turn off our tax model This may be desired if you are doing a test calculation In the past one had to enter the hidden Tax Table sheet
59. tax expense Modeling it in RSP however would be very difficult and inappropriate 20 Default lt lt gt gt Current Client Portfolio ES pe EI Allocations Dollar Values are in Year 2010 Dollars Paral Profit Sharing 401 K Deductible RA 00800000 Gay 2009 Year end Balances 300 000 750 000 250 000 50 000 10 000 Default Allocation of Current Portfolio to Accounts Se a a i ro Global Personal Non Deductible Actual Allocation of Current Portfolio to Accounts allocation Savings T Large Value 4 096 12 096 10 2 80 0 80 0 Large Growth 12 0 12 0 12 0 10 2 80 0 80 0 Small Value 12 0 12 0 12 0 10 2 80 0 80 0 Small Growth 12 0 y 10 2 80 0 80 0 International Large 12 0 1 10 296 80 096 80 096 International Small 12 096 1 10 296 80 096 80 096 Real Estate 0 0 80 0 80 0 Short Term Fixed Income 9 5 80 0 80 0 Intermediate Fixed Income 9 596 80 096 80 096 Municipals 9 596 80 0 80 0 Money Market 10 0 80 0 80 0 Overall Expected Performance for the Current Portfolio of 1 360 000 Performance Portfolio Return 4 84 7 72 7 72 7 72 7 72 7 08 Portfolio Volatility 4 3596 10 4696 10 4696 10 4696 10 46 9 03 One Year Value At Risk 10 903 90 021 30 007 6 001 1 200 137 066 Withdrawal Priority d 5 4 3 j 6 On this sheet
60. th spreadsheets in general and Excel in particular If you re not you ll need to learn the basics about Excel how to enter numbers in a cell use of scroll bars and the File menu The scroll bars can be used to navigate within a sheet and the lt lt gt gt or the tabs to get from sheet to sheet within a workbook Depending on your screen size you may have to use View Zoom on some sheets to view them more conveniently To print your results highlight the cells you want to print select File Print Area Set Print Area then FilePrint Most customizations will require you to use Tools Protection Unprotect Sheet to unlock the sheet being modified There is no need to re lock the sheet as the software automatically re locks sheets at key points in the processing Warning Never Protect the Workbook or use a password to Protect a Sheet That will prevent the software from manipulating the sheets Warning Do not Cut anything on RSP s sheets That may cause other cells to be misaligned Instead Copy and Paste Special onto a fresh sheet to create harmless copies that may be transformed arbitrarily 2 1 is odere ees am 11 12 13 14 15 16 17 18 19 20 What s Available in RSP Each portfolio account can use upto 11 asset classes with one standard set of default returns volatilities and correlations provided Multiple tax exempt asset classes municipal bonds and bond funds User may d
61. than make unrealistic assumptions about retirement income or a risky choice of a very aggressive portfolio It s probably better to face the music and cut spending 15 than to go into a 90 equity portfolio The accompanying figure shows what happens to the case we have been showing throughout the manual if certain remedies are chosen Remove the unrealistic assumption that the spouse might reach age 110 Postpone retirement for four additional years Reduce pre retirement and retirement spending each by 3 Reduce annual spending by an additional 3 each year after age 70 Use a slightly more aggressive B portfolio for the entire portfolio until the primary wage earner reaches age 70 reverting to the C G mix thereafter Try making these changes to the Base Case that comes loaded on your copy of RSP You will find something like the accompanying figures where there is now about a 95 chance of success As a bonus the median fortune has risen to 3 7M but that is not the main point XYZ Incorporated your firm name goes here Report far April 23 2070 Summary of Portfolio Value at Target Year 2060 Client Demo Your Wealth Distribution has these characteristics Case Remedy 5 6 chance of completely exhausting your portfolio 8595 chance of having more than 884 208 1599 chance of having less than 884 208 75 chance of having more than 1 504 694 2595 chance of having less than 1 604 694 5095 chance of having more than 3
62. the asset classes return and volatility and management fees paid To reduce the amount of information displayed on the Detailed Tabular sheet we have hidden the columns containing some of these fields Users who disagree with our choice of the statistics displayed are encouraged to use Tools Protection Unprotect Sheet and Format Columns Unhide to reveal any of these columns or Format Columns Hide to eliminate any from appearing on this sheet Note the example illustrated is your typical case with troubles looming and unsuspected The median looks fine but because spending is just below the after tax after inflation return there is a 4896 chance the portfolio is drained It represents a typical occurrence for clients who have been running deterministic analyses and led to believe that they are using a safe withdrawal rate Appendix F discusses how to address a troubled situation in general and applies those suggestions to this example The sensitivity studies are similar to those in earlier versions but a bit more elaborate for portfolio sensitivity For that we compare the current portfolio and rebalancing plan with six additional choices for the Global Rebalancing entire portfolio and the Taxed Rebalancing Personal Savings only from the A J menu Portfolio Investment Strategies Year 2010 Dollars Global Strategy Taxed Strategy Age of NOW NOW AE B F 1 FJ First Probability 75
63. the section on Detailed Inputs for more discussion of Required Minimum Distributions Age to End Analysis usually is set to some advanced age beyond which the client is unlikely to live perhaps life expectancy plus five years Alternatively it might represent a stage in a planning process like retirement age for a young couple The Cash Flow options are important to understand In a Monte Carlo program total income and therefore taxes are not known because the Taxed portfolio will generate random taxable dividends and distributions Thus the equation Income Spending Savings Taxes is somewhat indeterminate This is why we provide the Disposition of Excess Income toggle If you select the Savings radio button excess funds will be deposited in the Taxed Portfolio If you select the Spending radio button then we assume any excess will be spent The program has logic to handle shortfalls The priorities in order are taxes savings to tax deferred accounts spending and finally personal savings to the taxed account Taxes are paid and spending is achieved as input even if income is not sufficient to cover them by drawing from the client s taxed account or if necessary from the tax deferred accounts Many advisors prefer to treat pre retirement savings in a simpler less explicit fashion by specifying the amounts saved into each account each year without accounting for income spending and taxes Their approach is to work ou
64. tions in their financial plan These tables allow your client to make a quick comparison of the impact of changes to their current plan RSP provides two such studies Portfolio Sensitivity and Spending Sensitivity The Portfolio Sensitivity study requires seven candidate Rebalancing Plans all of which use either the Global Taxed scheme or the Deferred Taxed scheme Each of the seven plans will begin with their portfolio allocated as defined on the Current Portfolio page Specifying NOW corresponds to the rebalancing plan that is defined on the Rebalancing Plan sheet Specifying A J will rebalance to the allocation corresponding to that specific letter as defined on the Rebalancing Schedules sheet The Spending Sensitivity study will compare the impact of making an across the board percentage reduction increase in the client s current spending plan These percentages are applied to the schedule of Annual Expenses from the Detailed Inputs sheet without regard to any minimum or maximum living expenses that might limit your client The Complete Analysis will display shortened versions of both of these sensitivity studies The Target Balances Rebalancing Plans and Spending Adjustments are used for these shortened tables but the probabilities displayed will be for the Age to End Analysis Return Sensitivity Control Fixed Return in Year 1 Annual Adjustment First Year Annual Retum Fixed Returns Adjustments The previous two se
65. uickly with many of the entries estimated or guessed at Don t pass on an opportunity to provide advice and insight just because you don t have all the data 21 remaining portfolios are tapped in increasing priority order Withdrawals within each portfolio are made according to the Withdrawal Priority assigned to each account asset class This sheet displays a collection of current portfolio statistics to help users see the implications of their inputs The Global Allocation is calculated from the dollar amount in each account along with the individual allocations of each account onto the asset classes so that users will know how their entire portfolio is allocated among the eleven asset classes The Expected Portfolio Return and Volatility along with the current 1 year Value at Risk see Appendix D also are provided for each account and for the entire portfolio 8 Rebalancing Plan On this sheet you specify the rebalancing scheme you desire as the years progress using the array of balances defined on the Rebalancing Schedules sheet The Rebalancing Method buttons on the Client Basics sheet allow you to choose between two rebalancing methods the Global Taxed scheme and the Deferred Taxed scheme The Global Taxed scheme is comprised of two planned allocations for each year a Global Allocation that is the primary goal of the plan and a Taxed Allocation that is the secondary goal The program begins by temporarily assuming the Personal
66. ulate the consolidated return and volatility based on input returns volatilities and correlations then allow you to cut and paste the consolidated numbers to a slot on the AccountPerformance sheet 7 Configuration This worksheet is used to specify the contents of Client Case files and Account Performance files It is helpful to us to have it when we make changes to the code Users should not change anything on this sheet 33 Part IV Appendices Appendix A Modeling of Investment Returns Use of the normal distribution There has been much published lately on this subject by authors espousing a variety of viewpoints It is true that daily return data show symptoms of non normality with fat tails and peaked centers But monthly returns do not exhibit that kind of non normality For retirement planning daily and even monthly returns are not the issue We only need to model annual returns and they are quite Gaussian in appearance and pass the usual goodness of fit tests See The Economics of Financial Markets by Campbell Lo and MacKinlay Princeton University Press 1997 section 1 4 for a recent thorough discussion by competent scientists We also give here the results of fitting ten years 1991 through 2000 of monthly returns to the normal distribution for several familiar Vanguard funds We show the maximum absolute discrepancy between the cumulative distribution of the monthly returns and the fitted normal That quantity is the bas
67. uture produces a very diffuse flattened out distribution with some significant probability of having very little money and some significant probability of having a great deal of money and small amounts of probability everywhere in between And they ask how should we interpret this Our answer is that the 50 year plan should make sense It should before being adopted result in a very low probability of the bad event having very little money at the target year But then one should focus on an intermediate plan perhaps 25 years out Look at the successful 50 year plan and note the median of the probability distribution of wealth at the 25 year mark for that plan If that amount is present in the account at the actual 25 year mark then the plan is perhaps half way home It is analogous to military planning As the war begins the planners look at the long term objective and find short and intermediate term goals that will make necessary progress In World War Two we had to be able ultimately to invade Japan To do that we had to have bases from which to launch that attack To get to that point we first had to gain control of the sea lanes and then win the Battle of Midway The analogy goes further The initial planning is based on our best current information As the war proceeds unforeseen events may cause us to have to revise the plan No plan survives contact with the enemy So it is with financial planning The enemies are inflation poor
68. ver that even the Standard Asset Classes can be edited You can redefine or rename any or all of them and change the returns and volatilities as you wish But it is probably better practice to leave them exactly as supplied and create other market profiles which you will name 18 Account z E Management Market Profile and Performance Average Tax Annual Annual Annual Taxable Annual Withdrawal Managed Management Tax Account Types Return Volatility STCGD STCGD LT CGD Priority Fund Fee Efficiency Large Value 2 00 Y 2 0096 3 0 10 75 00 Large Growth 2 00 Y 2 00 3 N Small Value 12 0096 jl 2 00 2 00 3 N Small Growth 10 0096 d 2 0096 Y 2 0096 3 N International Large 8 0096 i 2 0096 Y 2 00 3 N International Small 12 0096 1 2 00 Y 2 00 3 N Real Estate 9 00 0 00 M 0 00 4 N Short Term Fixed Income i 0 00 Y 3 0096 2 N Intermediate Fixed Income 0 00 Y 4 00 2 N Municipals 4 00 N 0 00 2 N Money Market 0 00 Y 1 00 1 N SOURCE Standard xls DESCRIPTION Standard Asset Class Selection July 2003 Revision On the Account Performance sheet you see eleven asset classes with their default returns volatilities and also dividend and capital gains distribution CGD performance The latter are needed for Taxed accounts because dividends and CGDs generate taxes when they occur There is also a column for tax exempt dividends meant
69. we describe the current holdings according to the Asset Class breakdown chosen on Account Performance Put the dollar amounts in each account on the top row The allocation of each account onto the asset classes can be accomplished by selecting one of the symbols A through J as per definitions made or accepted as defaults on the Rebalancing Schedules sheet Those schedules can be edited and then the symbol will be modified to say A based instead of A to indicate it is a variant Note that cost basis is needed for the two accounts Personal Savings and Non Deductible IRA It is acommon mistake to forget to do this leaving a remnant number from some other case If it is set too low the model will be charging more tax than it should if it is set too high the model will not be charging enough tax Sometimes advisors need to prepare a preliminary rough plan and the client does not have the cost basis information handy In that case enter a reasonable guess based on recent market trends Finally you must indicate priorities for withdrawals These should be the six unique numbers 1 through 6 The number 1 is usually assigned to Personal Savings and the number 6 to Roth IRA the intermediate ones being not very significant Especially since Profit Sharing and 401 K roll into Deductible IRA upon retirement Withdrawals will be made first from the priority 1 portfolio until it has been depleted after which the 10 Rough planning can be done q
70. y Display Preference Probability Density Cumulative Density Final Wealth Percentiles Spending Percentiles Upper Middle Lower But first certain display controls are entered here Depending on how fast your machine is you can either leave the number of replications reps at 3000 or use fewer while still hunting and pecking for a workable plan The basic trend of a plan is seen using as few as 500 reps but the graph of the distribution will look ragged For final presentation use at least 3000 reps the graph will have a smoother more pleasant look Do your own 24 experiments to confirm that there is no point going beyond that not much chance that additional outliers will be generated and the graph will not change much A key output is the probability distribution of wealth at the target age and other years along the way Some users prefer to see this expressed as a cumulative distribution F and others prefer to look at the probability density f That is a matter of taste in either event the most important information certain percentiles picked off the distribution are shown also The key percentiles to be displayed on the output sheets are chosen here RSP will display 5 key percentiles for wealth distributions and 3 key percentiles for the spending achieved The default values are our suggestion as to the most informative We strongly recommend that you never show upper percentiles like 75 These are upsi
71. y required minimums or exact penalties Of course you can always withdraw more and may have to in order to support spending The user is then responsible for checking on any rules involving substantially equal withdrawals The withdrawals can be set to zero to use the program to examine trust or other investment issues where there are no required withdrawals As permitted there are no required withdrawals from the Roth IRA account unless all other funds are depleted Advanced Feature RSP 3 provides hidden cells to be used to enter any user defined data or formulas These hidden cells will be saved in the Client Case File along with the other input data The hidden area is named DetailedUserDefinedTable on Detailed Inputs Refer to the Advanced Feature section of First and Second Details for the instructions to access these hidden cells Bonus Advanced Feature RSP 3 provides hidden cells to be used to enter a schedule of Withdrawal Caps used to limit the use of your portfolio to satisfy your spending goals These hidden cells will be saved in the Client Case File along with the other input data The hidden area is named DetCaps on Detailed Inputs Refer to the Advanced Feature section of First and Second Details for the instructions to access these hidden cells The withdrawal cap may be useful when working with problem cases It is based on the premise that no more than a certain percentage of the total portfolio should be with

RSP User`s Manual - Wagner Math Finance

Contents

Download Pdf Manuals

Related Search

Related Contents