Required Minimum Distribution (RMD)

You can tell that I am running out of ideas to create new spreadsheets. I’m back to creating tax calculators. This one is concerned with the required minimum distribution (RMD) that everyone with an IRA or 401K will some day need to use. When you reach 70 1/2 (not 70 years old but 70 1/2 years old) the IRS requires you to start taking out a portion of your retirement savings that were tax deductible when you invested the funds. I don’t expect a lot of excitement over this entry. After all, how many 70 year olds are sitting around waiting for an Excel spreadsheet in their retirement, or even have Excel.

The RMD tables and calculations are simple. The factor needed comes off one IRS table, unless you have a spouse that is more than 10 years younger than you and is the sole beneficiary. Then the factor you need comes off a different table. The first year you must start the RMD can be confusing, because of the 70 1/2 year old rule. If your birthday is before July 1st of the year you turn 70, your have to start the RMD that year. If you were born after June 30th, you can wait until the next tax year.

All you need for input to this calculator is the value of all your IRAs and 401Ks on the last day of the previous year, your birthday, and the birthday of your spouse if he/she is more than 10 years younger than you and the sole beneficiary. I use the spouse’s birthday just to make sure of the 10 year difference. You don’t need your spouse’s birthday unless you answer YES to both the 10 years younger and beneficiary questions. If you do, a new box appears asking for the spouse’s birthday. After downloading the spreadsheet, select the RMD sheet:

The spreadsheet opens to a big disclaimer sheet. I am not a tax guru. I do my taxes with Turbo Tax. Always consult with your tax consultant on any of my calculators.

 

Download RMD

 

 

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

18 Comments

  1. I think a lot more 70-year-olds use Excel than you might think! I am only 64 but my first career was very technical (not finance) and I use Excel a lot.

    Also, ISTM that the spreadsheet setup assumes the user is already 70 and will need to calculate RMD beginning right away. Maybe I didn’t work it right, but when I entered into the yellow cells, the table gave me no results at all. Only when I lied to it and entered a birth-date in 1948 (rather the the true 1955) did it fill in the table. Is there a way to let it calculate balances accumulating before RMD kicks in?

    That said, this is a LOT more useful than the Schwab or Vanguard online calculators I have been using! Great work, and thank you!

    1. Donald,
      Thank you for your comment. The RMD program is only set up to calculate your RMD at various stages after 70 1/2. The only way to use it before you are 70 1/2 is to estimate the size of your 401k/IRA when you become 70 1/2 and change your birthday to the date you will become 70 1/2. If it is helpful, I have another excel file online that has a future value calculator.

      http://pistulka.com/Other/?p=2132

      From the contents sheet, column A, number 11 is a link to the future value calculator. Enter the present value of your IRA, along with current and future dates to calculate the value of the IRA when you are 70 1/2.

      Good Luck
      Don

  2. Don I have an interesting one I’d like to share with you in hopes you could help me figure out the calculations with the RMDs. The catch is their IRA growth rates change to preset % on preset dates. Could I perhaps somehow send you the illustration depicting this?
    Thanks in advance-
    Matt Costello
    952/334-0624

  3. This is great. I have one question? I will be 73 next February but plan on working a few more years yet. Is there a way to have the calculator begin in the actual year of my retirement, say 2023 for example?

    Thank you again,

    Lance.

    1. Lance,
      It depends on what you are trying to adjust. If you want to restart the “RMD vs. Earnings” table to the age of 77 from 73, change your birthday year from 1946 to 1942. The year will still start on 12/31/2018, so you can override that date by typing in 12/31/2023 in cell N8. You will need to estimate the value of your IRA in four years. I hope that helps.
      Don

    1. Hi Brian,
      No it doesn’t. I am not a tax consultant, so I unless someone brings to my attention that the rules have changed, it remains the same. Is there a change that you know of?
      Don

  4. Would love to have an email conversation with you about incorporating some of your work into our work helping people increase their charitable giving. Please write me if you are interested.

  5. Thank you so much for the effort you have put in creating these helpful spreadsheets. Would you have one that could be used for Inherited IRA’s, when the beneficiary is under the age of 70 1/2?

    Thank you
    Kelly Jo

    1. Hi Kelly Jo,
      Sorry, but I do not have any spreadsheets for inherited IRS’s. I put together the one on my blog when I turned 70 1/2 and thought it might help others.
      Don

  6. This was extremely helpful. I could have typed the IRS factors into an Excel sheet but was pretty sure some thoughtful soul would have already done it (I had already built the “add a ROA and subtract a distribution and use the FYE amount for the next year” model so all I really needed was the IRS factors in an Excel column already). Thank you!!! And I’m sure this entire model has helped countless others who don’t really know how to build models in Excel. Cheers

Leave a Reply

Your email address will not be published. Required fields are marked *