MShanDen

Jun 17, 2024Copper Contributor

# Fee Projection Formula Based on Dates

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:

Any recommendations would be greatly appreciated.

- 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))