Fee Projection Formula Based on Dates

[MShanDen]

I'm not sure if a formula I'm describing is possible but I thought I'd ask the group for recommendations. 

Below is a description:

The blue text is intended to be a manual entry, the black text will be based on formulas.

We have projects that receive a fee, a portion of that fee is payable as a flat rate over the course of a defined duration. The remaining fee is paid over the duration of phase 2, but a portion of the remaining fee is subject to retainage. 

I've been trying to develop a formula that would auto populate the fee schedule based on the input data. 

I've included a screenshot below. 

Link to file: 

https://docs.google.com/spreadsheets/d/1XtDbmbQRKFpYVeNnpN4yvr1IFkkXAi37/edit?usp=sharing&ouid=11165...

Any recommendations would be greatly appreciated. 

[#2]

@MShanDen 

I'm not sure if a formula I'm describing is possible but I thought I'd ask the group for recommendations. 

I AM sure that such a formula is possible. (There aren't many, if any, formulas that can't be created, once the specifics have been described clearly. THAT -- getting a clear description -- is where the rub usually enters the picture.)

You could help us help you by posting not just an image but a copy of that actual spreadsheet--put it on OneDrive or GoogleDrive and paste a link here that grants access. 

And then there's the matter of the clear description. Looking at the images (granted without studying them closely) it's not clear to me what the different columns are meant to represent, and in particular those off to the right side with various dates as headers. So if you could take some time to explain what those are about, that would help.

The formula will not be the hard part; it's making sure we understand how the parts fit together.

[#3]

Thanks @mathetes just included the link to the file.

[#4]

That's a start. I also said we could use a clear description. At this point, you're in effect asking us to reverse engineer the numbers you show off to the right. Again, you could help us help you if you explain the why behind this schedule; it may seem obvious to you, but even such terminology as "retainage"--which isn't even a word--could be explained; the difference between retainage 1 and retainage 2. While you're at it, all of the dates off on the right are the first of the month, but the dates in the first thirteen columns are all the 15th of their respective months.

On row one, although phase 1 and phase 2 add up to 26, there are in fact 27 months during which payments are shown...why?

AND SO FORTH. I give those just as examples of things that may be entirely clear to you, but aren't intuitive. If we were talking face to face, I could ask questions along the way. Since we're not, you need to explain, making the assumption that you're talking with an intelligent person who is just new to your project and the terminologies, but who is very experienced with Excel. Just explain to that person, in detail and in basic English language (let us do the translation into Excelese) what--given the INPUT of columns A-M--the OUTPUT is to be.

[#5 · Accepted solution]

I figured it out @mathetes.
Formula is below if you're curious.

Also Retainage is money withheld by one party in a contract to act as security against incomplete or defective works.

=LET(e,EOMONTH(+$N$3:$AP$3,0),(EOMONTH(L4,0)<=e)*IFS(e=EOMONTH(G4,0),D4/K4*E4+D4*F4,e=EOMONTH(I4,0),D4*H4,e<=EOMONTH(L4,J4-1),C4,e<=EOMONTH(L4,J4+K4-1),D4/K4*E4,TRUE,0))

[#6]

Good job! I was thinking something along those lines as well. But for your sake--for the sake of learning--you should feel really good to have figured it out on your own. LET lets you do neat things with formulas. To make it even more elegant, you might want to designate other variables for the other EOMONTH calculations, maybe giving them meaningful (abbreviated) names to indicate such things as EPh1, EPh2, ERet1, ERet2.....that way the final formula is actually readable by itself, without needing to refer to the spreadsheet column headings.