How do I take the difference between two cells data inputs and create a number of rows that equals that number?

[Tdb22]

Question: How do I take the difference between cell data inputs (see text in red below) and create a dynamic number of rows that equals the difference?

For example:
Age at Distribution: 56
Age at End of Distribution: 90
Years of Total Distribution: 34 <--------- This is the number of rows I want in my table. It should start at age 56 and sequentially increase to age 90. Currently, I'm creating this set of row data manually. I want it to dynamically change based on cell inputs that will change.

Current Spreadsheet Inputs

Accumulation Phase			Age
Current Age	35		56
Account	$186,000		57
Contributions (Annual)	$10,000		58
Tax Rate	28.00%		59
Nominal Return Rate	7.00%		60
Retire Age	55		61
Years To Retirement	20		62
Future Value of Account	$1,158,413		63
			64
Distribution Phase			65
Account Value	$1,158,413		66
Distribution Percentage	4.50%		67
Tax Rate	33.00%		68
Nominal Return Rate	5.00%		69
Lost Opportuntiy Cost Rate	6.00%		70
Age at Distribution	56		71
Age at End of Distribution	90		72
Years of Total Distribution	34		73
			74
			75
			76
			77
			78
			79
			80
			81
			82
			83
			84
			85
			86
			87
			88
			89
			90

<tbody>
</tbody>

 

[cooper645]

For your first cell you could use

=cell with distribution age

then use the following formula in the one below and copy it down the whole column. You could then just copy the column from sheet to sheet

=IF(B1<$A$2,B1+1,"")

B1 = first cell which distribution age started
$A$2 = max age cell

then when you input those ages, it will fill as neccessary

i have made an example for you to see
https://www.dropbox.com/s/ovicb2mfxk9gwyp/distribution help.xlsx?dl=0

 

[Tdb22]

Cooper645, excellent. Thank you! It automatically populates the ages in the column to fit the inputs. Awesomeness!

67	67
90	68	=IF(B1<$A$2,B1+1,"")
23	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90

<tbody>
</tbody>

 

[cooper645]

No problem

 

[Tdb22]

Cooper,

I'm having a problem with my spreadsheet (other columns don't dynamically autosize to the range inputs calculated above.

In the spreadsheet below, cell B20-B19 equals (22 years). I need to use that number as the beginning value in a future value formula (see fX top of sheet) that will dynamically match the number of periods in a range (22 years); however, as the formula moves down the age range (e.g., 68, 69, 70, ... , 90), then it will reduce the original period range (22 years) by one year for each row down the range of rows. To illustrate, J5 would be 21 years in the future value formula. J6 would be 20 years. And so on.

Any idea how I can create this formula? Thank you. Follow the columns over to cell J4. This is what I need to adjust dynamically to match number of years originally but decline in value as age nears "age at end of distribution". In this case, 90.

Tax and Lost Opportunity Cost Analysis							Total Income Distribution	Total Tax Cost	Total LOC Cost	Total Tax + LOC Cost
							$4,806,961	$1,586,297	$92,520,360	$94,106,657
Accumulation Phase			Age	Beginning Account Value	Account Value Growth	Account Value After Income Distribution	Income Distribution	Tax	LOC	Total
Current Age	35		68	$2,800,375	$2,940,393	$2,814,376	$126,017	$41,586	149,855.13	$191,441	----> J4 formula is =fv($B$18,($D$26-D4),,-I4,1)
Account	$186,000		69	$2,814,376	$2,955,095	$2,828,448	$126,647	$41,793	142,079.63	$183,873
Contributions (Annual)	$10,000		70	$2,828,448	$2,969,871	$2,842,591	$127,280	$42,002	134,707.57	$176,710
Tax Rate	28.00%		71	$2,842,591	$2,984,720	$2,856,804	$127,917	$42,212	127,718.03	$169,931
Nominal Return Rate	7.00%		72	$2,856,804	$2,999,644	$2,871,088	$128,556	$42,424	121,091.15	$163,515
Retire Age	67		73	$2,871,088	$3,014,642	$2,885,443	$129,199	$42,636	114,808.12	$157,444
Years To Retirement	32		74	$2,885,443	$3,029,715	$2,899,870	$129,845	$42,849	108,851.09	$151,700
Future Value of Account	$2,800,375		75	$2,899,870	$3,044,864	$2,914,370	$130,494	$43,063	103,203.16	$146,266
			76	$2,914,370	$3,060,088	$2,928,941	$131,147	$43,278	97,848.28	$141,127
Distribution Phase			77	$2,928,941	$3,075,389	$2,943,586	$131,802	$43,495	92,771.25	$136,266
Account Value	$2,800,375		78	$2,943,586	$3,090,765	$2,958,304	$132,461	$43,712	87,957.64	$131,670
Distribution Percentage	4.50%		79	$2,958,304	$3,106,219	$2,973,096	$133,124	$43,931	83,393.80	$127,325
Tax Rate	33.00%		80	$2,973,096	$3,121,750	$2,987,961	$133,789	$44,150	79,066.77	$123,217
Nominal Return Rate	5.00%		81	$2,987,961	$3,137,359	$3,002,901	$134,458	$44,371	74,964.25	$119,335
Lost Opportuntiy Cost Rate	6.00%		82	$3,002,901	$3,153,046	$3,017,915	$135,131	$44,593	71,074.59	$115,668
Age at Distribution	68		83	$3,017,915	$3,168,811	$3,033,005	$135,806	$44,816	67,386.76	$112,203
Age at End of Distribution	90		84	$3,033,005	$3,184,655	$3,048,170	$136,485	$45,040	63,890.28	$108,930
Years of Total Distibution	22		85	$3,048,170	$3,200,578	$3,063,411	$137,168	$45,265	60,575.21	$105,841
			86	$3,063,411	$3,216,581	$3,078,728	$137,853	$45,492	57,432.16	$102,924
			87	$3,078,728	$3,232,664	$3,094,122	$138,543	$45,719	54,452.19	$100,171
			88	$3,094,122	$3,248,828	$3,109,592	$139,235	$45,948	51,626.84	$97,575
			89	$3,109,592	$3,265,072	$3,125,140	$139,932	$46,177	48,948.09	$95,126
			90	$3,125,140	$3,281,397	$3,140,766	$140,631	$46,408	46,408.33	$92,817
				$3,140,766	$3,297,804	$3,156,470	$141,334	$46,640	8,836,695.31	$8,883,336
				$3,156,470	$3,314,293	$3,172,252	$142,041	$46,874	8,880,878.79	$8,927,752
				$3,172,252	$3,330,865	$3,188,113	$142,751	$47,108	8,925,283.18	$8,972,391
				$3,188,113	$3,347,519	$3,204,054	$143,465	$47,343	8,969,909.60	$9,017,253
				$3,204,054	$3,364,256	$3,220,074	$144,182	$47,580	9,014,759.14	$9,062,339
				$3,220,074	$3,381,078	$3,236,174	$144,903	$47,818	9,059,832.94	$9,107,651
				$3,236,174	$3,397,983	$3,252,355	$145,628	$48,057	9,105,132.10	$9,153,189

<colgroup><col style="width: 165px"><col width="100"><col width="42"><col width="29"><col width="70"><col width="79"><col width="84"><col width="68"><col width="58"><col width="84"><col width="89"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

 

[Tdb22]

Cooper, the J4 cell reference in beneath the column highlighted in yellow that reads "LOC".

 

[cooper645]

having a play about at the moment, never dealt with the =FV formula, but happy to learn and help.

How is your initail value in cell J4 worked out, as putting the formula in that you have across in "L4", returns a #value result for me. - Solved, copy and paste had $ in number cells (Need coffee)

 

[cooper645]

i have changed the formula slightly,

i assumed the values you had were the correct ones, but as you were using the last number "90" in cell "D26", you were getting the wrong return when the "age at end of return of distrubution" was changed.

the change i made was the cell reference for the "end of distribution age" i moved it to the same reference that column "E" uses

this is the formula;

=FV($B$18,($B$20-D4),,-I4,1)

Hope thats what you were after, if not then let me know and i will have another look.

Coops

 

[cooper645]

If that is what you were after, and you copy it down all the way as far as the age formula, it will return a #VALUE error when it goes past the last age, so I added a bit to ensure it stays blank and clean if it doesn't need to display a value

=IF(ISERROR(FV($B$18,($B$20-D4),,-I4,1)),"",FV($B$18,($B$20-D4),,-I4,1))

 

[cooper645]

If that is what you were after, and you copy it down all the way as far as the age formula, it will return a #VALUE error when it goes past the last age, so I added a bit to ensure it stays blank and clean if it doesn't need to display a value

=IF(ISERROR(FV($B$18,($B$20-D4),,-I4,1)),"",FV($B$18,($B$20-D4),,-I4,1))

Has this solution solved your problem??