Reverse Engineer Annual Cost Escalation Charged to Me

[ownersrep]

I am using a formula to calculate escalation for a schedule delay. I think this is correct.

Original Price	$563,750
Original Start Date	4/16/2018
New Start Date	7/7/2022
Annual Escalation	3.96475%
Outputs:
Escalated Price	$664,388
Cost of Escalation	$100,638

Using 1,104 delay days, yields ~3.02 years. 

The following formula was typed into cell O6
=B6*(1+N6)^(($J$6-$G$6)/365.25)  

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

COST SUMMARY

DELAY ANALYSIS

Original Cost

Change Order Request

Escalated Price

% Change

Original Start Date

Original End Date

Total Work Days

Adjusted Start Date

Work Day Variance (Days)

Annual Delay (Years)

Annual Escalation Percentage

Verified Escalated Price

6

563,750

100,638

664,388

18%

4/16/2018

9/16/2018

110

7/7/2022

1104

3.02

3.96475%

$664,388

Ultimately I’m trying to show the base fee and change order amount with the annual percentage increase allowing us to measure the delay based solely upon escalation over time. I'd like to verify the above formula was used correctly.

If so, then I have one other issue. I manually entered the annual escalation of 3.96475% in cell N6 so I could figure out what the consultant was charging for the three-year delay.
Is there a way to programmatically find out what escalation percentage actually was used in N6 rather than me fudging the percentages until it matches the value of the new escalated price?

[#2]

@ownersrep 

If you write out the equation, a little bit of mathematics yields

B6*(1+N6)^(($J$6-$G$6)/365.25)=D6   -->

(1+N6)^(($J$6-$G$6)/365.25)=D6/B6  -->

1+N6=(D6/B6)^(365.25/($J$6-$G$6))  -->

N6=(D6/B6)^(365.25/($J$6-$G$6))-1

So the formula to calculate the percentage is

=(D6/B6)^(365.25/($J$6-$G$6))-1

[#3]

@ownersrep 

First, I disagree with your calculation in L6.

You are dividing workdays by average total days per year -- apples and oranges.

Hypothetically, I might divide by average workdays per year.  But that might be difficult to detemine.

If the purpose of L6 is to calculate N6, I would use total days between original and new start dates, which is simply: =(J6-G6)/365.25

Then ostensibly, the rate in N6 can be calculated by:  =RATE(L6, 0, -D6, B6)

(PS.... I just noticed @Hans Vogelaar's response.  The RATE calculation should be the same as his formula, which I would write (D6/B6)^(1/L6) - 1.  But since RATE uses an iterative algorithm, his formula might be more accurate.  In this example, the difference is 0.00000000000161%.)

However, the RATE formula results in 3.96473722142622%, which rounds to 3.96474%, not 3.96475%.

Of course, the consultant is free to set the "escalation" rate any way he wants.  And we cannot know how he does that exactly.

If we assume that he rounds the rate up to the nearest multiple of 0.00005%, the formula might be:  =CEILING(RATE(L6,0,-B6,D6), 0.00005%)

But note that then =B6*(1+N6)^L6 is 664388.344980023, which rounds to 664388.34.  So, you might want to use =ROUND(B6*(1+N6)^L6, 0)

[#4]

Perfect, thank you.

[#5]

Thank you for the catch in L6. I've made the adjustment.