FAQ: QWIN Safe Withdrawal Rate Reporting - Custom Report Creation How-to....

Scooterlam

For those who are retired or FIRE'd and follow the 4% SWR "rule" or some variation,  I created a custom report that helps keep tabs on my withdrawal rate from my investment accounts.  I setup reports for prior calendar year(s) and current year and save them under "My Saved Reports".  By doing this, I can assess my SWR from prior years as well as have a YTD assessment.

These reports tie-in well with my use of investments, budgets (set to SWR) and Lifetime Planner features in Quicken.

I wanted to share how I built the report.  It's a fairly simple report to build.  Of course, this is but one approach to creating SWR reports in Quicken. Please contribute, suggest improvements etc and perhaps it can become a Report Center standard report.

I don't intend this post to debate relevance of the Bill Bengen or Trinity studies or other derivative studies / strategies, past or future.   But, simply to offer a way to report retirement portfolio draw downs in order to compare against your target SWR.  There are some links at the end of the post if you wish to learn more or refresh about SWR use in retirement planning.  Note that there are many spend-down strategies that use SWR concepts as well.

The assumptions...

• You transfer funds from retirement investment accounts (brokerage, tax deferred, tax exempt) to banking accounts (checking or savings) where you cover (and categorize) personal expenses.
• All your retirement investment accounts and spending accounts are tracked in Quicken.  That is, outside accounts are not considered, of course!
  
• Assets such as real estate or PV or FV of SSI or Pensions are not considered... Only investment accounts listed above.
  
• You know or have calculated your SWR at retirement based upon your total investment portfolio value.  That is, you know the basis from which you compare this report's result to determine SWR %.
  
• Transfer of funds (cash) are agnostic of the investment transaction that produces them (DIV, INT, SOLD et al).  The report (as with most Quicken reports) allows you to drill into the line item to understand the transaction(s) associated with the funds to be transferred.
  
• You have all your investment and banking accounts accounted for in the report, for the year of your report.  That is, don't forget to include accounts that you may have closed during the reporting year!
  
• If you pay bills or otherwise spend directly from an investment account, then this report may under/over report your SWR.  I segregate investments accounts from spending accounts for a number of reasons.  If you do use an investment account (cash management account) to pay bills you may have to adjust the accounts and category setting to avoid either 1. missing a transaction or 2. double counting a transaction!  I haven't tested this.

Lets build it....  Report Option 1

1.  In the Report Center>Quicken Standard Reports>Banking> choose the "Transactions" report and press "show report" button.  Image 1.
2.  In the Transaction report, select the Customize gear in the upper right of the screen.  Image 2.
3.  Modify the display tab attributes as shown in Image 2.
4.  In the Accounts tab, select all your banking accounts that receive distributions from your Investment accounts.  In other words, your spending accounts (ex.checking and Savings)
   
5. In the Categories tab, select all your Investment accounts that distribute funds to your banking accounts (now and future).  You'll find these near the bottom of the category window.
   
6. In the Payee, Tags and Securities tabs, ensure "select all" is chosen.  There is no change to Advanced tab.
7.   Display the report by clicking the OK button.  The resulting report should look like Image 3.
8.   Save the report off under My Saved Reports.
9.   Validate report results with your investment account register transfers to your spending     account(s).  Are all your fund transfers to your spending accounts (and vise versa) accounted for?  Remember you may have some contributions back to your investment portfolio from your spending account (excess cash, windfalls etc.).
   
10.  After validating your results, try reorganizing the report structure to make sense to you!  Make it simple and understandable!
    
11.  Save off any modification you make, then make copies of the report structure for prior years and evaluate.  Again, don't forget to include closed accounts used in prior years!
    
12.  Use the results of the report (withdrawal amount from retirement investment accounts) to compare with your chosen SWR strategy and the year's calculated SWR.

Was this useful?  Did I miss something?  Do you have specific suggestions to make it better, more applicable to others for purposes of tracking SWR?  Feedback?

Scott

QW 2019 HBR

Safe Withdrawal Rate Articles / Research - Fidelity provides a decent, albeit watered-down discussion of SWR, combined with a dose of marketing.  The Kitces research has much more depth and breadth about SWR.   And, you can always consult WIlliam Bengen's original study or the later Trinity Study.

https://www.fidelity.com/viewpoints/retirement/how-long-will-savings-last 

https://www.kitces.com/?s=safe+withdrawal+rate

Image 1

Image 2

Image 3

EDIT 01.22.2020 - Report Option 2

After using this report in 2019, I wanted to suggest that the option to Swap Account and Category as I mentioned in Image 3, results in a far better report, IMO.   That is, select your investments accounts you withdraw from for "Accounts" and select your spending (banking, cash) accounts you deposit to and spend from for "Category".   Makes much more sense and less confusing when reading the report from left to right!





EDIT 04.04.2021 - Report Modification 1

Another report refinement - An income deposit was made directly into one of my investment accounts but does not show in my SWR report....This could occur when you have earned or unearned income that serves to reduce that years withdrawal rate (ex. an IRS Economic Impact Payment).

This scenario can be handled in 1 of 2 ways, of which the second way requires a slight change in the report:

1. If you have the option, first deposit the income into a spending account (ex. checking), then transfer that income to an investment account.  No change to report. Preferred way!                    
2. If you deposit the income directly into an investment account,  then ensure that you have the income category name selected as a category (report option 2) or account (report option 1).  See image showing this modification to "report option 2".

Jim_Harman

Thanks for posting this, @Scooterlam

Key to this approach is the concept of creating a retirement "income" stream for budgeting purposes with transfers from investing to spending accounts and making all your expenditures for living expenses from spending accounts and not directly from investing accounts. This is a good approach IMO even if you do not use this report. 

jcgmc24

This will be great when I get to FI/RE. For now, using it to showcase how much I have contributed to my different retirement accounts (from pay checks etc..). Thanks for the detailed write up.