2024 enhancements to the VeriPlan financial planning Excel worksheet software
The 2024 version of the VeriPlan DIY home financial planning spreadsheet software:
–
1) Plan for over age 70 Qualified Charitable Distributions from traditional IRA accounts
- Plan your retirement charitable giving, while you optimize your taxes
- Differentiates QCD distributions by spounse and age
- Is fully integrated with VeriPlan’s year-by-year Roth conversions analysis
2) Integrates a new “painless” Monte Carlo investment performance variance facility:
- VeriPlan’s Monte Carlo method allows you to develop projections with long-term historical investment performance parameters that systematically vary below or above the compounded returns of the centerline projection model. The user can select any historical return integer percentile from 1 (worst) to 99 (best) with 50 as the centerline index.
- Projections that use these 1 to 99 historical returns percentiles automatically align with and rebalance according to the user’s chosen cash, bond and stock strategic asset allocation strategy and with all associated tax and other projection factors.
- VeriPlan’s Monte Carlo sampling approach is based upon a systematic Monte Carlo sampling study of 96 years of US historical bond and stock returns using various asset allocations, cash flow investment models, and investment holding periods. The study compared 198 different asset allocation, cash flow, and period combinations. For each, 5,000,000 statistical sampling draws were made and then were ordered from worst to best. Percentiles were developed and the investment internal rate of return was calculated for each percentile of each combination.
- The results revealed that, in practice, it was unnecessary to rerun Monte Carlo simulations against the same almost 100 year historical investment performance data set, even when cash flow patterns varied.
- VeriPlan’s new approach does not interfere with any user’s development of their lifetime financial projection model. If a user decides to vary their investment return assumptions using VeriPlan’s Monte Carlo mechanism, any change to their projection will still be fully automated and characterized by sub-second computer response time.
3) New Portfolio Return and new Portfolio Risk worksheets replace the consolidated Risk & Returns worksheet
Portfolio Return worksheet sections:
1) Apportion your projected bond returns between US Treasury and AAA bonds
2) Compounded historical real dollar asset class average return assumptions
3) Set the financial asset class return adjuster tool
4) Risk-free rate of return assumptions used by VeriPlan
5) Total pre-tax return assumptions for the cash, bond, and stock financial asset classes
Portfolio Risk worksheet sections:
1) Methods to vary investment asset class return assumptions and values
2) Set the Monte Carlo Variance Tool
3) Set the Standard Deviation Variance Tool
4) Set the Current Portfolio Revaluation Tool
5) Set the Safety Margin Tool
6) Explanation of the Monte Carlo Variance Tool
4) Implements all normal annual release updates, including:
- US Federal and 50 state + DC tax rates, limits, phase outs, and other tax parameters
- tax parameters related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals
- user adjustable historical asset class returns, inflation, and volatility data for 1928 through the present
2023 VeriPlan financial planning Excel worksheet enhancements
The 2023 version of the VeriPlan financial planning Excel worksheet software:
1) Projects employee and employer contributions up to legal maximums into 401(k), 403(b), 457, Thrift Savings, SIMPLE-IRA, and/or SEP-IRA plans. Directs pre-tax and after-tax contributions into traditional retirement accounts and/or Roth retirement accounts. Similarly, controls lifetime personal and spousal IRA contributions into traditional and/or Roth retirement accounts
2) A new Medicare worksheet projects retirement healthcare costs including Medicare Parts A, B, C, and D. Includes the automatic projection of Medicare IRMAA insurance subsidy reductions during higher retirement income years
3) Analyze year-by-year Roth conversions informed by:
- projected federal marginal income tax rates,
- Social Security retirement benefit income taxation,
- Medicare IRMAA premium reductions, and
- asset tax location optimization.
4) Explains the lifetime economic and investment tradeoffs between traditional and Roth retirement account assets
5) Automates 50 state + DC differential income taxation of:
- Social Security retirement benefits and
- traditional retirement plan withdrawals and RMDs
6) A new worksheet allows you to plan a future move from your current state of residence to another state, while automatically switching taxation.
7) Project up to ten different pension, annuity, and/or deferred compensation retirement payouts, while controlling taxation and directing deposits into taxable, traditional retirement, and/or Roth retirement accounts.
8) Implements SECURE Act 2.0’s delay of initial RMD ages to 73 in 2023 and age 75 in 2033. Differentiates spousal RMDs by age and asset ownership.
9) Social Security income benefits
- Social Security spousal benefits — Automatically determines the highest benefit based on the individual’s work record or the spousal benefit, including early claiming reductions.
- Social Security disability benefits — If the SSA has determined that one or both earners are disabled and are receiving disability benefits at any age, VeriPlan will project these disability benefits for you.
10) All VeriPlan financial planning excel worksheet graphics now include a dynamic x-axis beginning with the current age of the primary user
11) Provides explanations and graphical examples of constant purchasing power “real dollar” projections when average inflation is removed
12) Implements all normal annual release updates, including:
- US Federal and 50 state + DC tax rates, limits, phase outs, and other tax parameters
- tax parameters related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals
- user adjustable historical asset class returns, inflation, and volatility data for 1928 through the present
13) A new Retirement Planning worksheet discusses:
- Participation in IRAs and in Employer Defined Contribution Plans
- Health care expenses in retirement
- How the 50 states and DC tax Social Security and traditional retirement withdrawals
- The longevity challenge in lifetime financial planning
- Safe investment portfolio withdrawals to fund retirement
- The origin of the 4% retirement withdrawals rule-of-thumb
- Sequence of returns risk in retirement portfolios
- Portfolio growth potential during retirement
14) A new Optimizations worksheet discusses:
- Lifelong tax savings from investment portfolio asset tax location
- Early retirement health care expenses and ACA tax subsidies before Medicare
- Social Security retirement claiming age considerations
- Spending traditional retirement assets, while delaying Social Security payments
- Compound growth factors
- Tax-exempt municipal securities and VeriPlan’s return assumptions
- VeriPlan’s design and discounted cash flow modeling
2022 VeriPlan lifetime financial planning Excel worksheet enhancements
The 2022 version:
1) Updates US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using available 2022 information.
2) Updates tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals for 2022.
3) Updates user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2021.
4) Married users can now project Required Minimum Distributions (RMDs) that are differentiated by age and are proportional to estimates of relative individual ownership of future account balances in traditional retirement accounts.
5) For the first time in 20 years, the IRS has updated its Uniform Lifetime Table related to life expectancy, which is used to calculate RMDs from IRAs and employer sponsored retirement plans. The new Uniform Lifetime Table has the moderate effect of reducing RMDs during one’s seventies and eighties. In general, the older table’s numbers are pushed out by about two years during one’s early 70s and this difference narrows thereafter. Moderately increasing the RMD formula denominator for any given year reduces RMDs each year. In addition, VeriPlan has updated the Single Life Table for those who already own inherited IRAs with a lifetime RMD payout schedule.
6) For the male and female life expectancy chart, the data has been updated to the most recent Period Life Table available from the Social Security Administration, but this table does not yet reflect the Covid-19 pandemic’s impact on average US mortality statistics.
2021 VeriPlan lifetime financial planning Excel spreadsheet enhancements
The 2021 version:
1) Updates US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available.
2) Updates tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals for 2020.
3) Updates user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2020
4) A projection model Comparison Tool has been added to VeriPlan.
The VeriPlan financial planning Excel worksheet software is built on the Microsoft Excel spreadsheet engine. As with any other spreadsheet, any change that you make to one cell in VeriPlan will change the results of all other spreadsheet cells that are connected by the underlying logic. Therefore, spreadsheets do not automatically “save to state” of the model that existed just before the most recent change. Nevertheless, semi-automated model comparisons are possible, if you first manually “save the state” of a projection model, before making further revisions.
Four new worksheets implement the VeriPlan Comparison Tool. You can now make easier comparisons between projection models with these steps:
A) You develop a relatively complete model with all of your data and assumptions.
B) Using a preformatted worksheet with all output data, you copy all data from your projection model, and then use Excel’s “Paste Special” function to paste only the output data values from your initial model into a separate spreadsheet within VeriPlan. This “Copy” all data and then “Paste Special — values only” method severs the connection between the underlying VeriPlan spreadsheet formula logic and the data that is output by VeriPlan. Doing this locks the “state” of the data values from your prior projection model.
C) You continue to revise one or more assumptions and/or data inputs within VeriPlan to reflect any alternative personal financial strategy.
D) The VeriPlan Comparison Tool will automatically present the differences between the “live” data being output from the revised model versus the “locked” data values of the prior projection model. This allows you to review and evaluate much more quickly the data output differences between any two lifetime projection models.
E) While this comparison tool is designed for model comparisons within VeriPlan, it has an added benefit to a sophisticated spreadsheet user. This new ability to copy all of VeriPlan’s output means that a user could also copy VeriPlan model data output into a separate workbook. Using Microsoft Excel’s normal Copy and Paste functionality (versus the “Paste Special — values only” method) with Excel’s inherent capability of automatically updating values between workbook files, this means that a user could build whatever additional planning logic they might desire into that separate workbook and keep it linked to their original VeriPlan model.
5) From the outset, VeriPlan’s architecture has supported Roth retirement contributions analysis. The 2021 version now adds the ability to plan for tax-optimal, year-by-year conversions of traditional retirement accounts into Roth retirement accounts. This new Roth conversion feature projects specific yearly dollar amounts that could be converted within each federal income tax rate bracket. When this new Roth conversion feature is combined with the new the VeriPlan Comparison Tool, a user can quickly develop an optimal, minimum income tax, lifetime Roth conversion plan.
6) VeriPlan’s future debt functionality has been enhanced to be more consistent with VeriPlan’s constant purchasing power real dollar projections over time. A VeriPlan user can now specify in current dollar terms the principal, interest, and payment parameters for future debts that would be assumed and commence in future years. The VeriPlan Excel retirement calculator with inflation will automatically adjust the user’s current dollar input parameters for the impact of future inflation. Inflation adjustments are based upon long-term historical inflation rates, which the user can adjust if desired. These future debt enhancements apply to all residence, consumer debt, rental real estate, and other property debt functionality.
2020 VeriPlan lifetime financial planning worksheet enhancements
The 2020 version:
1) Updates US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available.
2) Updates tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals for 2019.
3) Updates user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2019.
4) Includes enhancements related to the SECURE ACT, which was enacted in December of 2019. The initial age for traditional retirement account RMDs was changed to 72. Because inherited “stretch IRAs” were eliminated, future inherited retirement accounts must distribute all assets within ten years. Retirement accounts inherited prior to 2020 continue as stretch IRA and this logic is also retained in VeriPlan.
2019 VeriPlan lifetime retirement planning calculator Excel enhancements
The 2019 version of the VeriPlan financial planning excel worksheet:
1) Updates US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available.
2) Updates tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals for 2019.
3) Updates user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2018
2018 VeriPlan financial projection planning Excel spreadsheet enhancements
The 2018 version:
1) Updates US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available. Includes changes related to the supposed tax reforms in the “Tax Cuts and Jobs Act” of 2017 signed into law in December of 2017
2) Updates tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals for 2018.
3) Updates user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2017
4) Adds functionality to enable future gifts and inheritances, including traditional and Roth beneficiary retirement accounts and their associated lifetime required minimum distributions
2017 VeriPlan home financial planning workbook enhancements
The 2017 version:
1) Updates US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available.
2) Updates tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals.
3) Updates user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2016
2016 VeriPlan family financial planning worksheet software enhancements
The 2016 version:
1) Updates US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available.
2) Updates tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals.
3) Updates user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2015
4) Expands VeriPlan’s graphics and data output, including new graphics for:
a) Retirement income
b) Retirement shortfalls
c) Rental real estate and other properties
d) Debt principal owned
4) The 2016 version substantially enhances the VeriPlan financial planning Excel worksheet template software functionality related to residence, rental real estate, other property, and debt management. All these new enhancements are fully integrated with all other VeriPlan income, tax, debt, cash flow, and investment functionality.
5 Personal Residences:
- Up to 5 personal residence assets are now supported. This allows lifetime modeling of first and second homes, vacation properties, and upgrades.
- Automated functionality includes: a) asset appreciation, b) tax basis tracking, c) real estate tax payments and deductions, and d) maintenance expenses.
- Two debts per property are modeled and automatically amortized including accelerated payments, if desired. Mortgage, home equity line of credit, and real estate tax deductions are projected including itemized deduction limitations associated with number of properties, total income, and type of debt.
- Individually, each of these five residential properties can be owned currently or can be planned for purchase in any future user-specified year.
- For planned future purchases, VeriPlan automatically takes into account interim appreciation, purchase expenses, planned debts, and needed purchase cash.
- Any property can be set for sale in any future year, and capital gains above the tax basis with sales expenses are taxed automatically at federal, state, and local levels, after taking into account allowable capital gains exclusions. Debts are retired and positive/negative cash flow is added to or taken from future financial assets automatically.
10 Personal Debts:
- In addition to the other debts associated with residences, rental real estate holdings, and other properties, VeriPlan supports the projection modeling of ten other debts. Such debts might be associated with credit cards, auto purchases, student loans, etc.
- Individually, each debt is amortized at the minimum payment rate, or at an accelerated rate should the user plan to retire the debt more quickly.
- Debts that are currently owned and future planned debts are supported. For example, if a future debt-funded car purchase is planned or debt would be incurred to support a child in college, this functionality can be used.
- Individual debts can be categorized as consumption-oriented or investment-oriented debts.
- When a projection model uses real, constant-purchasing power dollars across time, future debt payments and future debt principal balances owned are instead denominated in cheaper nominal dollars. As with all the other debts its projects, VeriPlan automatically adjusts for inflation both future debt payments and future debt principal balances. These inflation adjustments are based upon long-term historical inflation rates, but if the user wishes to change this inflation assumption, a user adjustment is also provided.
10 Rental Real Estate Properties:
- Up to 10 rental real estate properties are now supported. This allows lifetime modeling of personal real estate activities that are integrated with your personal financial affairs.
- Automated functionality includes: a) asset appreciation, b) tax basis tracking, c) real estate tax payments and deductions, and d) maintenance expenses.
- Two debts per rental property are modeled and automatically amortized including accelerated payments, if desired. Mortgage, home equity line of credit, and real estate tax deductions are automatically projected, including itemized deduction limitations associated with number of properties, total income, and type of debt.
- Individually, each of these 10 rental real estate properties can be owned currently or can be planned for purchase in any future user-specified year.
- For planned future purchases, VeriPlan automatically takes into account interim appreciation, purchase expenses, planned debts, and needed purchase cash.
- Any rental real estate property can be set for sale in any future year, and capital gains above the tax basis with sales expenses are taxed automatically at federal, state, and local levels. Debts are retired and positive/negative cash flow is added to or taken from future financial assets automatically.
- Rental real estate functionality also takes into account non-cash depreciation allowances for each property and adjusts projected income taxes. In addition, these depreciation deductions are recaptured, when a property is projected to be sold.
- For each rental, the user can indicate whether they actively participate, and VeriPlan will automatically apply the special loss allowance for active participation, if income is within limits.
10 Other Properties:
- Up to 10 other properties are now supported. This allows lifetime modeling of other closely-held properties, such family businesses, valuable collections, illiquid investments, etc. Projected financials related to these other properties are also integrated with your personal financial affairs within VeriPlan.
- Automated functionality includes: a) asset appreciation, b) tax basis tracking, c) business and real estate tax payments and deductions, and d) other operating expenses.
- One debt per property is modeled and automatically amortized including accelerated payments, if desired.
- Individually, each of these 10 other properties can be owned currently or can be planned for purchase in any future user-specified year.
- For planned future purchases, VeriPlan automatically takes into account interim appreciation, purchase expenses, planned debts, and needed purchase cash.
- Any other property can be set for sale in any future year, and capital gains above the tax basis with sales expenses are taxed automatically at federal, state, and local levels. Debts are retired and positive/negative cash flow is added to or taken from future financial assets automatically.
2015 VeriPlan lifelong financial planning Excel sheet enhancements
The 2015 version:
1) Enhanced automated lifetime investment cost projections for financial assets
2) Added graphic for male and female total life expectancy and remaining life expectancy given life already lived
3) Updated US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available. Updated tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals.
4) Updated user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2014
2014 VeriPlan financial planning worksheet enhancements
The 2014 version focused on taxation and parameter updates:
1) Updated US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available. Updated tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals.
2) Updated user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2013
2013 VeriPlan financial planning Microsoft Excel workbook software enhancements
Primary enhancements and updates in the 2013 version of the VeriPlan lifetime personal finance software:
1) Enhanced tax logic for the new:
* expanded federal income tax brackets that came out of the fiscal cliff compromise
* 3.8% federal Net Investment Income Tax on higher incomes
* 20% federal long-term capital gains tax bracket
* exemption and deduction phaseouts on higher incomes
2) Updated US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest information available. Updated tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals
3) Enhanced the Current Portfolio Rebalancing Tool to measure both the total and changed amounts of cash, bond, and stock assets that would be allocated across taxable accounts, traditional retirement accounts, and Roth retirement accounts. (This tool allows users to implement “asset tax location” and optimize taxation across their asset classes and account types.)
4) Updated user adjustable historical asset class returns, inflation, and volatility data for 1928 through end of 2012
5) Added patterns to colors on all graphics, improving ease-of-interpretation when reading black-and-white printouts
2012 VeriPlan home financial planning software enhancement notes
Primary enhancements and updates in the 2012 version of the VeriPlan do-it-yourself home personal finance software:
1) Streamlined and consolidated all functionality and documentation for ease-of-use while retaining all capabilities
2) Published a separate VeriPlan User Guide Manual. This user guide is the second half of the informative and completely FREE ebook entitled “Lifetime and Retirement Financial Planning and Investing, including the VeriPlan User Manual” To download your free copy of this ebook, click here to go to VeriPlan’s overview page at:
Then, scroll down about half-way, until you will see in the left-hand column the clickable graphic for this free VeriPlan User Guide Manual
3) Added the Current Portfolio Rebalancing Tool to assist in allocating cash, bond, and stock assets across taxable accounts, traditional retirement accounts, and Roth retirement accounts.
4) Updated internal and user changeable historical asset class returns, inflation, and volatility data for 1928 through 2011. Provided new historical graphic and data table with these annual data series
5) Updated US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest year information available from tax authorities. Updated tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals
2011 VeriPlan retirement planning worksheet enhancement notes
Primary enhancements and updates in the 2011 version of the VeriPlan lifetime home personal financial planning Excel worksheet software were:
1) Reorganized and expanded VeriPlan’s projection graphics and associated data tables to increase clarity and to expose more of VeriPlan’s internal projection information
2) Updated US Federal and 50 state + DC tax rates, limits, phaseouts, and other tax parameters using the latest year information available from these various tax authorities. Updated tax information related to traditional and Roth tax-advantaged IRA and employer-sponsored retirement plan contributions and withdrawals
3) Added “How To Use VeriPlan” worksheet to clarify methods of developing and comparing VeriPlan’s lifetime financial planning projections.
4) Improved Social Security inputs and controls for two earners who expect to qualify for separate retirement payments
5) Updated internal and user updateable historical asset class returns, inflation, and volatility data for 1928 through 2010. Provided new historical graphic and data table with these annual data series
6) Instituted spreadsheet grouping system for VeriPlan to improve ease-of-use
7) Added information about investment “asset tax location” optimization
8) Provided information about Social Security and tax-advantaged retirement plan tax optimization strategies in the period between retirement and the initiation of Required Minimum Distributions (RMDs)
9) Reviewed all 77 worksheets, graphics, and data tables for general improvements and internal/external hyperlink updating and validation
2006 through 2010 VeriPlan lifetime financial planning Excel spreadsheet software enhancements
All core VeriPlan personal financial planning excel worksheet software features have been robust and stable since 2006. Rather than continue a list of earlier enhancements in previous years, please refer to these two website pages for the:
VeriPlan personal financial planning calculator software overview
or the free
