I am trying to calculate the effective growth rate of costs on a rental
property. I have been using XIRR to calculate the effective CAGR by looking
at first year costs vs current costs, but its not really doing what I want.

For example, if you have year 1 costs of $300 per unit, year 2 costs of
$325/unit, year 3 costs of $335/unit and year 4 costs of $300 per unit, XIRR
would give you 0% since original and current costs are the same. However,
actual total costs are higher than a 0% growth rate.

What I'm really trying to do is this. Total four year costs/unit are
300+325+335+300=1260. What compounded rate of increased costs would give me
the same 4 year total?

I know I can use solver or goal seek to get the answer of 3.26%, but I'm
looking for something more automatic. If I do a macro to automate, the macro
will have to change every month (if I calculate on a monthly basis) since
goal seek and solver do not appear to let you enter a cell for the set value
(i'm using excel 2000; maybe this has changed in future versions). I also
would prefer a formula that takes dates into account (as XIRR does) but I may
be able to work around that. Any suggestions?

thx.

--
Kevin