Populate Fee Schedule Based on Dates

M

MShanDen

New Member
Joined
Jun 17, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 mini sheet below.

Fee Projection Update.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
2ABCDEFGHIJKLM N
3Project NameTotal FeeFee during Phase 1Fee During Phase 2% of Fee Payable During Phase 2% of Retainage 1Retainage 1 Payable Date% of Retainage 2Retainage 2 Release DatePhase 1 Duration (Months)Phase 2 Duration (Months)Start DateEnd Date6/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/20241/1/20252/1/20253/1/20254/1/20255/1/20256/1/20257/1/20258/1/20259/1/202510/1/202511/1/202512/1/20251/1/20262/1/20263/1/20264/1/20265/1/20266/1/20267/1/20268/1/20269/1/202610/1/2026
4Test 1$ 500,000$ 10,000$ 380,00075%10%4/15/202615%8/15/202612146/15/20248/15/2026$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 58,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 57,000.00
5Test 2$ 600,000$ 20,000$ 440,00085%0%15%5/15/20268147/15/20245/15/2026$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 66,000.00
6Test 3$ 400,000$ 15,000$ 280,00075%10%12/15/202515%4/15/20268128/15/20244/15/2026$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 45,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 42,000.00
Sheet1
Cell Formulas
RangeFormula
G4,G6G4=EDATE(M4,-4)
Z4:AI4,AK4:AM4,X6:AE6,AG6:AI6,W5:AJ5Z4=($D4/$K4)*$E4
AJ4,AF6AJ4=(($D4/$K4)*$E4)+($D4*$F4)
AN4,AJ6,AK5AN4=$D4*$H4
D4:D6D4=B4-(C4*J4)
I4:I6I4=M4
M4:M6M4=EDATE(L4,+K4+J4)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Try:
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
1Project NameTotal FeeFee during Phase 1Fee During Phase 2% of Fee Payable During Phase 2% of Retainage 1Retainage 1 Payable Date% of Retainage 2Retainage 2 Release DatePhase 1 Duration (Months)Phase 2 Duration (Months)Start DateEnd Date6/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/20241/1/20252/1/20253/1/20254/1/20255/1/20256/1/20257/1/20258/1/20259/1/202510/1/202511/1/202512/1/20251/1/20262/1/20263/1/20264/1/20265/1/20266/1/20267/1/20268/1/20269/1/202610/1/2026
2Test 150000010000380,0000.750.14/15/20260.158/15/202612146/15/20248/15/202610,00010,00010,00010,00010,00010,00010,00010,00010,00010,00010,00010,00020,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35757,000--
3Test 260000020000440,0000.8500.155/15/20268147/15/20245/15/2026-20,00020,00020,00020,00020,00020,00020,00026,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71466,00066,000-----
4Test 340000015000280,0000.750.112/15/20250.154/15/20268128/15/20244/15/2026--15,00015,00015,00015,00015,00015,00017,50017,50017,50017,50017,50017,50017,50017,50017,50017,50017,50017,50042,00042,00042,000------
Sheet4
Cell Formulas
RangeFormula
N2:AP4N2=LET(e,EOMONTH(+$N$1:$AP$1,0),s,SEQUENCE(1,COUNTA($N$1:$AP$1)), (EOMONTH(L2,0)<=e)*(EOMONTH(M2,0)>=e)*IF(s<=J2,C2,IF(s<=J2+K2,D2/K2*E2,D2*H2)))
Dynamic array formulas.
 
Upvote 0
Cubist said:
Try:
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
1Project NameTotal FeeFee during Phase 1Fee During Phase 2% of Fee Payable During Phase 2% of Retainage 1Retainage 1 Payable Date% of Retainage 2Retainage 2 Release DatePhase 1 Duration (Months)Phase 2 Duration (Months)Start DateEnd Date6/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/20241/1/20252/1/20253/1/20254/1/20255/1/20256/1/20257/1/20258/1/20259/1/202510/1/202511/1/202512/1/20251/1/20262/1/20263/1/20264/1/20265/1/20266/1/20267/1/20268/1/20269/1/202610/1/2026
2Test 150000010000380,0000.750.14/15/20260.158/15/202612146/15/20248/15/202610,00010,00010,00010,00010,00010,00010,00010,00010,00010,00010,00010,00020,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35720,35757,000--
3Test 260000020000440,0000.8500.155/15/20268147/15/20245/15/2026-20,00020,00020,00020,00020,00020,00020,00026,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71426,71466,00066,000-----
4Test 340000015000280,0000.750.112/15/20250.154/15/20268128/15/20244/15/2026--15,00015,00015,00015,00015,00015,00017,50017,50017,50017,50017,50017,50017,50017,50017,50017,50017,50017,50042,00042,00042,000------
Sheet4
Cell Formulas
RangeFormula
N2:AP4N2=LET(e,EOMONTH(+$N$1:$AP$1,0),s,SEQUENCE(1,COUNTA($N$1:$AP$1)), (EOMONTH(L2,0)<=e)*(EOMONTH(M2,0)>=e)*IF(s<=J2,C2,IF(s<=J2+K2,D2/K2*E2,D2*H2)))
Dynamic array formulas.
Click to expand...
Thanks so much @Cubist this got me 75% of the way there.

It looks like what's not working is the fee associated with the retainage. The intent is that retainage 1 (which would be 10% of the "Fee During Phase 2") would be paid on the Retainage 1 Payable Date. The total fee paid on the Retainage 1 Payable date would be the retainage + the standard fee for phase 2. Perhaps it would be easier if I added another row below each project for Retainage?

The intent for retainage 2 is that the payment is made at the end date. So the total would be the standard fee + the retainage (which would be "Fee During Phase 2" * % of Retainage 2).

Regardless, your formula got me close enough; so thanks for that!

Fee Projection Update.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
2ABCDEFGHIJKLM N
3Project NameTotal FeeFee during Phase 1Fee During Phase 2% of Fee Payable During Phase 2% of Retainage 1Retainage 1 Payable Date% of Retainage 2Retainage 2 Release DatePhase 1 Duration (Months)Phase 2 Duration (Months)Start DateEnd Date6/1/20247/1/20248/1/20249/1/202410/1/202411/1/202412/1/20241/1/20252/1/20253/1/20254/1/20255/1/20256/1/20257/1/20258/1/20259/1/202510/1/202511/1/202512/1/20251/1/20262/1/20263/1/20264/1/20265/1/20266/1/20267/1/20268/1/20269/1/202610/1/202611/1/2026CheckDelta2/1/20273/1/20274/1/20275/1/20276/1/2027
4Test 1$ 500,000$ 10,000$ 380,00075%10%4/15/202615%8/15/202612146/15/20248/15/2026$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 10,000.00$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 20,357.14$ 57,000.00$ -$ -$ 462,000.00$ (38,000.00)
5Test 2$ 600,000$ 20,000$ 440,00085%0%15%5/15/20268147/15/20245/15/2026$ -$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 20,000.00$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 26,714.29$ 66,000.00$ 66,000.00$ -$ -$ -$ -$ -$ 646,000.00$ 46,000.00
6Test 3$ 400,000$ 15,000$ 280,00075%10%12/15/202515%4/15/20268128/15/20244/15/2026$ -$ -$ 15,000.00$ 15,000.00$ 15,000.00$ 15,000.00$ 15,000.00$ 15,000.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 17,500.00$ 42,000.00$ 42,000.00$ 42,000.00$ -$ -$ -$ -$ -$ -$ 426,000.00$ 26,000.00
Sheet1
Cell Formulas
RangeFormula
G4,G6G4=EDATE(M4,-4)
M4:M6M4=EDATE(L4,+K4+J4)
N4:AP6N4=LET(e,EOMONTH(+$N$3:$AP$3,0),s,SEQUENCE(1,COUNTA($N$3:$AP$3)), (EOMONTH(L4,0)<=e)*(EOMONTH(M4,0)>=e)*IF(s<=J4,C4,IF(s<=J4+K4,D4/K4*E4,D4*H4)))
AR4:AR6AR4=SUM(N4#)
AS4:AS6AS4=AR4-B4
D4:D6D4=B4-(C4*J4)
I4:I6I4=M4
Dynamic array formulas.
 
Upvote 0
I overlooked the retainage date. Try this.
Excel Formula: 
=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))
 
Last edited:
Upvote 0
Cubist said:
I overlooked the retainage date. Try this.
Excel Formula: 
=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))
Click to expand...
That did it @Cubist
Amazing work. I owe you a beer!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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