Dynamic Growth Formula with Varying Time Periods

[HeresRandy]

Hi there,

Thanks in advance for the help!

What I need to do: I have investments that will be made in increments (in this case $15 total invested across five $3 increments in consecutive quarters starting with 3/31/2018). Once invested, each increment will grow beginning in the next quarter assuming straight line growth to a certain multiple (in this case 3 times: $45 total after all growth) until the entire investment is exited in 12/31/2020. In this example the first increment of 3 will grow to 9 by taking the growth of 6 and dividing by the number of periods of growth (11). The next increment of 3 will grow to 9 by taking the growth of 6 and dividing by the number of periods of growth (10), etc...

The problem: I need to show this growth quarterly on one line as its part of a much larger model. Additionally, I need to build the formula such that exit date and multiple are dynamic, as are the potential number of increments and amounts.

The example: I have been able to build out a very ugly and static model to illustrate what I would like to do in a dynamic formula, as per below. The red line is the dynamic formula I am attempting to solve for. This is only a small sample set and will need to be done across hundreds of investments and over 50 years worth of time periods so manually just doesnt make sense here.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	Inputs
3	Invested	15
4	Increments	5		Date	3/31/2018	6/30/2018	9/30/2018	12/31/2018	3/31/2019	6/30/2019	9/30/2019	12/31/2019	3/31/2020	6/30/2020	9/30/2020	12/31/2020	3/31/2021
5	1st Increment	3/31/2018		Invested	3	3	3	3	3
6	Exit Date	12/31/2020		Distributed												-45
7	Exit Multiple	3.0x		NAV Growth	3	3.545	4.145	4.812	5.562	3.419	3.419	3.419	3.419	3.419	3.419	3.419
8																		Totals
9			=(($F$5*$B$7)-$F$5)/MATCH($B$6,$G$4:$R$4,0)		3	0.545	0.545	0.545	0.545	0.545	0.545	0.545	0.545	0.545	0.545	0.545		9
10			=(($G$5*$B$7)-$G$5)/MATCH($B$6,$H$4:$R$4,0)			3	0.600	0.600	0.600	0.600	0.600	0.600	0.600	0.600	0.600	0.600		9
11			=(($H$5*$B$7)-$H$5)/MATCH($B$6,$I$4:$R$4,0)				3	0.667	0.667	0.667	0.667	0.667	0.667	0.667	0.667	0.667		9
12			=(($I$5*$B$7)-$I$5)/MATCH($B$6,$J$4:$R$4,0)					3	0.750	0.750	0.750	0.750	0.750	0.750	0.750	0.750		9
13			=(($J$5*$B$7)-$J$5)/MATCH($B$6,$K$4:$R$4,0)						3	0.857	0.857	0.857	0.857	0.857	0.857	0.857		9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Many Thanks!!
Randy