 # Apply An escalation rate depending on the year

Hi there,

I wish to use a formula that applies an escalation rate to a certain fee depending on the year that the fee is charged.

For Instance, If a project goes begins part way through 2020 and ends in feb of 2022, working days in 2020 need to be charged the normal rate, working days in 2021 need to be charged the normal fee + the escalation rate * normal fee and the working days in 2022 need to be charged the normal fee + the escalation rate^2 * normal fee.

This formula needs to be applicable to any number of years that the project could span

Cheers for the help

``=LET(start,A2,end,B2,baserate,C2,escalation,D2,holidays,0,startyr,NETWORKDAYS(start,DATE(YEAR(start),12,31),holidays),endyr,NETWORKDAYS(DATE(YEAR(end),1,1),end,holidays),IFS(start>end,"bad start/end dates",YEAR(end)=YEAR(start),NETWORKDAYS(start,end,holidays),YEAR(end)-YEAR(start)=1,startyr+(1+escalation)*endyr,TRUE,LET(yrcount,SEQUENCE(YEAR(end)-YEAR(start)-1,,1,1),years,NETWORKDAYS(DATE(YEAR(start)+yrcount,1,1),DATE(YEAR(start)+yrcount,12,31),holidays),SUM(startyr,years*(1+escalation)^yrcount,endyr*(1+escalation)^(YEAR(end)-YEAR(start)))))*baserate)``