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]

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 IRA	Contributions	Conversion	Withdrawal	RMD	Return	Ending Balance
2020	$1,000,000.00	$0.00	$100,000.00	$0.00	$0.00	6.00%	$954,000.00
2021	$954,000.00	$0.00	$100,000.00	$0.00	$0.00	6.00%	$905,240.00
2022	$905,240.00	$0.00	$100,000.00	$0.00	$0.00	6.00%	$853,554.40
2023	$853,554.40	$0.00	$100,000.00	$0.00	$0.00	6.00%	$798,767.66
2024	$798,767.66	$0.00	$100,000.00	$0.00	$0.00	6.00%	$740,693.72
2025	$740,693.72	$0.00	$100,000.00	$0.00	$0.00	6.00%	$679,135.35
2026	$679,135.35	$0.00	$100,000.00	$0.00	$0.00	6.00%	$613,883.47
2027	$613,883.47	$0.00	$100,000.00	$0.00	$0.00	6.00%	$544,716.48
2028	$544,716.48	$0.00	$100,000.00	$0.00	$19,952.98	6.00%	$450,249.30
2029	$450,249.30	$0.00		$0.00	$17,054.90	6.00%	$459,186.07
2030	$459,186.07	$0.00		$0.00	$18,007.30	6.00%	$467,649.50
2031	$467,649.50	$0.00		$0.00	$19,010.14	6.00%	$475,557.72
2032	$475,557.72	$0.00		$0.00	$20,065.73	6.00%	$482,821.51
2033	$482,821.51	$0.00		$0.00	$21,176.38	6.00%	$489,343.83
2034	$489,343.83	$0.00		$0.00	$22,344.47	6.00%	$495,019.33
2035	$495,019.33	$0.00		$0.00	$23,572.35	6.00%	$499,733.80
2036	$499,733.80	$0.00		$0.00	$24,739.30	6.00%	$503,494.17
2037	$503,494.17	$0.00		$0.00	$26,087.78	6.00%	$506,050.78
2038	$506,050.78	$0.00		$0.00	$27,502.76	6.00%	$507,260.90

L

Uniform Lifetime Table / Single Life Table
RMD Sch.	Effective 2020	Effective 2021
70.00	27.4	29.1
71.00	26.5	28.2
72.00	25.6	27.3
73.00	24.7	26.4
74.00	23.8	25.5
75.00	22.9	24.6
76.00	22	23.7
77.00	21.2	22.8
78.00	20.3	21.9
79.00	19.5	21
80.00	18.7	20.2
81.00	17.9	19.3
82.00	17.1	18.4
83.00	16.3	17.6
84.00	15.5	16.8
85.00	14.8	16
86.00	14.1	15.2
87.00	13.4	14.4

Thank you.
Phil

 

[pmasse13]

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

Thank You.
Phil

 

[KRice]

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.

 

[pmasse13]

Excellent....
Thank you so much.
Phil

 

[pmasse13]

Hi Kirk,
in:

G11:G51

G11

=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

 

[Joe4]

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.

 

[pmasse13]

Excellent, thank you....
How would I change the code to include additional columns ?
in other words, 2020, 2021, 2022, 2023, etc

Thank you
Phil

 

[Joe4]

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).

 

[pmasse13]

Understood....
Thank you very much
Phil

 

[KRice]

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:

=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
	A	B	C	D	E
1	Uniform Lifetime Table / Single Life Table
2		Effective through	Effective beginning
3	RMD Sch.	2020	2021	2024	2026
4	70	27.4	29.1	30	30.5
5	71	26.5	28.2	29	29.5
6	72	25.6	27.3	28	28.5
7	73	24.7	26.4	27	27.5
8	74	23.8	25.5	26	26.5
9	75	22.9	24.6	25	25.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
	A	B	C	D	E	F	G	H	I	J	K
1	RMD Calculator			Other file	Phil 2nd file.xlsx	1st approach	27.4
2				Sheet name	ULTs	2nd approach	#REF!
3	Today's Date	8/3/2020			27.4
4	Acct Bal 12/31 prior yr	100000	at end of 2019	RMD deadlines
5	Date of Birth	7/1/1949	4/1/2022	1st RMD for year 2021 NLT
6	Age 70.5 on	1/1/2020	12/31/2022	2nd RMD for year 2022 NLT
7	Age 72 on	7/1/2021	12/31	of subsequent years
8
9		2019
10	Age during year	QUALIFIED - RollOver IRA	Yr Beginning Balance	Conversion	Withdrawal	Return	RMD	Earnings Rate	Yr Ending Balance*	by NLT	ULT column after Year
11	70	2019							$100,000.00		2
12	71	2020	$100,000.00					6.00%	$100,000.00		2
13	72	2021	$100,000.00				$3,663.00	6.00%	$96,337.00	4/1/2022	3
14	73	2022	$96,337.00				$3,649.13	6.00%	$92,687.87	12/31/2022	3
15	74	2023	$92,687.87				$3,634.82	6.00%	$89,053.05	12/31/2023	3
16	75	2024	$89,053.05				$3,562.12	6.00%	$85,490.93	12/31/2024	4
17	76	2025	$85,490.93				$3,562.12	6.00%	$81,928.81	12/31/2025	4
18	77	2026	$81,928.81				$3,486.33	6.00%	$78,442.47	12/31/2026	5
19	78	2027	$78,442.47				$3,486.33	6.00%	$74,956.14	12/31/2027	5
20	79	2028	$74,956.14				$3,486.33	6.00%	$71,469.81	12/31/2028	5
Main

Cell Formulas
Range	Formula
G1,E3	G1	='C:\Users\kirkr\Documents\@Home\Hobbies_Misc\ExcelStuff\[Phil 2nd file.xlsx]ULTs'!$B$4
G2	G2	=INDIRECT("'[" & E1 & "]" & E2 & "'!$B$4 ")
C4	C4	="at end of "&YEAR(B3)-1
C5	C5	=IF($B$5<DATE(1949,7,1),DATE(YEAR($B$6)+1,4,1),DATE(YEAR($B$7)+1,4,1))
D5	D5	="1st RMD for year " & YEAR(C5)-1 & " NLT"
C6	C6	=DATE(YEAR(C5),12,31)
D6	D6	="2nd RMD for year " & YEAR(C6) & " NLT"
B6	B6	=DATE(YEAR($B$5)+70,MONTH($B$5)+6,DAY($B$5))
B7	B7	=DATE(YEAR($B$5)+72,MONTH($B$5),DAY($B$5))
A11:A20	A11	=B11-YEAR($B$5)
B11	B11	=YEAR($B$3)-1
I11	I11	=B4
J11:J20	J11	=IF(B11=YEAR($C$5)-1,$C$5,IF(B11>=YEAR($C$5)-1,DATE(B11,12,31),""))
K11:K20	K11	=IFERROR(LOOKUP(B11,ULT!$B$3:$E$3,{2,3,4,5}),2)
I12:I20	I12	=SUM(C12,-SUM(D12:F12)*(1+H12),-SUM(G12))
B12:B20	B12	=B11+1
C12:C20	C12	=I11
G11:G20	G11	=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) ),"")

 

[pmasse13]

Hi Kirk,
Wow.... You are AWESOME....
Thank you so much for this awesome example.
Phil