Dynamic Growth Formula with Varying Time Periods

H

HeresRandy

New Member
Joined
Dec 18, 2018
Messages
1
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
ABCDEFGHIJKLMNOPQR
Inputs
Invested
IncrementsDate
1st IncrementInvested
Exit DateDistributed
Exit MultipleNAV Growth

<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>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD="align: right"]9/30/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]3/31/2019[/TD]
[TD="align: right"]6/30/2019[/TD]
[TD="align: right"]9/30/2019[/TD]
[TD="align: right"]12/31/2019[/TD]
[TD="align: right"]3/31/2020[/TD]
[TD="align: right"]6/30/2020[/TD]
[TD="align: right"]9/30/2020[/TD]
[TD="align: right"]12/31/2020[/TD]
[TD="align: right"]3/31/2021[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]12/31/2020[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3.0x[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.545 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 4.145 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 4.812 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 5.562 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.419 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.419 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.419 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.419 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.419 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.419 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: right"] 3.419 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Totals[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]=(($F$5*$B$7)-$F$5)/MATCH($B$6,$G$4:$R$4,0)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"] 0.545 [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] "]=(($G$5*$B$7)-$G$5)/MATCH($B$6,$H$4:$R$4,0)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C5D9F1]#C5D9F1[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B8CCE4]#B8CCE4[/URL] , align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"] 0.600 [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0"]9[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] "]=(($H$5*$B$7)-$H$5)/MATCH($B$6,$I$4:$R$4,0)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D8E4BC]#D8E4BC[/URL] , align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"] 0.667 [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0"]9[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]=(($I$5*$B$7)-$I$5)/MATCH($B$6,$J$4:$R$4,0)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] , align: right"] 0.750 [/TD]
[TD="align: right"] 0.750 [/TD]
[TD="align: right"] 0.750 [/TD]
[TD="align: right"] 0.750 [/TD]
[TD="align: right"] 0.750 [/TD]
[TD="align: right"] 0.750 [/TD]
[TD="align: right"] 0.750 [/TD]
[TD="align: right"] 0.750 [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0"]9[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] "]=(($J$5*$B$7)-$J$5)/MATCH($B$6,$K$4:$R$4,0)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C4BD97]#C4BD97[/URL] , align: right"] 0.857 [/TD]
[TD="align: right"] 0.857 [/TD]
[TD="align: right"] 0.857 [/TD]
[TD="align: right"] 0.857 [/TD]
[TD="align: right"] 0.857 [/TD]
[TD="align: right"] 0.857 [/TD]
[TD="align: right"] 0.857 [/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B0F0"]9[/TD]

</tbody>
Sheet1​



Many Thanks!!
Randy
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You must log in or register to reply here.

Similar threads

S
  • Solved
Get table name on worksheet using formulas without using VBA
2
Replies
11
Views
1K
6StringJazzer
6StringJazzer
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
513
Eric W
Eric W
S
  • Question Question
IF condition with value splitting
Replies
5
Views
497
SanjayGMusafir
SanjayGMusafir
V
  • Solved
Adjust formula of dynamic average
2
Replies
14
Views
884
Peter_SSs
Peter_SSs
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top