Required Minimum Distribution (RMD) Spreadsheet Calculators 
Based on the SECURE Act of 2022
The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2022 made a second round of changes (relative to the SECURE Act of 2019) to the required minimum distribution (RMD) schedule for individual retirement accounts (IRAs) and defined contribution retirement plans. Excel spreadsheet calculators are developed to calculate the new annual RMD cash flows throughout retirement for those who are retired and for those who are planning to retire.  The spreadsheet calculators also allow savings to accrue with interest if the RMD is in excess of expected annual costs.
KEY TAKEAWAYS:
The spreadsheet calculators require only basic inputs and can be updated and applied at any point in time during the planning period.
The spreadsheet calculators allow for interest to accumulate before and after retirement in the IRA and in a savings account if the RMD is in excess of expected annual costs.
The spreadsheet calculators allow for additional monthly contributions up to retirement

Arnold, Tom

Earl, Jr., John H

Marshall, Cassandra D.

English

University of Richmond

University of Richmond UR Scholarship Repository Finance Faculty Publications Finance 9-25-2023 Required Minimum Distribution (RMD) Spreadsheet Calculators Based on the SECURE Act of 2022 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, John H. Earl, Jr, and Cassandra D. Marshall. “Required Minimum Distribution (RMD) Spreadsheet Calculators Based on the SECURE Act of 2022. University of Richmond, (September, 2023): 1-13. This Working Paper 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 2022    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  KEY TAKEAWAYS:  The spreadsheet calculators require only basic inputs and can be updated and applied at any point in time during the planning period.  The spreadsheet calculators allow for interest to accumulate before and after retirement in the IRA and in a savings account if the RMD is in excess of expected annual costs.   The spreadsheet calculators allow for additional monthly contributions up to retirement.   Please do not cite without permission.  September 25, 2023   1 Required Minimum Distribution (RMD) Spreadsheet Calculators  Based on the SECURE Act of 2022  The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2022 made a second round of changes (relative to the SECURE Act of 2019) to the required minimum distribution (RMD) schedule for individual retirement accounts (IRAs) and defined contribution retirement plans. Excel spreadsheet calculators are developed to calculate the new annual RMD cash flows throughout retirement for those who are retired and for those who are planning to retire.  The spreadsheet calculators also allow savings to accrue with interest if the RMD is in excess of expected annual costs.        2 INTRODUCTION  The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2022 (SECURE 2.0) builds upon changes already implemented in the SECURE Act of 2019.  The focus of this article is to update “required minimum distribution” (RMD) calculators that were produced by Arnold, Earl, and Marshall (2022) and to provide information about some additional changes in SECURE 2.0.  It is important for retirees and those planning for retirement to understand their RMD withdrawal schedule because these required disbursements will dictate how much of their retirement savings must be distributed and subsequently taxed in any given year. The specific RMD amount varies based on a combination of the retirement account balance and the owner’s remaining life expectancy as determined by IRS estimates.  In addition to planning for the tax consequences of the distribution, the individual will also need to consider the RMD amount relative to their projected living expense needs during each year of their retirement (i.e., Annual Retirement Expense).  If the retiree needs the full RMD to cover part or all of the projected Annual Retirement Expense, then the full distribution will be exhausted and may need to be further supplemented from other retirement accounts or savings.  However, in the event that the entire RMD after-tax is not needed to cover Annual Retirement Expenses, the individual will then have the opportunity to save the excess after-tax proceeds in a savings account that will earn interest and subsidize future retirement expenses when needed.    This article updates spreadsheet calculators produced by Arnold, Earl, and Marshall (2022) to incorporate changes from SECURE 2.0.  These calculators still require only basic inputs and can be applied at any point in time during the planning period.  The spreadsheet  3 calculators allow for interest to accumulate before and after retirement in the retirement account.  They also allow for additional monthly contributions up to the retirement date.  In addition, the new version of the spreadsheet calculators also includes a Savings Account feature that allows the retiree to accumulate RMD funds in excess of the Annual Retirement Expense in an interest-bearing account to be used once the retirement account balance falls to zero.    In the next section, key changes and dates from SECURE 2.0 are discussed as they pertain to the features of the spreadsheet calculators.  Next, updated Microsoft Excel spreadsheet calculators are developed which apply both to individuals who are already retired and also those who are still working and saving for retirement.  The final section concludes the article.  IMPORTANT DATES FOR RMD AMOUNTS UNDER SECURE 2.0  Under SECURE 2.0, the RMD starts at age 73 if you are born between January 1, 1951 and December 31, 1959.  The RMD process is: • Turn 73 years old in Year X, RMD distribution is determined based on the fund value on December 31of Year (X – 1) • The RMD distribution needs to occur by April 1 of Year (X +1) • Turn 74 years old in Year (X + 1), RMD distribution is determined based on the fund value on December 31of Year X • The RMD distribution needs to occur by December 31 of Year (X +1)  4 • NOTE: Two RMD distributions are due in Year (X + 1), however, one can still choose to distribute the first RMD in Year X to avoid two RMD distributions in Year (X + 1) If you are born January 1, 1960 or later, the RMD starts at age 75 in a similar manner:  • Turn 75 years old in Year Y, RMD distribution is determined based on the fund value on December 31of Year (Y – 1) • The RMD distribution needs to occur by April 1 of Year (Y + 1) • Turn 76 years old in Year (Y + 1), RMD distribution is determined based on the fund value on December 31of Year Y • The RMD distribution needs to occur by December 31 of Year (Y + 1) • NOTE: Two RMD distributions are due in Year (Y + 1) however, one can still choose to distribute the first RMD in Year Y to avoid two RMD distributions in Year (Y + 1) A birth year prior to 1950 already has previous RMD rules in place.  The updated RMD calculators are adjusted for the changes in SECURE 2.0 and for the 2022 Age-Based Distribution Periods needed for computing RMDs (see Table 1) 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: 72 27.4 88 13.7 104 4.9 73 26.5 89 12.9 105 4.6 74 25.5 90 12.2 106 4.3 75 24.6 91 11.5 107 4.1 76 23.7 92 10.8 108 3.9 77 22.9 93 10.1 109 3.7 78 22.0 94 9.5 110 3.5 79 21.1 95 8.9 111 3.4 80 20.2 96 8.4 112 3.3  5 81 19.4 97 7.8 113 3.1 82 18.5 98 7.3 114 3.0 83 17.7 99 6.8 115 2.9 84 16.8 100 6.4 116 2.8 85 16.0 101 6.0 117 2.7 86 15.2 102 5.6 118 2.5 87 14.4 103 5.2 119 2.3     120 +  2.0 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.   Further, the updated RMD calculators include a growing annual expense that is assumed tax-deductible.  The IRA will have money withdrawn to pay the tax-deductible expense.  If the tax-deductible expense withdrawal is not in excess of the RMD due, the excess money will accumulate in a savings account net of taxes.  The updated RMD calculators are developed in Excel for someone who is retired and for someone who is planning to retire and are presented in the next section.  The subsequent section concludes the article.  EXCEL RMD SPREADSHEET CALCULATORS  The programming for the RMD spreadsheet calculators can be downloaded at: https://scholarship.richmond.edu/finance-faculty-publications/XX/ The first RMD spreadsheet calculator applies to anyone who is retired.  The new SECURE 2.0 RMD changes to when RMDs start does not really apply.  However, the RMD schedule has changed since last year (see Table 1).  Compared to the associated RMD spreadsheet  6 calculator of Arnold, Earl, and Marshall (2022), an annual growing tax-deductible expense has been added which can offset part or all of the RMD for a given year.  If the RMD is in excess of the tax-deductible expense, the excess amount is accumulated into a savings account net of taxes.  If the annual tax-deductible expense is greater than the RMD, the IRA is reduced by the expense and the savings account only collects after-tax interest from the balance from the previous year.  The retiree is out of money when the savings account goes to zero or a negative value (see Table 2).  All RMD amounts are due by December 31st of the associated year. Table 2: Excel RMD Spreadsheet for SECURE 2.0 and Being Currently Retired   A B C D E F G 1 Birthdate: 1/2/1940      2        3 Current IRA Savings: $450,000.00   TODAY: 6/30/2023  4        5 Annual Retirement Expense: $50,000.00      6 Annual Expense Growth Rate: 2.00%      7 Annual Tax Rate: 35.00%      8        9 Monthly Investment Rate: 3.5% APR     10        11  YEAR: AGE: IRA  (Year End): Annual Expense: RMD Due: (based on FMV at previous year end) Savings: 12  2023 83 $457,932.65 $50,000.00 $24,983.32 $0.00 13  2024 84 $422,441.57 $51,009.22 $24,281.71 $0.00 14  2025 85 $384,643.07 $52,038.81 $23,214.52 $0.00 15  2026 86 $344,433.98 $53,089.18 $21,881.86 $0.00 16  2027 87 $301,707.05 $54,160.75 $20,232.28 $0.00 17  2028 88 $256,350.77 $55,253.95 $18,069.07 $0.00 18  2029 89 $208,249.23 $56,369.21 $15,588.90 $0.00 19  2030 90 $157,231.93 $57,506.99 $12,449.18 $0.00  Spreadsheet continues  11 H I J K L M N 12  AGE: DIVISOR:     13  72 27.4     14  73 26.5     15  74 25.5     16  75 24.6     17  76 23.7     18  77 22.9     19  78 22.0     All values pertain to a current date of 6/30/2023, the spreadsheet is set to update based on the current day of the year, CELL F3: =TODAY()  “FMV” is “Fair Market Value” and assume the annual expense is tax-deductible   7 A copy of this spreadsheet is available at: https://scholarship.richmond.edu/finance-faculty-publications/XX/  Although not shown in the above table, the savings account will expire (i.e. become negative) after 2031 (age of 91).  Depending on the retiree’s situation, this may be an issue that requires planning.  If the IRA needs to last until age 95, use the “Goal Seek” function in Excel to set the cell associated with savings at the age of 95 (cell G24) to zero by changing the “Annual Retirement Expense” cell (cell B5).  After some iterations, the annual retirement expense is reduced from $50,000.00 to $38,375.19.  Consequently, if the annual retirement expense can be reduced to $38,375.19 while still growing annually at 2.00%, the IRA will be able to fully fund this retirement.   The second spreadsheet calculator applies to someone who is currently saving for retirement (current IRA savings of $250,000 and making monthly contributions of $1,500 with $60,000 of annual expenses expected at retirement) and who is born on 6/1/1980 and intends to retire on 6/1/2047 (see table 3). Table 3: Excel RMD Spreadsheet for SECURE 2.0 and Planning Retirement   A B C D E F G 1 Birthdate: 6/1/1980      2        3 Current IRA Savings: $250,000.00   TODAY: 6/30/2023  4        5 Monthly Contribution: $1,500.00      6        7 Retirement Date: 6/1/2047      8 Months to Retirement: 288      9 Year of First RMD: 2055      10 IRA at Retirement: $1,253,917.31      11 Annual Retirement Expense: $60,000.00      12 Annual Expense Growth Rate: 2.00%      13 Annual Tax Rate: 35.00%      14        15 Monthly Investment Rate: 3.5% APR     16        17  YEAR: AGE: IRA  (Year End): Annual Expense: RMD Due: (based on FMV at Savings:  8 previous year end) 18  2047 67 $1,276,021.49 $30,000.00 $0.00 $0.00 19  2048 68 $1,290,338.68 $61,211.06 $0.00 $0.00 20  2049 69 $1,272,843.94 $62,446.57 $0.00 $0.00 21  2050 70 $1,253,843.94 $63,707.01 $0.00 $0.00 22  2051 71 $1,232,055.95 $64,992.90 $0.00 $0.00 23  2052 72 $1,208,571.93 $66,304.74 $0.00 $0.00 24  2053 73 $1,182,894.16 $67,643.05 $0.00 $0.00 25  2054 74 $1,154,917.19 $69,008.39 $0.00 $0.00 26  2055 75 $1,124,531.27 $70,401.28 $0.00 $0.00 27  2056 76 $1,091,622.19 $71,822.28 $88,620.69 $10,918.97 28  2057 77 $1,038,675.20 $73,271.97 $43,799.19 $11,169.98  Spreadsheet continues  18 H I J K L M N 19  AGE: DIVISOR:     21  72 27.4     22  73 26.5     23  74 25.5     24  75 24.6     25  76 23.7     26  77 22.9     27  78 22.0     28  79 21.1     All values pertain to a current date of 6/30/2023, the spreadsheet is set to update based on the current day of the year, CELL F3: =TODAY()  “FMV” is “Fair Market Value” and assume the annual expense is tax-deductible  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 beyond 100 years of age.  Under the current conditions in the table, the IRA and savings will last until 91 years old.  From a planning perspective, increasing the monthly contribution by $300.00 to $1,800.00 will allow the IRA and savings to last until age 95.  If the desire is to plan to live to 100 years old and to keep the monthly contribution at $1,500.00, use the Goal Seek function to set savings to zero at age 100 (CELL G51) by changing the annual expense at retirement (CELL B11).  Annual expenses at retirement will need to be reduced to $47,841.06 initially while growing at 2.0% annually to accommodate living to 100 years old.  Performing the same iteration with Goal Seek while assuming a $1,800.00 monthly contribution, changes the initially annual expense at retirement to $52,995.82.  9 As a note of caution, one should be aware of the annual limits for IRA contributions when performing these types of planning analyses.  That being stated, other savings vehicles can be employed to make up for when an IRA fails to meet a planning horizon.  Further the IRA investment mix can be altered to target higher return generation, but one needs to be fully aware of the additional risk when using this strategy.  For example, if the monthly investment rate of 3.50% APR in Table 3 is increased to 5.00% APR, the IRA and savings will last until 115 years of age.  As desirable as this may be, one still has to consider the additional risk that will exist. CONCLUSION  The RMD spreadsheet calculators developed in the previous section allow for retirement planning that incorporates RMDs and the funds/savings that accrue on an after-tax basis due to RMDs exceeding annual retirement expenses based on SECURE 2.0.  Although the calculators do not provide a full assessment of retirement planning because there is no incorporation of risks and other funding sources, the user does receive valuable information about what the IRA can provide during retirement and can then make appropriate planning changes.     10 APPENDIX Cell formulas for Table 2: CELL B12: =YEAR(F2)  CELL B13: = B12 +1 Copy this cell down the column  CELL C12: = B12 – YEAR($B$1) Copy this cell down the column  CELL D12: = B3*(1 + B9/12)^(12 – MONTH(F3))  CELL D13: = MAX((D12 – MAX(E12, F12))*(1 + $B$9/12)^12, 0) The IRA is reduced by the larger of the RMD or the annual tax-deductible expense from the previous year.  It is the appreciated by the monthly investment rate.  If the value becomes negative, it is set to zero.  Copy this cell down the column  CELL E12: = B5  CELL E13: = E12*(1 + $B$6/12)^12 The annual expense appreciates on a monthly basis Copy this cell down the column  CELL F12: =D12/(1 + $B$(/12)^12)/VLOOKUP(MIN(C12, 120), $I$12:$J$60, 2, FALSE) The RMD is computed based on the IRA value at the end of the previous year Copy this cell down the column  CELL G12: =IF(F12 > E12, (F12 – E12)*(1 – B7),0) Savings occurs on an after-tax basis if the RMD is greater than the annual expense  CELL G13: =G12*(1 + $B$9*(1 - $B$7)/12)^12 + IF(F13 > E13, (F13 – E13)*(1 - $B$7), 0) – IF(D13 < MAX(E13, F13), MAX(E13, F13) – D13, 0) Savings accumulates on an after-tax basis from the previous year The savings account can increase on an after-tax basis if the RMD exceeds the annual expense The savings account reduces if the annual expense is higher than the IRA balance…it can go negative indicating the IRA cannot support additional years of retirement Copy this cell down the column   CELLs I12 through J60 are based on Table III of IRS Publication 590-B  Cell formulas for Table 2:  11 CELL B8: =MONTH(B7) + 12*(YEAR(B7) – YEAR(F3) – 1) + MONTH(F3)  CELL B9: YEAR(B1) + IF(YEAR(B1) >= 1960, 75,73)  CELL B10: =FV(B15/12, B8, -B5, -B3)  CELL B18: =YEAR(B7)  CELL B19: = B18 + 1 Copy this cell down the column  CELL C18: =B18 – YEAR($B$1) Copy this cell down the column  CELL D18: =B10*(1 + B15/12)^(12 – MONTH(B7))  CELL D19: =MAX((D18 – MAX(E18, F18)*(1 + $B$15/12)^12, 0) The previous year-end balance of the IRA is reduced by the larger of the previous year RMD or previous year annual expense and accrues interest, or set to zero if the previous year annual expense (in this scenario, the annual expense will be larger than the associated RMD) is larger than the previous year-end balance of the IRA Copy this cell down the column  CELL E18: =(12 – MONTH(B7))*B11/12  CELL E19: =$B$11*(1 + $B$12/12)^(12*(B19 – $B$18))  CELL F18: =MAX(IF(B18 = ($B$9 + 1), (D16 – E16) / VLOOKUP(C17, $I$18:$J$66, 2, FALSE) +  (D17 – E17) / VLOOKUP(C18, $I$18:$J$66, 2, FALSE), IF(B18 < ($B$9 + 1), 0, D17 – MAX(E17,F17)) / VLOOKUP(MIN(C18, 120), $I$18:$J$66, 2, FALSE))),0) Computes RMD each year and is adjusted to have both RMDs for ages 75 and 76 combined (RMD associated with 75 is due by April 1st and the RMD associated with age 76 is due by December 31st in the same year) If the IRA runs out of money, the RMD is set to zero Copy this cell down the column  CELL G18: set to 0  CELL G19: =IF(B19 >= ($B$9 + 1), G18*(1 – $B$13)*$B$15/12)^12 + IF(F19 > E19, (F19 – E19)*(1 – $B$13), 0), 0) – IF(D19 < MAX(E19,F19), MAX(E19,F19) – D19,0) The savings account will increase on an after-tax basis by any excess of RMD relative to the annual expense and accrue after-tax interest on any previous balance If the IRA does not have enough money to cover the annual expense, the savings account will pay off whatever annual expense remains until the savings account balance goes negative  12 Copy this cell down the column  CELLs I18 through J66 are based on Table III of IRS Publication 590-B    13  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.  IRS Publication 590-B available at www.irs.gov.    

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

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

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

Abstract

Similar works

Full text

Available Versions

University of Richmond