The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2019 made significant changes to the required minimum distribution (RMD) schedule for individual retirement accounts (IRAs) and defined contribution retirement plans. Excel spreadsheet calculators are developed to calculate annual RMD cash flows throughout retirement for those who are retired and for those who are planning to retire.  Unlike internet calculators, the spreadsheet calculators allow savings to earn monthly interest throughout retirement.  Further, the calculators are easy to use and allow individuals to forecast long horizon RMD distributions for subsequent tax or reinvestment planning purposes

Arnold, Tom

Earl, Jr., John H

Marshall, Cassandra D.

English

The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2019 made significant changes to the required minimum distribution (RMD) schedule for individual retirement accounts (IRAs) and defined contribution retirement plans. Excel spreadsheet calculators are developed to calculate annual RMD cash flows throughout retirement for those who are retired and for those who are planning to retire. Unlike internet calculators, the spreadsheet calculators allow savings to earn monthly interest throughout retirement. Further, the calculators are easy to use and allow individuals to forecast long horizon RMD distributions for subsequent tax or reinvestment planning purposes

University of Richmond

University of Richmond UR Scholarship Repository Finance Faculty Publications Finance 5-17-2022 Required Minimum Distribution (RMD) Spreadsheet Calculators Based on the SECURE Act of 2019 Tom Arnold University of Richmond, tarnold@richmond.edu John H. Earl, Jr. Univ of Richmond, jearl@richmond.edu Cassandra D. Marshall University of Richmond, cmarshal@richmond.edu Follow this and additional works at: https://scholarship.richmond.edu/finance-faculty-publications  Part of the Finance and Financial Management Commons Recommended Citation Arnold, Tom, C., John H. Earl, Jr., and Cassandra D. Marshall. "Required Minimum Distribution (RMD) Spreadsheet Calculators Based on the SECURE Act of 2019," University of Richmond Robins School of Business, (2022): 1-9. This Article is brought to you for free and open access by the Finance at UR Scholarship Repository. It has been accepted for inclusion in Finance Faculty Publications by an authorized administrator of UR Scholarship Repository. For more information, please contact scholarshiprepository@richmond.edu. Required Minimum Distribution (RMD) Spreadsheet Calculators  Based on the SECURE Act of 2019    Tom Arnold, CFA, CIPM The Robins School of Business Department of Finance 102 UR Drive University of Richmond, VA 23173 tarnold@richmond.edu O: 804-287-6399 F: 804-289-8878  John H. Earl, Jr. The Robins School of Business Department of Finance 102 UR Drive University of Richmond, VA 23173 O: 804-289-8589 F: 804-289-8878 jearl@richmond.edu   Cassandra D. Marshall The Robins School of Business Department of Finance 102 UR Drive University of Richmond, VA 23173 O: 804-287-1851 F: 804-289-8878 cmarshal@richmond.edu       Please do not cite without permission.  May 17, 2022    1 Required Minimum Distribution (RMD) Spreadsheet Calculators  Based on the SECURE Act of 2019  The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2019 made significant changes to the required minimum distribution (RMD) schedule for individual retirement accounts (IRAs) and defined contribution retirement plans. Excel spreadsheet calculators are developed to calculate annual RMD cash flows throughout retirement for those who are retired and for those who are planning to retire.  Unlike internet calculators, the spreadsheet calculators allow savings to earn monthly interest throughout retirement.  Further, the calculators are easy to use and allow individuals to forecast long horizon RMD distributions for subsequent tax or reinvestment planning purposes.         2 INTRODUCTION  The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2019 was signed into law on December 20, 2019.  This bill had various provisions to improve access to tax advantaged retirement accounts, encourage small businesses to adopt retirement plans, and also to prevent older individuals from outliving their retirement assets (see Beam Tacchino, 2020). One of these changes was to push back required minimum distribution (RMD) payments from tax advantaged retirement accounts from age 70 ½ to age 72.1  This means that the first RMD could begin the year after you become 72 years of age, since the first distribution (withdrawal) is based on being 72 years old, but is not due until the following year on April 1st (required start date).  Consequently, the year after becoming 72 years old, there is an RMD distribution due by April 1st (based on being 72 years old, the year prior) and an additional RMD distribution due by December 31st (based on being 73 years old, in the current year).  After this initial year of potentially two RMDs, only one RMD distribution is due each year by December 31st.2   The actual distribution or cash flow is based on a schedule provided by the U.S. Internal Revenue Service (IRS Publication 590-B) and the amount of money in the retirement account as of December 31st of the prior year.  For example, if you become 75 years old in 2022, the RMD is based on the IRS RMD schedule applicable to being 75 years old and the Fair Market Value (FMV) in the retirement account on December 31, 2021 (i.e. technically, when you were 74 years old).  1 Tax advantaged plans subject to the SECURE Act rules include employer sponsored retirement plans (i.e., 401(k), 403(b), 457(b), ROTH 401(k), ROTH 403(b)), Traditional IRAs, SEP IRAs, and SIMPLE IRAs.  ROTH IRAs are not subject to required withdrawals until the death of the owner.  According to the CDC, life expectancy at birth for the U.S. population increased to 78.8 years in 2019.  In 2019, the increase to the RMD age from 70 ½ to 72 was the first such change to since the RMD first became law in 1986.   2 Individuals may also choose not to delay their initial RMD into the following year, which would allow for the withdrawals to be spread across two calendar years for tax purposes.  3  Understanding RMD payments is an essential part of retirement planning because it dictates how retirement savings must be disbursed, which in turn will impact taxes due and possible reinvestment planning throughout the retirement years.  Simply viewing the RMD schedule (see Table 1) does not really provide the context for how large these distributions may become while being retired.  In addition, RMDs may need to be calculated for each retirement account separately to avoid additional excise tax.  For retirees with multiple accounts, the total deferred tax liability and subsequent reinvestment planning could be substantial. Table 1: Age-Based Distribution Period for RMDs (based on Table III of IRS Publication 590-B)   Age: Distribution Period:  Age: Distribution Period:  Age: Distribution Period: 70 27.4 86 14.1 102 5.5 71 26.5 87 13.4 103 5.2 72 25.6 88 12.7 104 4.9 73 24.7 89 12.0 105 4.5 74 23.8 90 11.4 106 4.2 75 22.9 91 10.8 107 3.9 76 22.0 92 10.2 108 3.7 77 21.2 93 9.6 109 3.4 78 20.3 94 9.1 110 3.1 79 19.5 95 8.6 111 2.9 80 18.7 96 8.1 112 2.6 81 17.9 97 7.6 113 2.4 82 17.1 98 7.1 114 2.1 83 16.3 99 6.7 115 + 1.9 84 15.5 100 6.3   85 14.8 101 5.9   Applicable to:  Unmarried owners Married owners whose spouses are not more than 10 years younger Married owners whose spouses are not sole beneficiaries of their IRAs  RMD for age X is the retirement account balance as of 12/31 the previous year divided by the distribution period associated with X.   4  For example, assuming the account holder meets the criteria in Table 1 and has an account balance (FMV) of $1,000,000 at 12/31/2021.  The $1,000,000 becomes the basis for determining the RMD in 2022.  Assuming the account holder is 75 in 2022, the RMD in 2022 is $1,000,000 divided by the distribution period associated with age 75 (i.e. 22.9) and is equal to $43,668.12 (= $1,000,000 ÷ 22.9).   The RMD needs to be distributed from the retirement account by 12/31/2022.  Failure to distribute the RMD by that date results in a 50% excise tax on the amount not distributed in addition to the deferred income tax due (see Benz, 2019).  This process of determining and distributing the RMD will continue every year throughout retirement.  Given the complexity of the RMD distribution rules, many of the parameters are more easily understood and applied by the development of spreadsheet calculators in Excel to compute RMD withdrawal/distributions throughout the retirement period.  Unlike, online calculators, these spreadsheet calculators allow retirement savings to earn interest throughout (and before) retirement.  The benefits of the spreadsheet calculator are that it 1) requires only basic inputs and can be updated and applied at any point in time during the planning period, 2) allows for interest to accumulate before and after retirement, and 3) allows for additional monthly contributions up to retirement.  Further, these forecasted distribution amounts allow individuals to make arrangements for tax planning by providing estimates of taxable income and a schedule for the potential reinvestment of the excess distribution proceeds (see Benz, 2019).  In addition to extending the RMD age limit, the SECURE Act contained other adjustments as well, such as, changes in the categories of beneficiaries and the elimination of the “stretch” provision for some beneficiaries.  While  5 these changes are important, they are beyond the scope of this paper (see Berti, Hunt, and Johnson 2020).  In the next section, Excel spreadsheet calculators are developed and applied following the new rules of the SECURE Act for individuals who are retired and making RMD withdrawals and for individuals who are planning to retire in the future.  The subsequent section concludes the article.  EXCEL RMD SPREADSHEET CALCULATORS  The programming for the RMD spreadsheet calculators is not extensive, but can be tedious.  Consequently, the spreadsheet calculators can be downloaded at: https://scholarship.richmond.edu/finance-faculty-publications/XX/ The first spreadsheet calculator applies to anyone under the SECURE Act and is retired.  Further, the spreadsheet calculator is also applicable to anyone born before 6/1/1949 who is not affected by the SECURE Act.  In Table 2, the RMD schedule is displayed for someone born on 1/2/1940 and who currently has IRA savings of $450,000.  To produce the RMD schedule, the user needs to supply their date of birth, current IRA savings, and a monthly investment rate as an APR (note: this is the annual rate of return generated in the IRA).  The spreadsheet will adjust based on the current date (in this example, 12/16/2021). Table 2: Excel RMD Spreadsheet for SECURE Act and Being Currently Retired   A B C D E F G H I 1 Birthdate: 1/2/1940        2          3 Current IRA Savings: $450,000.00        4          5 Monthly Investment Rate: 3.5% APR       6          7  YEAR: AGE: IRA (Year End): RMD Based on FMV RMD Due:  AGE: DIVISOR:  6 (Previous Year End): 8  2021 81 $450,000.00 $24,726.23 12/31/2021  70 27.4 9  2022 82 $440,865.46 $24,896.13 12/31/2022  71 26.5 10  2023 83 $430,764.10 $25,519.59 12/31/2023  72 25.6 11  2024 84 $419,657.82 $26,144.81 12/31/2024  73 24.7 12  2025 85 $407,509.07 $26,588.72 12/31/2025  74 23.8 13  2026 86 $394,468.53 $27,015.63 12/31/2026  75 22.9 14  2027 87 $380,522.08 $27,421.86 12/31/2027  76 22.0 15  2028 88 $365,658.92 $27,803.17 12/31/2028  77 21.2 All values pertain to a current date of 12/16/2021, the spreadsheet is set to update based on the current day of the year (i.e. the =TODAY() function) and will only produce these values during December of 2021 “FMV” is “Fair Market Value”  CELL B8: =YEAR(TODAY()) Determines the current year  CELL B9: = B8 +1 Copy this cell down the column  CELL C8: = B8 – YEAR($B$1) Copy this cell down the column  CELL D8: = B3*(1 +B5/12)^(12 – MONTH(TODAY())) The IRA savings are appreciated until the end of the current year.   CELL D9 = (D8 – E8)*(1 + $B$5/12)^12 The IRA savings are appreciated until the end of the year assuming RMD payment in the previous year is made on December 31st. Copy this cell down the column  CELL E8: = (D8/(1 + B5/12)^12)/VLOOKUP(MIN(C8,115), $H$8:$I$53, 2, FALSE) The RMD is computed based on the IRA value at 12/31 the previous year  CELL E9: = (D8 – E8)/VLOOKUP(MIN(C9,115), $H$8:$I$53, 2, FALSE) Copy this cell down the column  CELL F9: = DATE(B8, 12, 31) Copy this cell down the column  CELLs H8 through I53 are based on Table III of IRS Publication 590-B  A copy of this spreadsheet is available at: https://scholarship.richmond.edu/finance-faculty-publications/XX/  Although not shown in the above table, the downloadable spreadsheet calculator displays RMDs for 50 years beyond the current year.  Under the current conditions in the table, the RMD will stay above $20,000 annually until the age of 101.  The second spreadsheet calculator applies to someone who is currently saving for retirement (current IRA savings of $157,000 and making monthly contributions of $600) and who is born on 6/1/1980 and intends to retire on 6/1/2045. To produce the RMD schedule, the user needs to supply their date of birth, current IRA savings, monthly IRA contribution, expected retirement date, and a monthly investment rate as an APR (note: this  7 is the annual rate of return generated in the IRA).  The spreadsheet will adjust based on the current date (in this example, 12/16/2021). Table 3: Excel RMD Spreadsheet for SECURE Act and Planning Retirement   A B C D E F G H I 1 Birthdate: 6/1/1980        2          3 Current IRA Savings: $157,000.00        4          5 Monthly Contribution: $600.00        6          7 Retirement Date: 6/1/2045        8          9 Monthly Investment Rate: 3.5% APR       10          11  YEAR: AGE: IRA (Year End): RMD Based on FMV (Previous Year End): RMD Due:  AGE: DIVISOR: 12  2051 71 $781,220.37 0   70 27.4 13  2052 72 $809,006.00 $30,516.42 4/1/2053  71 26.5 14  2053 73 $806,445.88 $31,517.80 12/31/2053  72 25.6 15  2054 74 $802,489.92 $32,560.00 12/31/2054  73 24.7 16  2055 75 $797,313.97 $33,621.39 12/31/2055  74 23.8 17  2056 76 $790,854.80 $34,713.30 12/31/2056  75 22.9 18  2057 77 $783,035.15 $35,667.05 12/31/2057  76 22.0 19  2058 78 $773,949.70 $36,816.16 12/31/2058  77 21.2 All values pertain to a current date of 12/16/2021, the spreadsheet is set to update based on the current day of the year (i.e. the =TODAY() function) and will only produce these values during December of 2021 “FMV” is “Fair Market Value”  CELL B12: =YEAR(B1) + 71 Helps determines the first RMD set on 12/31 of the year prior to being 72 years old and is due on April 1st of the year after turning 72 years old  CELL B13: = B12 +1 Copy this cell down the column  CELL C12: = B12 – YEAR($B$1) Copy this cell down the column  CELL D12: = FV(B9/12, (B12 – YEAR(TODAY()))*12+(12 – MONTH(TODAY())), , – B3 – PV(B9/12, (YEAR(B7) – YEAR(TODAY()))*12 + (12 – MONTH(B1)) – (12 – MONTH(B7)), – B5)) This formula is complicated because it appreciates the current IRA savings and the monthly contributions made to the IRA.  It is set to be valued for December 31 of the year when the retiree turns 71 (i.e. the year prior to turning 72).  CELL D13: = (D12)*(1 + B9/12)^12 The IRA savings accumulates monthly interest until December 31st of that year.  CELL D14: = (D13 – E13)*(1 + B9/12)^12 + E13*((1 + B9/12)^3 – 1) The IRA savings accumulates monthly interest until December 31st of that year.  The first RMD payment is assumed to be paid on 4/1 after accumulating 3 months of interest.  CELL D15: = (D14 – E14)*(1 + $B$9/12)^12 The IRA savings are appreciated until the end of the year assuming RMD payment in the previous year is made on December 31st. Cop this cell down the column  CELL E12: 0 No RMD is paid this year  CELL E13: = (D12)/VLOOKUP(MIN(C13,115), $H$12:$I$57, 2, FALSE) The RMD is computed based on the IRA value at 12/31 the previous year  CELL E14: = (D13 – E13)/VLOOKUP(MIN(C14,115), $H$12:$I$57, 2, FALSE) Copy this cell down the column   8 CELL F13: = DATE(B14, 4, 1) CELL F14: = DATE(B14, 12, 31) Copy this cell down the column  CELLs H12 through I57 are based on Table III of IRS Publication 590-B  A copy of this spreadsheet is available at: https://scholarship.richmond.edu/finance-faculty-publications/XX/  Similar to the previous calculator, the downloadable spreadsheet calculator displays RMDs for 50 years (i.e. until the age of 120 years old).  Under the current conditions in the table, the RMD will stay between $20,000 and $47,000 annually until the age of 106 years old.  The ability for planning with this spreadsheet calculator is very powerful.  One can determine the effects of increasing/decreasing monthly contributions, retiring earlier or later, or considering investments that may generate higher return over time because current conditions will not produce the desired situation at retirement.  Further, although not explored here, the calculator can provide an initial basis for retirement tax planning as well as amounts for potential conversion or rollover of RMDs into ROTH IRAs, 529 plans, or qualified charitable distributions (QCDs). CONCLUSION  RMD distributions are an important part of retirement planning even if only to avoid the significant penalty for not making such distributions.  The spreadsheet calculators provide a long horizon of annual RMDs for planning purposes and can be used for developing investment strategies prior to retirement (see also, Arnold, Earl, Marshall, and Schwartz, 2017 and 2018).      9 References Arnold, T., J. Earl, C. Marshall, and A. Schwartz. “Excel Calculators for Determining Retirement Accumulation and Disbursement Information.” Journal of Wealth Management, Vol. 20, No. 2 (2017), pp. 94-101.  Arnold, T., J. Earl, C. Marshall, and A. Schwartz. “Using ‘Equivalent Tax Rates’ to Determine Tax Efficient Retirement Investment and Withdrawal.” Journal of Wealth Management, Vol. 21, No. 2 (2018), pp. 55-69.  Beam Tacchino, K. “Thirty Examples Illustrating How the SECURE Act Will Enhance Retirement Plans and Retirement Planning.” Journal of Financial Service Professionals, Vol. 74, No. 2 (2020), pp. 5-13.  Benz, C. “Yes, RMDs Can Improve Your Portfolio.” www.morningstar.com/articles/888828/yes-rmds-can-improve-your-portfolio. (11/4/2019)  Berti, N., K. Hunt, T. Johnson. “SECURE Act Brings Major Changes to Retirement and Estate Planning.” Journal of Taxation of Investments, Vol. 37, No. 3 (2020), pp. 23-30.  IRS Publication 590-B available at www.irs.gov.    

Required Minimum Distribution (RMD) Spreadsheet Calculators  Based on the SECURE Act of 2019

https://scholarship.richmond.edu/cgi/viewcontent.cgi?article=1051&context=finance-faculty-publications

Required Minimum Distribution (RMD) Spreadsheet Calculators Based on the SECURE Act of 2019

Abstract

Similar works

Full text

Available Versions

University of Richmond