R$P 2K User`s Manual - Wagner Math Finance
Contents
1. Spending Sensitivity Adjustments fo Spending For the Portfolio Sensitivity test you select seven numbers to represent different levels of the proportion of the portfolio invested in equities It is useful to see that first for the complete range from 0 to 100 We will then show the probability that wealth is at least x at age y for each of the nine pairs of wealth levels x and ages y chosen This display is useful to make two points having even a small percentage of equities can make a difference and having much beyond 80 does not achieve a whole lot more than 80 does For a client who prefers to hold a low percent of equities and whose plan is not working increasing that percent is one of the remedies and this table can be helpful in making that point shows the portfolio sensitivity analysis of the Baxter s base case 19 Figure 13 Portfolio Sensitivity Analysis The Baxter s Base Case Name Reed amp Carol Baxter Date 3 6 00 Case Base lt HOME Portfolio investment Strategies Year 2000 Dollars Equity Fixed Income Ratio 0 700 15 85 30 70 50 50 70 30 85 15 100 0 Age of First Probability 80 0 04 0 23 0 44 0 66 0 77 Total Wealth 84 0 01 0 09 0 28 0 53 0 69 gt 250 000 88 0 00 0 04 0 19 0 43 0 62 Probability 80 0 01 0 10 0 28 0 53 0 68 Total Wealth 84 0 00 0 04 0 19 0 42 0 61 gt 500 000 88 0 00 0 02 0 12 0 36 0 56 Probability 80 0 00 0 04 0 19 0 41 0 59 Total Wealth 84 0 00 0 022. only to Social Security Income If you believe that Social Security will not keep pace with inflation set this value lower than the Annual Inflation Rate 26 Detailed Inputs Withdrawal Cap Clicking the Switch button toggles between activating ON and deactivating OFF the withdrawal cap In any year this is the maximum percentage of the current portfolio total that the user is allowed to draw from the portfolio for after tax expenses if it is to be depended on for expenses to the end of the analysis period Activating this feature will fill each cell with a default schedule of spending caps based on the guidelines discussed by Jonathan Clements For more information see section 8 of this document Equity Proportion In any year this is the proportion of your investment portfolio that you want in stocks If the Rebalance option was selected this will be used to shift your portfolio gradually into more or less conservative investments If the Buy amp Hold option was selected this will be used to make new investments to your portfolio The Fixed Income Proportion is displayed for information only Tax Deferred Distributions In any year this is the percentage of your tax deferred portfolio not including your Roth IRA you wish to withdraw In general one should use the IRS required minimum distribution schedule but you may choose to experiment with a more aggressive schedule Clicking the Switch button above this col
3. 0 0 0 0 64 0 36 0 211 444 0 0 0 0 0 63 0 37 0 217 448 0 0 0 0 0 62 0 38 0 223 623 0 0 0 0 0 61 0 39 0 186 168 0 0 0 0 0 60 0 40 0 191 455 0 0 0 0 0 60 0 40 0 196 891 Eureka The latest retirement plan assures the Baxters a ninety percent chance of having a non negative portfolio value at the end of the analysis period see Figure 11 17 Figure 11 Projected Wealth Remedies Completed Name Reed amp Caro Baxter Date 3 1 00 Case remedies completed Projected Wealth Nominal Dollars 5 000 000 4 500 000 4 000 000 3 500 000 3 000 000 2 500 000 2 000 000 SathPercentil 1 500 000 Af th Pete a A a S a 8 BK a sar Percemite 1 000 000 p ENa Percentile 500 000 Portfolio Sensitivity Analysis The Sensitivity Inputs sheet is used to launch two sensitivity studies that you might find instructional for your clients For either you first select the matrix of cases you d like to include three target ages for the First Person and three wealth levels in current dollars That will create a set of three times three equals nine cases see Figure 12 18 Figure 12 SensitivityInputs worksheet Name Reed amp Carol Baxter Date 3 6 00 Case Sase zz Client Cases Sensitivity Analysis Controls Target Ages 250 000 Target Balances 750 000 Portfolio Sensitivity Equity Percentages 100
4. 0 0 60 0 0 194 408 9 5 100 0 0 5 0 0 0 60 0 0 199 929 8 9 100 0 11 2 0 0 0 60 0 0 0 205 606 84 100 0 1 9 0 0 0 60 0 0 0 211 444 7 9 100 0 12 7 0 0 0 60 0 0 0 217 448 74 100 0 3 5 0 0 0 60 0 0 0 223 623 6 9 100 0 14 5 0 0 0 60 0 0 0 186 168 74 100 0 325 96 amp SS oo 68 68 0 C6 0 0 0 60 0 0 191 455 6 9 100 0 14 5 0 0 0 60 0 0 196 891 6 5 100 0 15 4 HEF i de e d de d Ae e d e On each of the three detailed worksheets FirstDetails SecondDetails and DetailedInputs a user may fine tune the Income and Expense schedule generated by the Client Description inputs by editing the cells on the worksheet we will edit the DetailedInputs worksheet as an example later in Section 5 of this document For example the user may want to edit the pre retirement entries in the Indexed Income column to take into account expected salary increases that exceed the rate of inflation After making such edits a user must be careful when navigating the worksheets back and forth For example if a user were to navigate back after editing one of the schedules say on the FirstDetails worksheet and then clicked the Next button to navigate forward he would want to answer No to the query Overwrite detailed sheet for First Client Otherwise the program would use the inputs on the ClientDescription worksheet to generate the First Person Income
5. about the wealth level indicated on the x axis There are always five bars between each tick mark and label on the x axis So if the labels begin with 1M then each bar represents an increment of 1M divided by five or 200K The first bar shows how many cases ended up by having wealth between 0 and 200K The next bar shows the percent of cases that ended up with wealth between 200K and 400K And so on The chart scale is automated to produce labels that are nearly round numbers to facilitate reading We also choose a scale so that the highest 5 to 10 of the cases in the final year simulated are accumulated or rolled up into the final bar on the right The reason for doing this is that the density tends to have a lognormal shape which often means a very long scraggly tail to the right if we included a bar for every value that occurs it would be impossible to see clearly what s going on in the lower left hand part of the distribution which is the part we want to focus attention on Some users prefer to look at and to show their clients cumulative distributions It s a matter of taste the table to the right of the density shows the key information off the cumulative in either event For a cumulative the y value shows the probability that final wealth is less than or equal to the x value rather than the probability that the value is in the neighborhood of the x value For a retiree a successful case will have a y value somewhat sm
6. if you continued at that rate you would remove all of it during your expected lifetime you and your spouse s joint expected lifetime or some designated shorter period The joint expected lifetime method is the most generous and those numbers are given by default in the Distributions column You can change the formula to substitute a beneficiary other than a spouse You can actually put any numbers you like in R P 2000 s Distribution schedule the onus is on the user to use numbers the IRS would approve of However the fact that distributions must start at age 70 is a very non trivial effect and should be addressed head on You are basically taking care of a tax obligation that has been looming and spreading those payments over a twenty year period and having the program address this realistically is a very important feature of the program Consolidating Accounts R P is designed to accept return and volatility data for each of four client portfolio types Equity and Fixed Income Taxed and Tax deferred The Portfolio Consolidation sheet is provided as a worksheet or calculator to assist in rolling up information about multiple investments that comprise an individual s four major portfolio components The worksheet gives slots for five components the amounts returns volatilities and correlation data see the next subsection When you press the Consolidate button the calculation is done and copied to the clipboard so you can Paste to
7. return of each portfolio account Sensible default values are supplied but the user should choose values appropriate for the client s particular portfolio accounts Annual Volatility the annual standard deviation of each portfolio account Sensible default values are supplied but the user should choose values appropriate for the client s particular portfolio accounts e Fixed Annual Dividend fixed percentage paid as a dividend annually for each portfolio account This amount will be paid even in years where the account experiences a loss The dividend and CGD see below amounts will be subtracted from each year s actual return to calculate the portfolio growth for each replication e Fixed Annual CGD fixed percentage paid as a capital gains distribution annually for each portfolio account This amount will be paid even in years where the account experiences a loss The dividend and CGD amounts will be subtracted from each year s actual return to calculate the portfolio growth for each replication e XXXX Year end Cost Basis The cost basis in current dollars for each of the 3 personal savings accounts equity fixed income and tax exempt fixed income at the end of the year prior to the start of this analysis period 8 Beyond the Basics The Withdrawal Cap The withdrawal cap column on the Detailed Inputs sheet makes available a tool to help work with problem cases It is based on the premise that no more than a cert
8. to find problem solutions Be sure to edit the cell reference and use its absolute address before dragging If you press Format Sheet Unhide you will see a list of hidden sheets you can Unhide The most recently calculated Detailed Charts can be resurrected this way but it will not reflect changes you have made to any input data You can also Unhide the data that determine our tax model this way You will see tables that contain the thresholds and marginal rates for the four modes of filing in the same order they appear on Client Description You can use any one of these as a dummy for experimenting with different tax bases One R P user reports using this capability to examine trust issues especially trusts set up as part of divorce settlements No doubt it could be used for some non US cases 31
9. 0 12 0 34 0 55 gt 750 000 88 0 00 0 01 0 08 0 29 0 51 Example if you invest your portfolio in 70 Equities and 30 Fixed income then there is a 77 chance you will have more than 250 000 at age 80 Spending Sensitivity Analysis The Spending Sensitivity Analysis shows how results might vary with small or large changes in spending For each of the seven factors chosen we vary the entire schedule of spending as it appears on the DetailedInputs sheet Then we show results for the same matrix of nine cases shows the spending sensitivity analysis of the Baxter s base case You might run this first in a problem case to see how spending alone could be altered to solve a problem It s more likely that a combination of measures will be more palatable but it is instructional to see this one alone 20 Figure 14 Spending Sensitivity Analysis The Baxter s Base Case Name Reed amp Caro Baxter Date 3 6 00 Case Base z HOME Spending Pian Strategies Year 2000 Doilars 15 10 5 Original 5 10 15 Spending Pian Agjustment Decrease Decrease Decrease Pian increase increase increase Age of First Probabi 80 0 87 0 81 0 72 0 63 0 54 Total We 84 0 79 0 71 0 61 0 53 0 43 gt 250 0 88 0 73 0 63 0 54 0 43 0 35 Probabi 80 0 78 0 69 0 61 0 53 0 44 Total We 84 0 71 0 62 0 53 0 43 0 35 gt 500 0 88 0 65 0 56 0 47 0 39 0 30 Probabili 80 0 67 0 59 0 51 0 42 0 34 Total Wea 84 0 62 0 53 0 44 0 37 0 29 gt 750 000 8
10. 000 has completed saving your new case Click the Next button and note that partial remedies is now displayed as the current case When control is returned to the PortfolioCorrelation worksheet click Next to recalculate Projected Spending When the simulation is complete the Status bar will display the Ready message and the DetailedCharts worksheet will be displayed see Figure 9 Figure 9 Projected Wealth Partial Remedies Name Reed amp Carol Baxter Date 3 1 00 Case partial remedies Projected Wealth Nominal Dollars 3 000 000 2 500 000 2 000 000 7 500 000 2 N a 2 f a 8 K 1 000 000 4 500 000 View Projected Spending and Final Wealth Getting Closer As you can see from the DetailedCharts worksheet under the modified scenario the Baxters are closer to achieving their new spending goals during retirement The thirtieth percentile curve on the Projected Spending chart now allows for desired spending throughout the analysis period There is still a twenty percent chance however that the value of their portfolio will be zero by the year 2027 16 Change Mix of Equities amp Recalculate Our next strategy recommendation is to edit the mix of equities Navigate to the DetailedInputs worksheet and scroll until the Equity Proportion column is displayed Edit the cells in this column to look like those in Navigate to the PortfolioCorrelation worksheet and click the Next button
11. 8 0 58 0 49 0 40 0 32 0 25 Example Ifyou increase your spending plan by 10 then there is a 63 chance you will have more than 250 000 at age 80 6 Client Case Management Scenarios are identified by both a client name and a case name Each R P 2000 workbook can manage up to 250 scenarios This can be any combination of clients and cases For example the user may have client and 250 cases for that client a thorough analysis of their situation to say the least or he may look at five cases for each of his fifty clients If you need to manage more scenarios select Excel s File Save As to save a copy of the R P 2000 workbook under a new name The Client Cases Button The Client Case button appears on several worksheets AdvisorPreferences DetailedInputs PortfolioCorrelation and SensitivityInputs These are the worksheets where the user is most likely to want to save a scenario You may wonder why the Client Cases button does not appear on the ClientDescription worksheet Recall that the inputs on this sheet are expanded on the detailed worksheets that immediately follow It is often the case that the user may want to fine tune the detailed worksheets and edit them slightly Thus we chose to postpone giving the user the opportunity to save the scenario data until the DetailedInputs worksheet 21 The Client Case Options Dialog R P 2000 manages its scenarios much the same way most applications manage their files
12. It allows the user to create a new scenario open an existing scenario save the current scenario rename a scenario and delete a scenario The difference is that the scenarios R P 2000 presents to the user are all stored in the same current Excel workbook file R P2000 xls You don t need to have a separate workbook for each scenario To view the scenarios stored in the current R P 2000 workbook go to the AdvisorPreferences worksheet and click on the Client Cases button This will bring up the Client Case Options dialog see Figure 15 Figure 15 Client Case Options dialog Client Case Options 21x Reed amp Carol Baxter base gt New Delete Save Save As Cancel To view the list of client cases click on the drop down button of the Client Case Options dialog To open a scenario select a client case from this list and click Open The status bar will display Ready and the Client Case Options dialog will close once the selected scenario has been loaded The ClientDescription worksheet will be displayed with the name of the just loaded client and case at the top To delete a scenario select the case from the list and click Delete Click Yes to the query Permanently delete lt client case name gt The deleted client case name will be deleted from the list The Client Case Options dialog remains visible ready for the next command To save the current scenario click Save This may take a while since in a
13. R P 2000 User s Manual Daniel H Wagner Associates Inc R P was developed by a team of mathematicians at Wagner Associates in collaboration with Independence Advisors of Berwyn PA The Wagner team has substantial experience in developing software for financial analysis Independence Advisors have supplied the expertise of Wealth Management practitioners issues to address presenting results to clients and reasonable values for the return and volatility of stock and bond portfolios R P 2000 The Retirement Spending Planner 1998 2000 Daniel H Wagner Associates Inc Version 2 03 40 Lloyd Avenue Suite 200 RSPsupport pa wagner com Malvern PA 19355 http www wagner com 610 644 3400 TABLE OF CONTENTS 1 Introduction R P 2000 is a tool for retirement planning to estimate whether a portfolio is sufficient to sustain desired spending levels during retirement The unique feature of R P 2000 is that it addresses statistical uncertainties in portfolio performance due to market variability and the volatilities that different types of investments have 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 what spending levels are desired and some very simple inputs to characterize tax liabilities The intent in using this program is to help devise a plan for when retirement is possible wi
14. ain percentage of the total portfolio should be withdrawn 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 switch the toggle 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 28 R P 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 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 Distributions Starting at age 70 you must begin withdrawals from the tax deferred portfolio so that
15. aller than 0 15 for the lowest bar For then there is greater than 85 chance that final wealth is above that minimal amount For the accumulation phase you want to see a value less than 0 25 out at the target retirement wealth For then there is greater than 75 chance the target wealth for retirement will be achieved This description is written using the recommended 75 and 85 thresholds for accumulators and retirees respectively Some users will use other values Whichever type of chart is shown the key percentiles are tabled The default values of these key percentiles can be changed back on the Advisor Preferences sheet This allows the user to focus on a different part of the distribution The nominal dollars presentation is useful for saving and tracking how the plan is working out as the years go by The dollars shown will be real dollars as time goes by if the value used for inflation is near accurate The inflation adjusted Year XX XX Dollars presentation is good for looking at the out years to see if those amounts make intuitive sense 13 There are up and down triangles that can be clicked on the Final Wealth Density chart so that earlier years distributions can be viewed one at a time or you can type in any year in the formula bar and hit Enter Projected Spending The Projected Spending chart shows the distribution of spending that might occur possibly at variance with what was desired For each year within the com
16. and Expense schedule and your edits would be lost Clicking the Fix button at the top of a column will reset that column s cells to their default value The Switch buttons apply to the Withdrawal Cap and the Tax Deferred Distribution columns and are explained in detail in Section 8 of this document Do not edit the values in the Equity Proportion column if you wish to continue with this example Again scroll the worksheet to view the inputs Click Next to display the PortfolioDescription worksheet see Figure 5 The inputs on this worksheet are explained in detail in Section 7 of this document Briefly this worksheet contains the current portfolio amounts in each type of investment broken down by sheltered and non sheltered accounts The Baxter s total portfolio value is 720 000 The PortfolioDescription worksheet also contains statistical estimates of the annual return and risk volatility for each account These estimates are crucial to the analysis so the wealth manager should ensure values appropriate for the client s portfolio are used Click Next again to navigate to the PortfolioCorrelation worksheet The Portfolio Correlation Matrix is explained in detail in Section 8 of this document For now it suffices to say that if you are unsure of the correct values to use it is standard practice to use the default value of 1 0 in each cell Figure 5 PortfolioDescription worksheet Base Case Name Reed amp Carol Baxter Date 3 1 00 C
17. are not client dependent Changing data on this worksheet will affect all future calculations made for any client case Figure 2 AdvisorPreferences worksheet Clean Up License Client gt gt Workbook info Cases Cash Flow Controls Disposition of Excess Income Personal Savings Spending Output Display Controls Density Display Preference Oprobability Density Cumulative Density Final Wealth Percentiles Retirement Spending Percentiles Upper Middle Lower Simulation Controis Number of Replications 1000 Random Number Seed 13531537 Change the value of Number of Replications to 3000 to match the higher precision used for our example Click the Next button on the AdvisorPreferences worksheet to bring you back to the ClientDescription worksheet Click the Next button again Click Yes to the query Overwrite detailed sheet for First Client This will display the FirstDetails worksheet a schedule of the First Person s Income and Expense Profile in current dollars Notice that the data is generated solely from the ClientDescription worksheet Scroll the worksheet to view the inputs Click the Next button and again answer Yes to the query Overwrite detailed sheet for Second Client This will display the SecondDetails worksheet a schedule of the Second Person s Income and Expense Profile in current dollars This data also is generated solely from the ClientDescription worksheet After vie
18. ase base Consolidate lt Accounts gt Client Portfolio Description Dollar Values are in Year 2000 Dollars Rebalancing Model OBuy amp Hold Rebalance Non sheltered Accounts 7999 Average Annual Fixed Fixed 1999 Account Description Year end Annual Annual Annual Year end Cost Balance Return Volatility Dividend CGD Basis Personal Savings Equity 75 000 12 0 15 0 2 0 2 0 70 000 Personal Savings Fixed Income 50 000 5 09 6 0 2 0 2 0 45 000 Tax exempt 100 000 5 0 5 5 2 0 2 0 80 000 Fixed Income Personal Savings Sheltered Accounts 7999 Average Annual Fixed Fixed Account Description Year end Annual Annual Annual Balance Return OMY Dividend CGD Retirement 1 Profit Sharing Equity 20 000 1 5 14 0 2 0 2 0 Retirement 1 Profit Sharing Fixed 100 000 6 0 5 5 2 0 2 0 Retirement 2 401K Equit 100 000 0 5 11 0 2 0 2 0 Retirement 2 401K 25 000 5 5 6 0 2 0 2 0 Retirement 3 IRA i 120 000 2 0 14 0 2 0 2 0 Retirement 3 IRA 80 000 6 3 5 0 2 0 2 0 Retirement 4 Roth IRA i 25 000 1 2 12 0 2 0 Retirement 4 Roth IRA 25 000 48 5 0 2 0 10 Calculate Projected Spending and Wealth Click Next and answer No to the query Save Case before proceeding as we have not made any changes to the case This will initiate the Monte Carlo simulation making draws for returns from th
19. base tailed Spending and Portfolio Profile Fix Fix Fix Fix Switch Switch 401 K IRA Roth IRA Annual Avg Life Withdrawal Minimum Contribution Contribution Contribution Equity Fixed Income Expenses Remaining s Cap Distributions Nominal Nominal Nominal Proportion z Proportion j Nominal individual OFF individual 10 000 0 0 60 0 40 0 90 000 25 9 100 0 0 0 10 284 0 0 60 0 40 0 92 556 25 0 100 0 0 0 10 576 0 0 60 0 40 0 95 184 24 2 100 0 0 0 10 876 0 0 60 0 40 0 97 887 23 3 100 0 0 0 0 0 0 60 0 40 0 100 666 22 5 100 0 0 0 0 0 0 60 0 40 0 103 525 21 6 100 0 0 0 0 0 0 60 0 40 0 124 208 20 8 100 0 0 0 0 0 0 60 0 40 0 127 735 20 0 100 0 0 0 0 0 0 60 0 40 0 131 362 9 2 100 0 0 0 0 0 0 60 0 40 0 135 093 84 100 0 0 0 0 0 0 60 0 40 0 138 929 7 6 100 0 0 0 0 0 0 60 0 40 0 142 874 6 8 100 0 0 0 0 0 0 60 0 40 0 146 931 6 0 100 0 6 3 0 0 0 60 0 40 0 151 103 5 3 100 0 6 5 0 0 0 60 0 40 0 155 393 46 100 0 6 8 0 0 0 60 0 40 0 159 806 3 9 100 0 7 2 0 0 0 60 0 0 0 164 344 3 2 100 0 7 6 0 0 0 60 0 0 169 010 2 5 100 0 8 0 0 0 0 60 0 0 173 810 1 9 100 0 8 4 0 0 0 60 0 0 178 745 1 2 100 0 8 9 0 0 0 60 0 0 183 821 0 6 100 0 9 4 0 0 0 60 0 0 189 040 0 0 100 0 10 0 0
20. college The formulas on those sheets are all simple links to cells on the ClientDescription worksheet so adding or subtracting values becomes a simple formula Furthermore these worksheets work entirely in current dollars where the DetailedInputs worksheet incorporates inflation in its formulas Suppose you want to edit the Equity Proportion on the Detailed Inputs sheet to have it decline linearly from 70 to 50 over a ten year period Just click on the first cell to be decremented and replace the contents of the formula bar with click on previous cell 02 and drag that expression down another nine cells Suppose you want to adjust the spending that appears on Detailed Inputs to be geometrically decreasing by a factor of 1 each year Unprotect the Detailed Inputs sheet Click on the earliest Spending number that you want to decrease by 1 Suppose that happens to be in year 2001 In the formula bar append to the expression there 99 2001 2000 But don t type 2001 click on the year cell where 2001 appears Now when you drag that formula down the exponent will increase by 1 each time and you ve got the geometric ratio correctly inserted You have used the year column as a dummy to create an arithmetic sequence In the previous example you might want to enter the factor of 0 99 in a blank cell and click on it instead of typing it Then it s available for editing and you can do experiments with that reduction factor
21. ddition to its own data storage tasks R P 2000 is also saving the Excel workbook this data lives in Again the Status bar displays Ready when the task is complete The same worksheet you initiated the save from is displayed Please be aware that the Save Case button does not save the output charts and tables for the scenario you are saving When opening the scenario again the user must recompute the output charts and tables 22 To create a new scenario click New This will bring up the Naming New Client Case dialog Enter the new client name and click OK Next enter the new case name and click OK R P 2000 will display the ClientDescription worksheet with the yellow shaded cells cleared Note that the AdvisorPreferences worksheet cells are unchanged as they are not considered client specific data To save a case under a new name click Save As This will bring up the Saving Client Case As dialog Enter the new client name and click OK Next enter the new case name and click OK As in the Save function this will take a little while and the same worksheet you initiated the save as from will be displayed once the task is completed with the new name of the client and case at the top The Cancel button on the Client Case Options dialog merely brings down the dialog Also in any of the data management functions when you are queried for information you always have the opportunity to cancel 7 A Closer Look at the Inputs In you are un
22. e distributions for returns that were entered in the PortfolioDescription worksheet and summarizing the results statistically When the simulation is complete the Status bar will display the Ready message and the DetailedCharts worksheet will be displayed see amp Figure 7 Figure 6 Projected Wealth Base Case Name Reed amp Caro Saxter Date 3 1 00 Case base Projected Wealth Nominal Dollars 2 000 000 1 800 000 1 600 000 1 400 000 1 200 000 1 000 000 800 000 o A a S q 8 f 5 thPercentile 600 000 10 thPercentile 15 thPercentile 400 000 25 th Percentile i 50 th Percentile 200 000 11 Figure 7 Projected Spending Base Case Projected Retirement Spending Nominal Doilars 250 000 200 000 150 000 100 000 50 000 3 gt 2 O d fez 3 y S 3 k x 0 View Detailed Charts The Disappointing News As you can see from the DetailedCharts worksheet under the current scenario the Baxters will not be able to achieve their desired spending goals during retirement with very high probability As the pink curve on the Projected Spending chart indicates there is a thirty percent chance that the Baxter s would maintain their desired spending until the year 2026 but then would be forced to significantly cut their spending and live only on their Social Security income from the year 2027 on The red curve indicate
23. et before navigating to the detailed worksheets for editing e Pre Retirement Retirement Income The amount earned each year before after retirement age If the source of income is influenced by inflation the amount should be entered in the Pre Retirement Retirement Indexed Income cell When creating the income schedule found on the DetailedInputs worksheet R P will internally inflate the indexed income to nominal dollars for each year The inflation rate used is the Annual Inflation Rate also entered on the ClientDescription worksheet see below If the source of income is not influenced by inflation the amount should be entered in the Pre Retirement Retirement Non indexed Income cell Typical sources of such income are certain types of pensions and annuities Non indexed income will remain in current dollars for each year of the DetailedInputs schedule e Social Security Income The amount of social security to be drawn depending on age and amounts earned during working career Write the Social Security Administration for accurate information on benefits earned The amount entered should be in current dollars and will be inflated according to the Inflation Rate for Social Security also entered on the ClientDescription worksheet see below e Profit Sharing This is yearly income beyond Pre Retirement income and is automatically contributed to a separate tax deferred account see the Portfolio Description inputs below The amo
24. hen the box is not checked client description inputs are required for one person only and SecondDetails worksheet is not generated The following ages are used to generate the Income and Expense schedules used by the simulation if the Two person box is checked values for each person are required Age Now Schedules will be generated for years starting at this age Retirement Age Pre Retirement Income Contributions Pre Retirement Expenses and Pre Retirement Deductions apply to the years prior to this age Retirement Income Retirement Spending and Retirement Deductions apply to the years beginning with this age Social Security Start at Age Social Security benefits begin to be drawn at this age Most users will select 62 or 65 Younger users may have to wait beyond those ages Currently age 70 is the maximum Age to End Analysis Schedule will be generated for years from Age Now to this age 24 The following income category inputs apply to both the First Person and Second Person Income Categories For each input it is assumed that the amount entered applies to each and every year of the inflation adjusted income schedules To model a varying schedule for example one that takes into account pay raises due to promotions the user should edit the inflation adjusted income schedules produced on the FirstDetails and SecondDetails worksheets Note It is best to enter all the inputs required on the ClientDescription workshe
25. indexed income 120 000 Personal Savings Contribution 8 500 Retirement Age 64 Pre Retirement Non indexed income 401 K Contribution 10 000 Social Security Start at Age 64 Profit Sharing Contribution 10 000 JRA Contribution Age fo End Analysis 85 Retirement Indexed Income Roth IRA Contribution Retirement Non indexed Income Two person Pre Retirement Expenses 87 500 Social Security Income 19 000 Two person Retirement Expenses 105 000 Second Person Description Second Person income Categories Second Person Expense Categories Sa Age Now 56 Pre Retirement indexed income 25 000 Personal Savings Contribution Retirement Age 62 Pre Retirement Non indexed Income 401 K Contribution Social Security Start at Age 62 Profit Sharing Contribution IRA Contribution Age fo End Analysis 86 Retirement indexed Income Roth IRA Contribution Retirement Non indexed Income One person Pre Retirement Expenses 85 000 Social Security Income 12 000 One person Retirement Expenses 85 000 15 Navigate to the PortfolioCorrelation worksheet and click Client Cases Next click the Save As button on the Client Case Options dialog This will display the Saving Client Case As dialog with the client Reed amp Carol Baxter Since we are not changing clients click OK Enter partial remedies for the new client case and click OK The Status Bar will display Ready when R P 2
26. m of indexed and non indexed income social security dividends and capital gains distributions CGDs from the Taxed portfolio any amount withdrawn from the Taxed portfolio to support spending minus its cost basis and any withdrawal from the Tax deferred portfolio Roughly speaking it is the taxable income The other exception is Median Spending defined as all spending except what is spent to pay taxes Note that these numbers will not exactly balance in ways you would like them to because you are looking at medians The median number in each category does not correspond to the numbers that occur in any one replication They should balance approximately however 30 Miscellaneous Comments and Tips If you are not an experienced Excel or other spreadsheet user you should become one There are lots of Excel skills that can be very useful and time saving in running R P You can populate some of the R P fields for a client s case by linking to your own Excel based client information sheets You can add a sheet that you have customized to summarize selected parts of R P output for more compact presentation to your clients To do some of these things you may have to Unprotect the sheet by going to the Tools gt Protection menu no password is required If you are intimidated by long Excel formulas try to use the FirstDetails and SecondDetails worksheets to model your client s unusual events like selling a home or sending a dependent to
27. narios the final wealth will NOT be sufficient to last at least 5 more years If you then examine the lower percentile results you may be surprised to see that there is a significant chance that the final wealth will not be sufficient to last even 1 more year 23 Retirement Spending Percentiles used for the Projected Retirement Spending chart The default values of 30 20 and 10 are most insightful For example suppose it is desired to spend 100 000 every year If the value of the 10 curve at age 85 is 75 000 that means that in 10 of the scenarios annual spending was less than or equal to 75 000 at that age The user should be alert to a case where the 10 curve drops drastically as the years go by That means that initial spending is too high and there will be very difficult adjustments to lower spending later in his life Number of Replications for rapid response use 500 For a case of major interest use 1000 or even 3000 Numbers larger than that do not give any particular extra insight This value is also commonly known as Number of Repetitions or Number of Samples Client Description Year at Start of Analysis The calendar year when First Person reaches Age Now see below Two person When this box is checked the ClientDescription worksheet requires inputs for two people First Person Second Person assumed to be partners Both the FirstDetails and SecondDetails worksheets are generated from these inputs W
28. nner gt RSP 2000 from your Start menu Your first R P 2000 session will require you to activate your license before the program can calculate results Detailed instructions for doing so may be found in your printed or e mailed installation instructions This activation must be performed for each computer that you have licensed to run R P 2000 Information specific to the licensed computer is used to activate the license so the license activation will only work on that computer Instructions for transferring a license to another computer may be found in the ReadMe doc file The remainder of this section introduces you to R P 2000 by guiding you through a typical scenario Open Base Case Click Client Cases on the AdvisorPreferences worksheet Select Reed amp Carol Baxter base from the drop down list on the Client Case Options dialog Click Open Once the case has been loaded the Status Bar will display Ready and the ClientDescription worksheet will be displayed see Figure 1 Figure 1 ClientDescription worksheet Base Case z gt gt Name Reed amp Caro Baxter 3 1 00 Case base NOTE Dollar Values are in Year 2000 Dollars Year at Start of Analysis 2000 Two person First Person Description First Person income Categories First Person Expense Categories Age Now 58 Pre Retirement indexed Income 120 000 Personal Savings Contribution Retirement Age 62 Pre Retirement Non indexed Income 401 K Contribution Social Security Sta
29. pass of the study we show selected percentiles of the spending each year Remember we just ran 3000 repetitions of the client s case For each year in the case we look at those 3000 outcomes order them from low to high and pick off the numbers that fall at various levels these are the percentiles The recommended percentiles to focus on are 30 20 and 10 Also shown in green is the desired spending In the most favorable of cases you ll see nothing but a red line That occurs when the lowest percentile of interest the 10 coincided with the desired spending so it covers up the green line For an almost satisfactory case you ll see the 10 percentile curve drop from the desired level way out toward the final years In a quite unsatisfactory case you ll see all three curves falling precipitously to zero perhaps early on in the clients lifetimes The previous display Final Wealth Density shows if money is likely to run out if it is likely to run out then the Projected Spending chart shows about when that will first occur Projected Wealth For each year within the compass of the study we show selected percentiles of total wealth Remember we just ran 3000 repetitions of the client s case For each year in the case we look at those 3000 outcomes order them from low to high and pick off the numbers that fall at various levels these are the percentiles The recommended percentiles to focus on are 50 25 15 10 and 5 The 50
30. percentile is the median of the distribution Half the cases were better than this the other half worse For a plan that is working the 50 percentile should be flat or increasing as the years go by if it is declining then the plan is seriously flawed The 25 percentile is the lower quartile 25 were worse and 75 better It is the curve to pay most attention to for the accumulation phase For the accumulation phase it should be rising gently or more so The middle curve is the 15 percentile 15 were worse 85 better It is the curve to watch for the retirement phase It can be declining but should always remain positive More conservative users may prefer to monitor the 10 or even the 5 percentile and require them to remain positive Many clients will not be able to afford that level of caution Guide them to focus on the 15 Adjust the plan until the 15 percentile is behaving as desired never declining to zero 14 You can select other percentiles to be presented back on the Advisor Preferences sheet Higher percentiles like 75 80 and 90 will show the upside potential They might be instructional to look at but not recommended for routinely showing to your client They can be distracting and deflect attention from a potential problem You don t want your client musing on the nice possibility that there is a 25 chance of having over 2 000 000 when there is a 35 chance of having less than 25 000 You can also look at the 1 pe
31. rcentile but very few of your clients can afford to be that cautious Another reason to be content with 85 confidence is the realization that in those 15 cases where things might go poorly you and your client would redo the plan and take steps however unpleasant to right the course You won t just mechanically press on and watch the wealth trickle away And 85 of the time we estimate the plan will work and no such remedies will be needed 5 Digging Deeper More on the Baxters Now that you have an understanding of the charts lets see if a few changes to the Baxter s current retirement plan will increase their chances of being solvent at their chosen target ages Edit the ClientDescription Worksheet Click the back arrow button until you ve navigated back to the ClientDescription worksheet Increase the Retirement Age under the First Person Description from 62 to 64 Next we go to the First Person Expense Categories and increase the Personal Savings Contribution from 6 000 to 8 500 Lastly we want to decrease the Two person Pre Retirement Expenses from 90 000 to 87 500 see Figure 8 Figure 8 ClientDescription worksheet Partial Remedies z gt Name Reed amp Carol Baxter Date 3 1 00 Case partial remedies NOTE Dollar Values are in Year Dollars Year af Start of Analysis F Two person First Person Description First Person Income Categories First Person Expense Categories Age Now 58 Pre Retirement
32. roke And if they re sure they ll live to be 85 then they have a fair chance to live to be 90 A prudent analysis will look for 90 or 95 assurance and consider a target lifetime several years beyond the expected The wealth manager should review this analysis with their client annually 2 Requirements for Running R P 2000 The program requires a PC with the Microsoft Windows 95 Windows NT or Windows 98 operating system and runs under Microsoft Excel 7 8 or 9 as included in Microsoft Office 95 97 or 2000 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 with spreadsheets in general and Excel in particular If you re not you ll need to learn the basics about Excel first 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 scroll buttons scroll among the worksheet tabs in 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 File Print 3 Getting Started With R P 2000 Meet the Baxters To start R P 2000 choose Programs DHWA Retirement Spending Pla
33. rrelation data that is improper in this sense you get a warning message Summary Statistics The summary statistics sheet is provided for additional insight to the advisor and is probably not something that will be given to clients It provides insight into key bits of financial data and how the cash flows are working It can be used to troubleshoot puzzling cases or to reassure you that some details of a plan are in fact working as intended Looking at the tax impact of a sub optimal plan for withdrawing from the Tax deferred portfolio can be illuminating The numbers summarize what happened in each of 21categories for each year in the time period simulated for the total number of replications For the most part we report robust statistics such as medians The reason is for many of the portfolio balance categories we are summarizing information about a lognormal like distribution These have very fat right hand tails and averages can be poor descriptors The median is the value in the middle half the values are higher and half are lower Instead of standard deviation which is similarly overly influenced by outliers and a poor descriptor of spread we use interquartile IQ range IQ range is the difference between the 75 and the 25 percentiles Half of all the values are in an interval that includes the median and is of that size Nearly all the column headers are now self explanatory One exception is Median Income defined as the su
34. rt at Age 62 Profit Sharing Contribution 10 000 IRA Contribution Age to End Analysis 85 Retirement indexed income Roth IRA Contribution Retirement Non indexed Income Two person Pre Retirement Expenses Social Security income 18 000 Two person Retirement Expenses Second Person Description Second Person income Categories Second Person Expense Categories SA D Age Now Pre Retirement indexed Income 25 000 Personal Savings Contribution qe Retirement Age Pre Retirement Non indexed Income 401 K Contribution p Social Security Start at Age Profit Sharing Contribution IRA Contribution JE Age to End Analysis Retirement indexed income Roth IRA Contribution OoOo o Retirement Non indexed Income One person Pre Retirement Expenses 85 000 SS Social Security Income 12 000 One person Retirement Expenses Tax Assumptions EEE Two person Filing Status Married Joint p Two person Pre Retirement Federal Deductions 25 000 Two person Retirement Federal Deductions 20 000 X One person Filing Status O Married Joint Single OWMarried Separate O Head of Household One person Pre Retirement Federal Deductions 20 000 One person Retirement Federal Deductions 20 000 Taxable Social Security Income 85 00 State Tax Rate 3 00 Local Tax Rate 1 00 inflation Assumptions a Annual Inflation Rate 2 80 Inflation Rate for Social Securit
35. s that the Two person value is used for each year that both the first and second person are included while the One person value is used for each year that only one of them is included Only Married Filing Jointly is available when both the first and second person are included Two person One person Pre Retirement Retirement Federal Deductions the sum of exemptions and standard or itemized deductions in current dollars The client can consult a recent tax return to obtain a value for this That would be useful for example for someone who has a vacation home that they plan to sell when they get substantially older It is important to note for the Two person analysis that the Two person value is used for each year that both the first and second person are included while the One person value is used for each year that only one of them is included Taxable Social Security Income the percentage of Social Security Income subject to Federal income tax State Tax Rate for the user s state or a blended average if more than one residence Local Tax Rate for the user s township or municipality The model assumes local taxes apply to earned income only following assumptions regarding inflation must be entered Annual Inflation Rate Projected annual rate of inflation This will be used to convert current dollars to nominal dollars and vice versa Inflation Rate for Social Security Projected annual rate of inflation applied
36. s that there is a ten percent chance that this could happen seven years sooner On the other hand looking at the Projected Final Wealth chart we see that there is a fifty percent chance of having 935 000 at the end of the analysis period A linear analysis would show a value similar to this and the Baxters would continue with their current plan Because R P is a simulation that considers the nonlinear aspects of the scenario namely the portfolio growth uncertainties the results are not limited to one answer namely the expected value of the 3000 replications we ran The Baxters can evaluate their risk tolerance and decide whether or not to modify their retirement plan We assume the Baxters want a better than fifty percent chance of achieving their goals and recommend strategies to achieve them in Section 5 of this document If you would like a clearer understanding of the charts read Section 4 Understanding the Charts first 12 4 Understanding the Charts Final Wealth Density The chart at the top of the Detailed Charts sheet shows Final Wealth Density in either of two mathematical formats density or cumulative in Nominal Dollars and Inflation Adjusted Dollars You may toggle between the density representation or the cumulative representation at any time from this sheet We just ran 3000 repetitions of this client s case Those 3000 results are summarized as a density by showing what percentage of them fell in a small interval
37. sure of the definition of an input while running R P 2000 move the cursor over the input s label to display a brief description of the input To enable this feature under Excel Version 7 Office 95 select Tools Options and check Note Indicator in the Show area of the View tab To enable this feature under Excel Version 8 Office 97 select Tools Options and check Comment indicator only in the Comments area of the View tab After you become familiar with the inputs you might want to disable these descriptions by un checking the appropriate option Advisor Preferences e Disposition of Excess Income Controls whether any excess income Income minus Contributions except Profit Sharing minus Taxes minus Spending computed at the end of each year is spent or put in personal savings e Density Display Preference Controls the representation of the Final Wealth Density chart on the Detailed Charts worksheet see Section 4 for more details e Final Wealth Percentiles used for the Final Wealth and Projected Wealth charts The default values of 5 10 15 25 and 50 focus the user on down side risk For example suppose it is desired to spend 100 000 every year and to have enough money in your portfolios at age 90 to last 5 years If the value of the 50 curve at age 90 is 500 000 that means that in half of the scenarios the final wealth will be sufficient to last at least 5 more years This also implies that in half the sce
38. th the level of spending that is desired It is also intended for someone who is already retired to check that current spending levels are appropriate It is crucially important to realize that the plan must be reviewed on an annual basis An unexpectedly bad year may cause the user to be in an entirely different situation where reductions in spending are advisable Basic market changes may cause professional market analysts to advise new guidelines for assumptions about volatilities that accompany specific returns The user may experience fundamental changes in his personal situation with new responsibilities change in marital status or sale of some assets A prudent individual will review everything every year even if there is no big change in situation The program makes it easy to do that 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 Some sensitivity experiments should be done to show how differently things turn out if volatility is 15 rather than 10 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 they think they 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 b
39. the slot where it belongs on the Portfolio Description sheet For the Taxed portfolio you will have to estimate and enter the cost basis also doing a side calculation Client Portfolio Correlation Matrix Correlation is a number between 1 0 and 1 0 which measures the amount of sameness there is in movement between two investments At the extremes 1 0 means they move in lockstep in the same direction 1 0 means they move in lockstep in opposite directions and 0 0 means they are completely statistically independent All of our correlations by default are set to 1 0 This is the safe conservative assumption More realistic asset class correlation data can be found in standard sources such as Bloomberg Morningstar or Ibbotson publications 29 You can do your own experiment to see how important an assumption this is Typically for a 25 year scenario you can raise the median wealth about 30 by setting all the correlations to 0 0 vs 1 0 And the 15 wealth might go from almost nothing to a substantial amount So clearly this is a key input to keep set to the default 1 0 s unless you are sure of what you re doing Finally if you experiment be aware that some correlations you enter might be logically impossible It is impossible for example to have three investments where the pairwise correlations are all less than 0 5 If A is that contrary to B and B is that contrary to C then C cannot be that contrary to A If you enter co
40. to execute the simulation one more time Figure 10 DetailedInputs worksheet Remedies Completed Name Reed amp Caro Baxter Detailed Spending and Portfolio Profile Fa Fx Fa Fa Fx Fix Personal Savings Profit Sharing 401 K IRA Roth IRA Annual Contribution Contribution Contribution Contribution Contribution Equity Fixed income Expenses Nominal Nominal Nominal Nominal Nominal Proportion Proportion Nominal 8 500 10 000 10 000 0 0 80 0 20 0 87 500 8 741 10 284 10 284 0 0 80 0 0 0 89 985 8 990 10 576 10 576 0 0 80 0 0 0 92 540 9 245 10 876 10 876 0 0 80 0 0 0 95 168 9 507 11 185 11 185 0 0 80 0 0 0 97 870 9 777 11 503 11 503 0 0 80 0 0 0 100 649 0 0 0 0 0 80 0 0 0 124 208 0 0 0 0 0 80 0 127 735 0 0 0 0 0 80 0 0 0 131 362 NNNNNNNN So 0 0 0 0 0 80 0 20 0 135 093 0 0 0 0 0 79 0 21 0 138 929 0 0 0 0 0 78 0 22 0 142 874 0 0 0 0 0 75 0 25 0 146 931 0 0 0 0 0 75 0 25 0 151 103 0 0 0 0 0 75 0 25 0 155 393 0 0 0 0 0 74 0 26 0 159 806 0 0 0 0 0 73 0 27 0 164 344 0 0 0 0 0 72 0 28 0 169 010 0 0 0 0 0 71 0 29 0 173 810 0 0 0 0 0 70 0 30 0 178 745 0 0 0 0 0 69 0 31 0 183 821 0 0 0 0 0 68 0 32 0 189 040 0 0 0 0 0 67 0 33 0 194 408 0 0 0 0 0 66 0 34 0 199 929 0 0 0 0 0 65 0 35 0 205 606 0
41. umn will fill each cell with the IRS required minimum distribution schedule toggling between the IRS unisex single life and joint life expectancy table For more information see section 8 of this document Portfolio Description Rebalancing Model the model will rebalance the portfolio each year to the initial balance if Rebalance is chosen and will not rebalance if Buy amp Hold is chosen Taxed Accounts A description of the client s non sheltered accounts Contributions are made with taxed dollars Dividends and capital gains distributions are taxed although dividends are not taxed in the tax exempt fixed income account Withdrawals are taxed as capital gains after subtracting the appropriate cost basis Tax deferred Accounts A description of the client s sheltered accounts such as IRA Roth IRA 401 K and company Profit Sharing Contributions are made with pre tax dollars for IRA and 401 K accounts Roth IRA contributions are made with taxed dollars Profit Sharing contributions are not reflected in income Dividends and capital gains distributions are not taxed Withdrawals are taxed as ordinary income for IRA 401 K and Profit Sharing accounts Roth IRA withdrawals are not taxed Premature withdrawals are subject to the IRS penalty XXXX Year end Balance Total amount invested in each account at the end of the year prior to the start of this analysis period 27 e Average Annual Return average gross annual
42. unt entered should be in current dollars and will be inflated each year according to the Annual Inflation Rate This amount also only applies to pre retirement years The following expense category inputs apply to both the First Person and Second Person Expense Categories Again for each input it is assumed that the amount entered applies to each and every year of the inflation adjusted income schedules A varying schedule may be modeled by editing the schedules produced on the FirstDetails and SecondDetails worksheets e One person Two person Pre Retirement Retirement Expenses The amount of money it is desired to spend each year before after retirement age in after tax dollars It is important to note for the Two person analysis that the Two person value is used for each year that both the first and second person are included while the One person value is used for each year that only one of them is included 25 Th Th Personal Savings Contribution Total amount to invest each pre retirement year in non sheltered accounts with taxed dollars Sheltered Contribution Total amount to invest each pre retirement year in accounts such as IRAs 401 K and Roth IRAs with pre tax dollars following tax assumptions must be entered Two person One person Filing Status Tax tables corresponding to 1999 federal returns are coded in the program for each of the choices It is important to note for the Two person analysi
43. wing the inputs click the Next button and answer Yes to the next query Overwrite Detailed Spending and Portfolio Profile This will bring up the Choose Desired Percentage in Equity Accounts dialog Enter 60 and click OK The DetailedInputs worksheet should now be displayed This is a schedule of the combined Income and Expense Profile of both the First and Second Person inflated to nominal dollars where appropriate see Figure 3 amp Figure 4 Figure 3 DetailedInputs worksheet Base Case Name l DE z p r i Cases Detailed Spending and Portfolio Pr Age Age indexed Non indexed Social Total Federal Personal Savings Profit Sharing 401 K IRA oo of income income Security Deductions Contribution Contribution Contribution Contribution Year First Second Nominal Current Nominal Nominal Nominal Nominal Nominal Nominal 2000 145 000 25 000 6 000 10 000 10 000 2001 149 117 25 710 6 170 10 284 10 284 2002 153 352 26 440 6 346 10 576 10 576 157 706 27 191 6 526 10 876 10 876 27 963 27 963 0 0 0 28 757 i 28 757 0 0 23 659 0 24 331 25 021 25 732 26 463 27 214 27 987 28 781 29 599 30 439 31 304 32 192 33 107 34 047 35 013 36 008 37 030 38 082 39 163 40 275 41 419 42 595 i 43 804 27 029 45 048 27 796 46 327 Figure 4 DetailedInputs worksheet scrolled Base Case Name Reed amp Caro Baxter Date 3 1 00 Case
44. y 2 80 Navigating the Input Worksheets R P 2000 uses eight visible Excel worksheets to enter input data AdvisorPreferences ClientDescription FirstDetails SecondDetails DetailedInputs PortfolioDescription PortfolioCorrelation and SensitivityInputs A ninth worksheet PortfolioConsolidation is made visible by clicking Consolidate Accounts on the PortfolioDescription worksheet see Consolidating Accounts in Section 8 Throughout the workbook cells that require user input are identified by a pale yellow shade These are inputs that merit careful consideration by anyone planning their retirement Default values are provided where possible All initial dollar amounts are entered in current dollars Notice the lt lt and gt gt buttons in the upper left corner of each worksheet We refer to the lt lt button which takes you to the previous worksheet as the Back button and the gt gt button which takes you to the next worksheet as the Next button To navigate the worksheets it is recommended that you use the Next and Back buttons rather than the tabs at the bottom of the worksheets In some key cases clicking the Next button formats the next worksheet and fills it with data based upon the data entered so far More on this later Click the Back button now This will display the AdvisorPreferences worksheet see Figure 2 The inputs on the AdvisorPreferences worksheet are intended to be static and
Download Pdf Manuals
Related Search