Reverse Engineer Annual Cost Escalation Charged to Me

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 HansVogelaar'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)