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)

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

<tbody>
</tbody>

 

[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.