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

 

[KRice]

Have a look at this and see if it does what you expect. I didn't realize the IRS issued a new ULT table for 2021. My understanding is that if someone's birth date is prior to July 1, 1949, then they were subject to requirements prior to the SECURE Act. But I'm assuming that the new ULT table would still apply to them for RMD's in 2021 and beyond.

This sheet requires inputs in the upper left blue-shaded cells. The dates at which the individual turns 70.5 and 72 years of age are computed, and then the SECURE Act age requirement is evaluated (green cells) to determine the RMD deadlines for the first two RMD's. Information from this upper block is used as inputs for the year-by-year table below, where: 1) the first Year in the table corresponds to the year for which the end-of-year account balance was given in B4; and 2) the end-of-year account balance is copied into I11. I've deleted the user entries for Conversion, Withdrawal, and Return, but the table computations should work okay with them (I'm assuming that all of these values are positive, and the account balance calculation subtracts those values). The RMD formula performs a lookup of the ULT on a separate worksheet and pulls in the life expectancy corresponding to the person's age during that year, as well as determining whether the IRS's 2020 or 2021 ULT list should be used.

You should be able to click on clipboard icon in the upper left corner of each of these working examples (at the intersection of row and columns headings), and then open a blank worksheet in Excel, select the same cell as shown in the upper left of the example (cell A1 in both cases here) and paste. You will have to rename the sheet holding the ULT table as "ULT" so that the formula finds it.

MrExcel20210422_RMD.xlsx
	A	B	C	D	E	F	G	H	I	J
1	RMD Calculator
2
3	Today's Date	4/22/2021
4	Acct Bal 12/31 prior yr	100000	at end of 2020	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
11	71	2020							$100,000.00
12	72	2021	$100,000.00				$3,663.00	6.00%	$96,337.00	4/1/2022
13	73	2022	$96,337.00				$3,649.13	6.00%	$92,687.87	12/31/2022
14	74	2023	$92,687.87				$3,634.82	6.00%	$89,053.05	12/31/2023
15	75	2024	$89,053.05				$3,620.04	6.00%	$85,433.01	12/31/2024
16	76	2025	$85,433.01				$3,604.77	6.00%	$81,828.24	12/31/2025
17	77	2026	$81,828.24				$3,588.96	6.00%	$78,239.28	12/31/2026
18	78	2027	$78,239.28				$3,572.57	6.00%	$74,666.71	12/31/2027
19	79	2028	$74,666.71				$3,555.56	6.00%	$71,111.15	12/31/2028
20	80	2029	$71,111.15				$3,520.35	6.00%	$67,590.80	12/31/2029
21	81	2030	$67,590.80				$3,502.11	6.00%	$64,088.69	12/31/2030
22	82	2031	$64,088.69				$3,483.08	6.00%	$60,605.60	12/31/2031
23	83	2032	$60,605.60				$3,443.50	6.00%	$57,162.10	12/31/2032
24	84	2033	$57,162.10				$3,402.51	6.00%	$53,759.60	12/31/2033
25	85	2034	$53,759.60				$3,359.97	6.00%	$50,399.62	12/31/2034
26	86	2035	$50,399.62				$3,315.76	6.00%	$47,083.86	12/31/2035
27	87	2036	$47,083.86				$3,269.71	6.00%	$43,814.15	12/31/2036
28	88	2037	$43,814.15				$3,221.63	6.00%	$40,592.52	12/31/2037
29	89	2038	$40,592.52				$3,146.71	6.00%	$37,445.81	12/31/2038
30	90	2039	$37,445.81				$3,094.70	6.00%	$34,351.12	12/31/2039
31	91	2040	$34,351.12				$3,013.26	6.00%	$31,337.86	12/31/2040
32	92	2041	$31,337.86				$2,901.65	6.00%	$28,436.21	12/31/2041
33	93	2042	$28,436.21				$2,815.47	6.00%	$25,620.74	12/31/2042
34	94	2043	$25,620.74				$2,696.92	6.00%	$22,923.82	12/31/2043
35	95	2044	$22,923.82				$2,575.71	6.00%	$20,348.11	12/31/2044
36	96	2045	$20,348.11				$2,451.58	6.00%	$17,896.53	12/31/2045
37	97	2046	$17,896.53				$2,294.43	6.00%	$15,602.10	12/31/2046
38	98	2047	$15,602.10				$2,137.27	6.00%	$13,464.83	12/31/2047
39	99	2048	$13,464.83				$1,980.12	6.00%	$11,484.71	12/31/2048
40	100	2049	$11,484.71				$1,794.49	6.00%	$9,690.22	12/31/2049
41	101	2050	$9,690.22				$1,642.41	6.00%	$8,047.81	12/31/2050
42	102	2051	$8,047.81				$1,437.11	6.00%	$6,610.70	12/31/2051
43	103	2052	$6,610.70				$1,271.29	6.00%	$5,339.41	12/31/2052
44	104	2053	$5,339.41				$1,089.68	6.00%	$4,249.74	12/31/2053
45	105	2054	$4,249.74				$923.86	6.00%	$3,325.88	12/31/2054
46	106	2055	$3,325.88				$773.46	6.00%	$2,552.42	12/31/2055
47	107	2056	$2,552.42				$622.54	6.00%	$1,929.88	12/31/2056
48	108	2057	$1,929.88				$494.84	6.00%	$1,435.04	12/31/2057
49	109	2058	$1,435.04				$387.85	6.00%	$1,047.19	12/31/2058
50	110	2059	$1,047.19				$299.20	6.00%	$747.99	12/31/2059
51	111	2060	$747.99				$220.00	6.00%	$527.99	12/31/2060
Main

Cell Formulas
Range	Formula
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:A51	A11	=B11-YEAR($B$5)
B11	B11	=YEAR($B$3)-1
I11	I11	=B4
J11:J51	J11	=IF(B11=YEAR($C$5)-1,$C$5,IF(B11>=YEAR($C$5)-1,DATE(B11,12,31),""))
I12:I51	I12	=SUM(C12,-SUM(D12:F12)*(1+H12),-SUM(G12))
B12:B51	B12	=B11+1
C12:C51	C12	=I11
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)),"")

MrExcel20210422_RMD.xlsx
	A	B	C
1	Uniform Lifetime Table / Single Life Table
2		Effective through	Effective beginning
3	RMD Sch.	2020	2021
4	70	27.4	29.1
5	71	26.5	28.2
6	72	25.6	27.3
7	73	24.7	26.4
8	74	23.8	25.5
9	75	22.9	24.6
10	76	22	23.7
11	77	21.2	22.8
12	78	20.3	21.9
13	79	19.5	21
14	80	18.7	20.2
15	81	17.9	19.3
16	82	17.1	18.4
17	83	16.3	17.6
18	84	15.5	16.8
19	85	14.8	16
20	86	14.1	15.2
21	87	13.4	14.4
22	88	12.7	13.6
23	89	12	12.9
24	90	11.4	12.1
25	91	10.8	11.4
26	92	10.2	10.8
27	93	9.6	10.1
28	94	9.1	9.5
29	95	8.6	8.9
30	96	8.1	8.3
31	97	7.6	7.8
32	98	7.1	7.3
33	99	6.7	6.8
34	100	6.3	6.4
35	101	5.9	5.9
36	102	5.5	5.6
37	103	5.2	5.2
38	104	4.9	4.9
39	105	4.5	4.6
40	106	4.2	4.3
41	107	3.9	4.1
42	108	3.7	3.9
43	109	3.4	3.7
44	110	3.1	3.5
45	111	2.9	3.4
46	112	2.6	3.2
47	113	2.4	3.1
48	114	2.1	3
49	115	1.9	2.9
50	116		2.8
51	117		2.7
52	118		2.5
53	119		2.3
54	120		2
ULT

 

[pmasse13]

Hi Kirk,
Thank You Very Much for taking the time to figure this out for me. I will try and incorporate it into my spreadsheet and let you know.

Again, Much Appreciated.
Phil

 

[KRice]

I'm happy to help. Please let me know if you run into any difficulties or discover that I don't have something quite right.

 

[pmasse13]

Hi Kirk,
I got it to work perfectly.
I can't thank you enough.
All the Best.
Phil

 

[KRice]

You're welcome, Phil. Thanks for the update...I'm glad it's working.

 

[pmasse13]

Hi Kirk,
Is there a slick way to incorporate a value from one workbook in the column in another workbook ?

Like this:
Single Filer (Under 65) But using something like: Single Filer (='Supporting Tables'!$D$2)

Thank you
Phil

 

[KRice]

Yes there is...there are a couple of options depending on your specific needs. You could either hardwire the other workbook and sheet name directly into the formula, or if you might want the formula to pull information from various workbooks (or various sheets)...and you'd like to have some control over which workbook/sheet is being referred to, then you could set up some cells to hold the names of the relevant workbook and worksheet. Then using text string methods, the reference to that workbook/sheet is built and used with the INDIRECT function. In your example, are you trying to pull one value from only one source location?

Using information from my earlier post, I duplicated the ULT worksheet and placed it in another workbook called "Phil 2nd file.xlsx"...and I renamed the ULT worksheet to "ULTs". If I'm interested in pulling the first age 70 ULT value from the 2020 column, using the first approach, the formula in cell G1 shows how that is done. For the 2nd approach, we set up cells D1:E2, where we enter the names of the other workbook and sheet name. Then in cell G2, I've constructed a formula using INDIRECT that pieces together those text strings to form the reference to cell $B$4 in the other workbook. If I wanted to pull information from another worksheet, then the sheet name would be edited.

I believe these workbooks may need to be in the same folder.

MrExcel20210422_RMD.xlsx
	D	E	F	G
1	Other file	Phil 2nd file.xlsx	1st approach	27.4
2	Sheet name	ULTs	2nd approach	27.4
Main

Cell Formulas
Range	Formula
G1	G1	='[Phil 2nd file.xlsx]ULTs'!$B$4
G2	G2	=INDIRECT("'[" & E1 & "]" & E2 & "'!$B$4 ")

 

[pmasse13]

Hi Kirk,
Nevermind... I got it to work using concatenate...

Thank you very much.
You're The BEST.
Phil

 

[KRice]

Good!...I'm glad you got it working.