Apply An escalation rate depending on the year

%3CLINGO-SUB%20id%3D%22lingo-sub-2051454%22%20slang%3D%22en-US%22%3EApply%20An%20escalation%20rate%20depending%20on%20the%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2051454%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20to%20use%20a%20formula%20that%20applies%20an%20escalation%20rate%20to%20a%20certain%20fee%20depending%20on%20the%20year%20that%20the%20fee%20is%20charged.%3C%2FP%3E%3CP%3EFor%20Instance%2C%20If%20a%20project%20goes%20begins%20part%20way%20through%202020%20and%20ends%20in%20feb%20of%202022%2C%20working%20days%20in%202020%20need%20to%20be%20charged%20the%20normal%20rate%2C%20working%20days%20in%202021%20need%20to%20be%20charged%20the%20normal%20fee%20%2B%20the%20escalation%20rate%20*%20normal%20fee%20and%20the%20working%20days%20in%202022%20need%20to%20be%20charged%20the%20normal%20fee%20%2B%20the%20escalation%20rate%5E2%20*%20normal%20fee.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20needs%20to%20be%20applicable%20to%20any%20number%20of%20years%20that%20the%20project%20could%20span%3C%2FP%3E%3CP%3ECheers%20for%20the%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2051454%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2051603%22%20slang%3D%22en-US%22%3ERe%3A%20Apply%20An%20escalation%20rate%20depending%20on%20the%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2051603%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F925630%22%20target%3D%22_blank%22%3E%40graycrumbs%3C%2FA%3E%26nbsp%3BFirst%20of%20all%20I%20assumed%20you%20had%20a%20typo%20and%20the%20you%20really%20wanted%20a%20compounding%20interest%20type%20on%20the%20escalation%20rate%20and%20therefore%20(1%2Bescalation)%5EN%2C%20assuming%20escalation%20is%20a%20%25%20increase%20since%20you%20are%20%2B%20base%20rate%20on%20top%20of%20it.%26nbsp%3B%20It%20isn't%20the%20prettiest%20and%20maybe%20one%20of%20you%20accountants%20know%20of%20some%20fancy%20accounting%20formula%20in%20excel%20to%20make%20it%20easier%2C%20but%20I%20believe%20it%20does%20what%20you%20want%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(start%2CA2%2Cend%2CB2%2Cbaserate%2CC2%2Cescalation%2CD2%2Cholidays%2C0%2Cstartyr%2CNETWORKDAYS(start%2CDATE(YEAR(start)%2C12%2C31)%2Cholidays)%2Cendyr%2CNETWORKDAYS(DATE(YEAR(end)%2C1%2C1)%2Cend%2Cholidays)%2CIFS(start%26gt%3Bend%2C%22bad%20start%2Fend%20dates%22%2CYEAR(end)%3DYEAR(start)%2CNETWORKDAYS(start%2Cend%2Cholidays)%2CYEAR(end)-YEAR(start)%3D1%2Cstartyr%2B(1%2Bescalation)*endyr%2CTRUE%2CLET(yrcount%2CSEQUENCE(YEAR(end)-YEAR(start)-1%2C%2C1%2C1)%2Cyears%2CNETWORKDAYS(DATE(YEAR(start)%2Byrcount%2C1%2C1)%2CDATE(YEAR(start)%2Byrcount%2C12%2C31)%2Cholidays)%2CSUM(startyr%2Cyears*(1%2Bescalation)%5Eyrcount%2Cendyr*(1%2Bescalation)%5E(YEAR(end)-YEAR(start)))))*baserate)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eyou%20set%20the%20location%20of%20the%20start%20date%20(A2)%2C%20end%20date%20(B2)%2C%20base%20rate%20(C2)%2C%20escalation%20rate%20(D2)%20and%20an%20array%20of%20holidays%20(0)%20you%20want%20to%20take%20into%20account.%26nbsp%3B%20you%20can%20play%20with%20it%20in%20the%20attached%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

1 Reply

@graycrumbs First of all I assumed you had a typo and the you really wanted a compounding interest type on the escalation rate and therefore (1+escalation)^N, assuming escalation is a % increase since you are + base rate on top of it.  It isn't the prettiest and maybe one of you accountants know of some fancy accounting formula in excel to make it easier, but I believe it does what you want:

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

you set the location of the start date (A2), end date (B2), base rate (C2), escalation rate (D2) and an array of holidays (0) you want to take into account.  you can play with it in the attached sheet.