Offset question, or some other possible solution to this problem

[Enda80]

http://www.irs.gov/publications/p590/ar02.html

A situation which would seem to have a simple answer but so far I have not yet devised one for it. Namely, this involves required minimum distributions. When someone reaches a certain age, 70.5, after that they have to start taking money out of their retirement plan account. Of course, more to it than that figures into the situation, however, that serves as the needed information to understand the problem.

This money must come out of the retirement plan at a certain rate. That rate gets decided upon by the age of the person in the retirment plan, and the age of the person that he or she has designated as a beneficiary to receive his or her money if he or she dies.

So, for somebody born in 1926, that person would have reached the age of 70.5 in 1996. That person's beneficiary reached the age of 20 that same year.

Table II (continued)
20 21 22 23 24 25 26 27 28 29
60 63.3 62.3 61.4 60.4 59.5 58.5 57.6 56.6 55.7 54.7
61 63.3 62.3 61.3 60.4 59.4 58.5 57.5 56.6 55.6 54.7
62 63.2 62.3 61.3 60.4 59.4 58.4 57.5 56.5 55.6 54.7
63 63.2 62.3 61.3 60.3 59.4 58.4 57.5 56.5 55.6 54.6

Now, to calculate the rate at which the money must come out of the person's retirement plan, for 1996, 1/63.3 of the balance of the account must come out.

What I wanted to do was to set up a formula on another worksheet which return, during a particular year, what the rate would be. In 1996, when the participant has reached the age of 60 and the beneificiary has reached the age of 20, that would come to 1/63.3, while in 1997, when the participant has reached the age of 61 and the beneficiary the age of 21, that would come to 1/62.3.

I tried this out on one worksheet in a simplifed form so I could get the formulas involved ready for this post.

Doing it one one sheet, I pasted the information (after doing a text to columns in data) so that cell A1 remained a blank cell, while Cells A2:A5 had the numbers from 60 to 63 in them (age of the participant). Cells B1:K1 had the numbers 20 to 29 in them (age of the beneficiary). I decided to use the offset function to pull this off.

So, in Cell B9, I put in this formula
=OFFSET(B2,1,1,1,1)


When I dragged to autopopulate the cells underneath, this happened:
=OFFSET(B3,1,1,1,1)
=OFFSET(B4,1,1,1,1)
=OFFSET(B5,1,1,1,1)

62.3
62.3
62.3
0

When it returned the number 0, I know a problem had occured. Namely, the formulas offset the cells underneath the cell that I had first offset and so forth, instead of offsetting the location that I had last offset. So instead of offestting C3, D4, E5, F6, etc., it followed the pattern described above.

 

[Jim-in-I]

I am not sure if this idea works for your project, but I thought I would submit it for your perusal.

In Sheet1 there is an entry section that allows you to enter:

• Participant Birth Date
• Beneficiary Birth Date
• Projected Date (for future balance reduction)
• Retirement Amount (balance in account)

These data entry cells are highlighted in green.

From this information you are provided with:

• Current Age of Participant
• Current Age of Beneficiary
• Projected Age of Participant (based on Projected Date)
• Projected Age of Beneficiary (based on Projected Date)
• Projected Rate (based on data table of rates – on Sheet2)
• Projected Deduction (based on data table of rates – on Sheet2)

MrExcel_Test_08-20-08.xls
	A	B	C	D
1	RetirementFundCalculation	Dates	CurrentAge	ProjectedAge
2	ParticipantBirthDate=	1/1/1936	72.7	60
3	BeneficiaryBirthDate=	1/1/1976	32.7	20
4
5	EnterProjectedDate=	6/1/1996
6	RetirementAmount=	$500,000
7
8	ProjectedRate=	63.3
9	ProjectedDeduction=	$31,650
10
11	Participantmustreducethetotalbalanceby$31,650in1996.
Sheet1

The data table on Sheet2 was duplicated from your post; however, I would assume the actual rate table is much larger.

MrExcel_Test_08-20-08.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		BeneficiaryAge
3	ParticipantAge	?9	20	21	22	23	24	25	26	27	28	29
4		60	63.3	62.3	61.4	60.4	59.5	58.5	57.6	56.6	55.7	54.7
5		61	63.3	62.3	61.3	60.4	59.4	58.5	57.5	56.6	55.6	54.7
6		62	63.2	62.3	61.3	60.4	59.4	58.4	57.5	56.5	55.6	54.7
7		63	63.2	62.3	61.3	60.3	59.4	58.4	57.5	56.5	55.6	54.6
Sheet2

If this is something you can use, you will need to make modifications to one cell in the entry section.

Change Cell B8 (Projected Rate) to point at your actual data table
So: =INDEX(Sheet2!C4:L7,MATCH(D2,Sheet2!B4:B7,0),MATCH(D3,Sheet2!C3:L3,0))

• Change the Sheet Name to data table location (Sheet2 in this formula)
• Change the Cells to include the entire data table (C4:L7, B4:B7, D3:L3, in this formula.

Sure hope this helps out,
Jim

 

[tusharm]

Two points.

First, you mention someone born in 1926 being of age 70.5 in 1996. That makes sense since the difference between the two years is 70. But, then, the table you use is for someone 60-63 years of age. Also, in the subsequent text you refer to the same 1926 person being of age 60 in 1996!

Second, in the table you are creating, it would appear all you want to do is duplicate the IRS table. So, all you need is =A2 in the first cell. Then, copy that cell as far right as needed and then copy that row as far down as needed.

http://www.irs.gov/publications/p590/ar02.html

A situation which would seem to have a simple answer but so far I have not yet devised one for it. Namely, this involves required minimum distributions. When someone reaches a certain age, 70.5, after that they have to start taking money out of their retirement plan account. Of course, more to it than that figures into the situation, however, that serves as the needed information to understand the problem.

This money must come out of the retirement plan at a certain rate. That rate gets decided upon by the age of the person in the retirment plan, and the age of the person that he or she has designated as a beneficiary to receive his or her money if he or she dies.

So, for somebody born in 1926, that person would have reached the age of 70.5 in 1996. That person's beneficiary reached the age of 20 that same year.

Table II (continued)
20 21 22 23 24 25 26 27 28 29
60 63.3 62.3 61.4 60.4 59.5 58.5 57.6 56.6 55.7 54.7
61 63.3 62.3 61.3 60.4 59.4 58.5 57.5 56.6 55.6 54.7
62 63.2 62.3 61.3 60.4 59.4 58.4 57.5 56.5 55.6 54.7
63 63.2 62.3 61.3 60.3 59.4 58.4 57.5 56.5 55.6 54.6

Now, to calculate the rate at which the money must come out of the person's retirement plan, for 1996, 1/63.3 of the balance of the account must come out.

What I wanted to do was to set up a formula on another worksheet which return, during a particular year, what the rate would be. In 1996, when the participant has reached the age of 60 and the beneificiary has reached the age of 20, that would come to 1/63.3, while in 1997, when the participant has reached the age of 61 and the beneficiary the age of 21, that would come to 1/62.3.

I tried this out on one worksheet in a simplifed form so I could get the formulas involved ready for this post.

Doing it one one sheet, I pasted the information (after doing a text to columns in data) so that cell A1 remained a blank cell, while Cells A2:A5 had the numbers from 60 to 63 in them (age of the participant). Cells B1:K1 had the numbers 20 to 29 in them (age of the beneficiary). I decided to use the offset function to pull this off.

So, in Cell B9, I put in this formula
=OFFSET(B2,1,1,1,1)


When I dragged to autopopulate the cells underneath, this happened:
=OFFSET(B3,1,1,1,1)
=OFFSET(B4,1,1,1,1)
=OFFSET(B5,1,1,1,1)

62.3
62.3
62.3
0

When it returned the number 0, I know a problem had occured. Namely, the formulas offset the cells underneath the cell that I had first offset and so forth, instead of offsetting the location that I had last offset. So instead of offestting C3, D4, E5, F6, etc., it followed the pattern described above.

 

[Enda80]

Somebody pointed out an error, therefore I have revised this post below

http://www.irs.gov/publications/p590/ar02.html

A situation which would seem to have a simple answer but so far I have not yet devised one for it. Namely, this involves required minimum distributions. When someone reaches a certain age, 70.5, after that they have to start taking money out of their retirement plan account. Of course, more to it than that figures into the situation, however, that serves as the needed information to understand the problem.

This money must come out of the retirement plan at a certain rate. That rate gets decided upon by the age of the person in the retirment plan, and the age of the person that he or she has designated as a beneficiary to receive his or her money if he or she dies.

So, for somebody born in 1926, that person would have reached the age of 70.5 in 1996. That person's beneficiary reached the age of 20 that same year.

Table II (continued)
Ages 20 21 22 23 24 25 26 27 28 29
70 63.1 62.2 61.2 60.2 59.3 58.3 57.3 56.4 55.4 54.4
71 63.1 62.1 61.2 60.2 59.2 58.3 57.3 56.4 55.4 54.4
72 63.1 62.1 61.2 60.2 59.2 58.3 57.3 56.3 55.4 54.4
73 63.1 62.1 61.2 60.2 59.2 58.3 57.3 56.3 55.4 54.4
74 63.1 62.1 61.2 60.2 59.2 58.2 57.3 56.3 55.4 54.4
75 63.1 62.1 61.1 60.2 59.2 58.2 57.3 56.3 55.3 54.4
76 63.1 62.1 61.1 60.2 59.2 58.2 57.3 56.3 55.3 54.4
77 63.1 62.1 61.1 60.2 59.2 58.2 57.3 56.3 55.3 54.4
78 63.1 62.1 61.1 60.2 59.2 58.2 57.3 56.3 55.3 54.4
79 63.1 62.1 61.1 60.2 59.2 58.2 57.2 56.3 55.3 54.3
80 63.1 62.1 61.1 60.1 59.2 58.2 57.2 56.3 55.3 54.3
81 63.1 62.1 61.1 60.1 59.2 58.2 57.2 56.3 55.3 54.3
82 63.1 62.1 61.1 60.1 59.2 58.2 57.2 56.3 55.3 54.3
83 63.1 62.1 61.1 60.1 59.2 58.2 57.2 56.3 55.3 54.3
84 63 62.1 61.1 60.1 59.2 58.2 57.2 56.3 55.3 54.3


Now, to calculate the rate at which the money must come out of the person's retirement plan, for 1996, 1/63.3 of the balance of the account must come out.

What I wanted to do was to set up a formula on another worksheet which return, during a particular year, what the rate would be. In 1996, when the participant has reached the age of 60 and the beneficiary has reached the age of 20, that would come to 1/63.3, while in 1997, when the participant has reached the age of 61 and the beneficiary the age of 21, that would come to 1/62.3.

I tried this out on one worksheet in a simplified form so I could get the formulas involved ready for this post.

Doing it one one sheet, I pasted the information (after doing a text to columns in data) so that cell A1 remained a blank cell, while Cells A2:A5 had the numbers from 60 to 63 in them (age of the participant). Cells B1:K1 had the numbers 20 to 29 in them (age of the beneficiary). I decided to use the offset function to pull this off.

So, in Cell B9, I put in this formula
=OFFSET(B2,1,1,1,1)


When I dragged to autopopulate the cells underneath, this happened:
=OFFSET(B3,1,1,1,1)
=OFFSET(B4,1,1,1,1)
=OFFSET(B5,1,1,1,1)

62.3
62.3
62.3
0

When it returned the number 0, I know a problem had occured. Namely, the formulas offset the cells underneath the cell that I had first offset and so forth, instead of offsetting the location that I had last offset. So instead of offestting C3, D4, E5, F6, etc., it followed the pattern described above.

 

[Jim-in-I]

I noticed the age discrepancy in your original post and had adjusted the Participant Birth Date to accommodate your rates table.

If you are interested, here is an updated view with your revised rate table.

MrExcel_Test_08-20-08.xls
	A	B	C	D
1	RetirementFundCalculation	Dates	CurrentAge	ProjectedAge
2	ParticipantBirthDate=	1/1/1926	82.7	70
3	BeneficiaryBirthDate=	1/1/1976	32.7	20
4
5	EnterProjectedDate=	6/1/1996
6	RetirementAmount=	$500,000
7
8	ProjectedRate=	63.1
9	ProjectedDeduction=	$31,550
10
11	Participantmustreducethetotalbalanceby$31,550in1996.
Sheet1

MrExcel_Test_08-20-08.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1
2			BeneficiaryAge
3		ParticipantAge	20	21	22	23	24	25	26	27	28	29
4		70	63.1	62.2	61.2	60.2	59.3	58.3	57.3	56.4	55.4	54.4
5		71	63.1	62.1	61.2	60.2	59.2	58.3	57.3	56.4	55.4	54.4
6		72	63.1	62.1	61.2	60.2	59.2	58.3	57.3	56.3	55.4	54.4
7		73	63.1	62.1	61.2	60.2	59.2	58.3	57.3	56.3	55.4	54.4
8		74	63.1	62.1	61.2	60.2	59.2	58.2	57.3	56.3	55.4	54.4
9		75	63.1	62.1	61.1	60.2	59.2	58.2	57.3	56.3	55.3	54.4
10		76	63.1	62.1	61.1	60.2	59.2	58.2	57.3	56.3	55.3	54.4
11		77	63.1	62.1	61.1	60.2	59.2	58.2	57.3	56.3	55.3	54.4
12		78	63.1	62.1	61.1	60.2	59.2	58.2	57.3	56.3	55.3	54.4
13		79	63.1	62.1	61.1	60.2	59.2	58.2	57.2	56.3	55.3	54.3
14		80	63.1	62.1	61.1	60.1	59.2	58.2	57.2	56.3	55.3	54.3
15		81	63.1	62.1	61.1	60.1	59.2	58.2	57.2	56.3	55.3	54.3
16		82	63.1	62.1	61.1	60.1	59.2	58.2	57.2	56.3	55.3	54.3
17		83	63.1	62.1	61.1	60.1	59.2	58.2	57.2	56.3	55.3	54.3
18		84	63.1	62.1	61.1	60.1	59.2	58.2	57.2	56.3	55.3	54.3
Sheet2

Regards,
Jim

 

[Enda80]

Thank you for your help. If not inconvenient, could you e-mail a copy of this file?