Hi, I am trying to populate column cells using an RMD table. In other words, I use the age 72 to start RMD's using the info from an RMD Table.

pmasse13

New Member
Joined
Jun 11, 2009
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
Hi,
I am trying to populate column cells using an RMD table.
In other words, I use the age 72 to start RMD's using the info from an RMD Table.
Starting in 2028.... You'll see below, that RMD's start showing up.
So, if I have someone provide me with the year they will turn 72, I want to correctly populate the correct cell value: =($B10/$L5), =($B11/$L6), etc...

I need to automate this process.
B
QUALIFIED - RollOver IRAContributionsConversionWithdrawalRMDReturnEnding Balance
2020$1,000,000.00$0.00$100,000.00$0.00$0.006.00%$954,000.00
2021$954,000.00$0.00$100,000.00$0.00$0.006.00%$905,240.00
2022$905,240.00$0.00$100,000.00$0.00$0.006.00%$853,554.40
2023$853,554.40$0.00$100,000.00$0.00$0.006.00%$798,767.66
2024$798,767.66$0.00$100,000.00$0.00$0.006.00%$740,693.72
2025$740,693.72$0.00$100,000.00$0.00$0.006.00%$679,135.35
2026$679,135.35$0.00$100,000.00$0.00$0.006.00%$613,883.47
2027$613,883.47$0.00$100,000.00$0.00$0.006.00%$544,716.48
2028$544,716.48$0.00$100,000.00$0.00$19,952.986.00%$450,249.30
2029$450,249.30$0.00$0.00$17,054.906.00%$459,186.07
2030$459,186.07$0.00$0.00$18,007.306.00%$467,649.50
2031$467,649.50$0.00$0.00$19,010.146.00%$475,557.72
2032$475,557.72$0.00$0.00$20,065.736.00%$482,821.51
2033$482,821.51$0.00$0.00$21,176.386.00%$489,343.83
2034$489,343.83$0.00$0.00$22,344.476.00%$495,019.33
2035$495,019.33$0.00$0.00$23,572.356.00%$499,733.80
2036$499,733.80$0.00$0.00$24,739.306.00%$503,494.17
2037$503,494.17$0.00$0.00$26,087.786.00%$506,050.78
2038$506,050.78$0.00$0.00$27,502.766.00%$507,260.90

L
Uniform Lifetime Table / Single Life Table
RMD Sch.Effective 2020Effective 2021
70.0027.429.1
71.0026.528.2
72.0025.627.3
73.0024.726.4
74.0023.825.5
75.0022.924.6
76.002223.7
77.0021.222.8
78.0020.321.9
79.0019.521
80.0018.720.2
81.0017.919.3
82.0017.118.4
83.0016.317.6
84.0015.516.8
85.0014.816
86.0014.115.2
87.0013.414.4

Thank you.
Phil
 
Hi Kirk,
Would you please tell me what: & " NLT" Means ? I mean the NLT Abbrev.

Thank You.
Phil
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Oh...No Later Than. The deadlines for the RMD's are no later than April 1 (for their first RMD) or December 31 for subsequent RMD's.
 
Upvote 0
Hi Kirk,
in:
G11:G51G11=IF(B11>=YEAR($C$5)-1,IF(ISNUMBER(I10),I10,C11)/VLOOKUP(A11,ULT!$A$4:$C$54,IF(B11<=ULT!B$3,2,3)),"")

what does: B11<=ULT!B$3,2,3, actually do ? I mean the: ULT!B$3, "2,3" ?

Am not familiar with this notation.
Thank you.
Phil
 
Upvote 0
what does: B11<=ULT!B$3,2,3, actually do ? I mean the: ULT!B$3, "2,3" ?
Are you familiar with the structure of the IF function in Excel?
If not, see here: MS Excel: How to use the IF Function (WS).

What it is basically saying is that if the value in cell B1 is less than or equal to the value of B3 on the "ULT" sheet, then return the value "2", otherwise return the value "3".
That is being used as the third argument of the VLOOKUP function, which tells it which column to return.
So it is either returning the value from the 2nd column ("B") or the 3rd column ("C"), based on that IF statement.
 
Upvote 0
Excellent, thank you....
How would I change the code to include additional columns ?
in other words, 2020, 2021, 2022, 2023, etc

Thank you
Phil
 
Upvote 0
How would I change the code to include additional columns ?
in other words, 2020, 2021, 2022, 2023, etc
If you want to return values from multiple columns, then you would need multiple formulas, one for each value you want to return.
Remember, any Excel formula can only return a value to the cell the formula is placed in. It cannot populate other cells outside of itself (i.e. you cannot place a formula in cell D1 and have it update cell E1).
 
Upvote 0
Thanks @Joe4 for explaining those details. Phil, if the IRS one day publishes revised lifetime expectancies, then some adjustments are needed. Let's say they begin to do that every two years beginning in 2024, so by 2026 we would have columns in the ULT table of 2020, 2021, 2024, and 2026. If you'd like to reconstruct what the RMD's should have been for someone in past years, as well as making estimates for upcoming years, then we need some way to tell the VLOOKUP function to return the life expectancy value from either column 2, 3, 4, or 5 (which would correspond to years 2020, 2021, 2024, and 2026...assuming we consider that Age is in column 1 of the ULT table), and the choice of column number depends on what is entered for "Today's Date" in cell B3, the particular year for our estimate (i.e., the row we are on in the table), and whether the person is responsible for making an RMD that year.

The IF formula I used is somewhat clunky, but if this were to be expanded, I would probably look at some other approach. For example, we could use:
Excel Formula:
=IFERROR(LOOKUP(B12,ULT!$B$4:$E$4,{2,3,4,5}),2)
in place of the IF statement.

Wrapping the function with IFERROR that returns 2 means that if the year in question is less than the earliest year (2020), then an error would result...so we have to instruct the function to return something. This may not be strictly correct since we refer to a ULT year that comes later than that shown in column B of the main table, but it also may not matter because RMD's are probably not due when that condition arises. To be safe, it is probably better to populate the ULT table with the earliest year that you think you'll ever need...and this error condition will never arise. Looking further into this approach, the LOOKUP part of this formula tries to find the year in the ULT column headings that matches the year in column B of the Main table. If it finds a match, the corresponding value in the second array is returned (again, these column numbers reflect what is described in the 1st paragraph and consider that Age is column number 1). If a match cannot be found, then the LOOKUP function matches the largest value in the lookup array that is less than or equal to the lookup value.

If we have a ULT table resembling this:
MrExcel20210422_RMD.xlsx
ABCDE
1Uniform Lifetime Table / Single Life Table
2Effective throughEffective beginning
3RMD Sch.2020202120242026
47027.429.13030.5
57126.528.22929.5
67225.627.32828.5
77324.726.42727.5
87423.825.52626.5
97522.924.62525.5
ULT


We can modify the formula in question to look like that shown in G11 and down to pull ULT information from the relevant column. I've added a column K to show which ULT column applies as we move through the years from row-to-row in column B:
MrExcel20210422_RMD.xlsx
ABCDEFGHIJK
1RMD CalculatorOther filePhil 2nd file.xlsx1st approach27.4
2Sheet nameULTs2nd approach#REF!
3Today's Date8/3/202027.4
4Acct Bal 12/31 prior yr100000at end of 2019RMD deadlines
5Date of Birth7/1/19494/1/20221st RMD for year 2021 NLT
6Age 70.5 on1/1/202012/31/20222nd RMD for year 2022 NLT
7Age 72 on7/1/202112/31of subsequent years
8
92019
10Age during yearQUALIFIED - RollOver IRAYr Beginning BalanceConversionWithdrawalReturnRMDEarnings RateYr Ending Balance*by NLTULT column after Year
11702019 $100,000.00 2
12712020$100,000.00 6.00%$100,000.00 2
13722021$100,000.00$3,663.006.00%$96,337.004/1/20223
14732022$96,337.00$3,649.136.00%$92,687.8712/31/20223
15742023$92,687.87$3,634.826.00%$89,053.0512/31/20233
16752024$89,053.05$3,562.126.00%$85,490.9312/31/20244
17762025$85,490.93$3,562.126.00%$81,928.8112/31/20254
18772026$81,928.81$3,486.336.00%$78,442.4712/31/20265
19782027$78,442.47$3,486.336.00%$74,956.1412/31/20275
20792028$74,956.14$3,486.336.00%$71,469.8112/31/20285
Main
Cell Formulas
RangeFormula
G1,E3G1='C:\Users\kirkr\Documents\@Home\Hobbies_Misc\ExcelStuff\[Phil 2nd file.xlsx]ULTs'!$B$4
G2G2=INDIRECT("'[" & E1 & "]" & E2 & "'!$B$4 ")
C4C4="at end of "&YEAR(B3)-1
C5C5=IF($B$5<DATE(1949,7,1),DATE(YEAR($B$6)+1,4,1),DATE(YEAR($B$7)+1,4,1))
D5D5="1st RMD for year " & YEAR(C5)-1 & " NLT"
C6C6=DATE(YEAR(C5),12,31)
D6D6="2nd RMD for year " & YEAR(C6) & " NLT"
B6B6=DATE(YEAR($B$5)+70,MONTH($B$5)+6,DAY($B$5))
B7B7=DATE(YEAR($B$5)+72,MONTH($B$5),DAY($B$5))
A11:A20A11=B11-YEAR($B$5)
B11B11=YEAR($B$3)-1
I11I11=B4
J11:J20J11=IF(B11=YEAR($C$5)-1,$C$5,IF(B11>=YEAR($C$5)-1,DATE(B11,12,31),""))
K11:K20K11=IFERROR(LOOKUP(B11,ULT!$B$3:$E$3,{2,3,4,5}),2)
I12:I20I12=SUM(C12,-SUM(D12:F12)*(1+H12),-SUM(G12))
B12:B20B12=B11+1
C12:C20C12=I11
G11:G20G11=IF(B11>=YEAR($C$5)-1,IF(ISNUMBER(I10),I10,C11)/VLOOKUP(A11,ULT!$A$4:$E$54, IFERROR(LOOKUP(B11,ULT!$B$3:$E$3,{2,3,4,5}),2) ),"")
 
Upvote 0
Hi Kirk,
Wow.... You are AWESOME....
Thank you so much for this awesome example.
Phil
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top