Formula for Changing % Allocations

[krich3]

I am trying to create a formula that I can insert into a 'start period' cell and drag to populate % changes (different for each period). What is the best function to utilize for this purpose? (See Below)




[TABLE="width: 0"]
<tbody>[TR]
[TD="colspan: 8"]Using the "Starts" Table, calculate total fees for each project, fee amounts generated each period per project, and total fees.
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]Assumptions: The fee is 3.5% on Total Capitalization. 25% of the fee is collected in the first period, 10% in
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]the final period, and the remainder in a straight-line amount over the duration of the project.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]STARTS (Total Capitalization per Initial Period)
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]ASSUMPTIONS
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment
[/TD]
[TD]Duration
[/TD]
[TD]Total Capitalization
[/TD]
[TD]Start Period
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD="colspan: 2"]% of Fees in First Period
[/TD]
[TD]25.0%
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 1
[/TD]
[TD]3 Periods
[/TD]
[TD]$21,499
[/TD]
[TD]Period 2
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD="colspan: 2"]% of Fees in Last Period
[/TD]
[TD]10.0%
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 2
[/TD]
[TD]5 Periods
[/TD]
[TD]$27,833
[/TD]
[TD]Period 1
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]Fee Rate
[/TD]
[TD]
[/TD]
[TD]3.50%
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 3
[/TD]
[TD]4 Periods
[/TD]
[TD]$23,494
[/TD]
[TD]Period 2
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 4
[/TD]
[TD]2 Periods
[/TD]
[TD]$35,585
[/TD]
[TD]Period 4
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 5
[/TD]
[TD]2 Periods
[/TD]
[TD]$35,534
[/TD]
[TD]Period 5
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]
[/TD]
[TD]$143,945
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]FEES PER PERIOD
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment
[/TD]
[TD]Duration
[/TD]
[TD]Total Fees per Project
[/TD]
[TD]Period 1
[/TD]
[TD]Period 2
[/TD]
[TD]Period 3
[/TD]
[TD]Period 4
[/TD]
[TD]Period 5
[/TD]
[TD]Period 6
[/TD]
[TD]Period 7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 1
[/TD]
[TD]3 Periods
[/TD]
[TD]$0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 2
[/TD]
[TD]5 Periods
[/TD]
[TD]$0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 3
[/TD]
[TD]4 Periods
[/TD]
[TD]$0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 4
[/TD]
[TD]2 Periods
[/TD]
[TD]$0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Investment 5
[/TD]
[TD]2 Periods
[/TD]
[TD]$0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Fees
[/TD]
[TD]
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD]$0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MikeDBMan]

I have the formula about 70% complete but am leaving for a 4-day weekend pretty soon. I'll check back on Tuesday and if nobody else has solved your problem, I'll try to get the formula finished. I have to add an additional field to make it easier: A calculated period-end field which takes the first period, adds the Duration and then displays the Ending Period.

 

[MikeDBMan]

I have the formula but can't figure out how to easily present it to you. I don't know how to upload a spreadsheet, do you? I will try to just upload the formula with a few explanations:​

=IF(G$13<$B15,0,IF(G$13=$B15,0.25*0.035*VLOOKUP($C15,$C$5:$F$11,3,FALSE),IF(G$13=$A15,(0.1*0.035*VLOOKUP($C15,$C$5:$F$11,3,FALSE)),IF(G$13>$A15,0,(0.65/($D15-2)*0.035*VLOOKUP($C15,$C$5:$F$11,3,FALSE))))))

Row 13 contains headers: In order A=J: End, Start, Investment #, Duration, TOtal Fees per project, Period 1, Period2, Period 3, Period 4, Period 5, etc.

Column A on row 15 is the end period, column b is start period. C is the investment name, D is the duration, F period 1 fees, G is period2 fees, H is period 3 fees,etc.
THe lookup is looking up the data where the investments are stored: Col C=Investment Name, D is Duration, E is total value, F is start period. HOpe you can figure this out. I didn't test it very extensively but see if it works at all and let me know. Back next week.