Jan 11 2021 06:57 PM
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
Jan 11 2021 09:04 PM
@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.
Feb 13 2023 03:12 PM
Feb 13 2023 03:37 PM
@MegArchSTN i don't understand your comment. I downloaded that file, copied the line down and changed the escalation on each line and seems to affect it:
maybe you can explain better what isn't working for you or how it is acting on your copy?
Feb 14 2023 07:41 AM
Define "Net Calc". Is it dollars? Days? The description made it sound like you would enter the initial fee or rate which in my case would be $/hr and Net Calc would be the average rate over the duration. No?
start date | end date | rate | escalation | Holidays | Net Calc |
2/20/2023 | 4/14/2023 | $1.00 | 0.04 | 40.00 | |
2/20/2023 | 4/14/2024 | $1.00 | 0.04 | 303.00 | |
2/20/2023 | 4/14/2028 | $1.00 | 0.04 | 1469.95 |
Feb 14 2023 08:08 AM
@MegArchSTN Basically the original posting requested a formula that would increase the rate each new year by the escalation amount. So in your example line 1 is at $1/day and 40 workdays. Line 2 is a little more than a year so for 225 workdays of the initial year it is billed at $1/day but then the 75 workdays of the next year it is billed at 1.04/day resulting in 303 instead of 300. see this example:
line 1 shows same as yours. line 2 shows days in year 2023. line 3 shows days in year 2024 (because the start date is 1/1/2024 the rate on line 3 stays $1/day) and then line 4 shows your line 2 and how the total changes because it crossed over to a new year. Hope that helps you understand what this formula does. If you need something else, feel free to post a new message/thread.
Mar 16 2023 05:21 AM
Mar 16 2023 05:29 AM
Mar 16 2023 07:31 AM
Mar 16 2023 08:40 AM