Hi,

the effective rate is 1.0326182492429

You can obtain this result by using Solver.

In cell A1 enter 300
In A2 enter =A1*C1
in A3 enter =A2*C1
in A4 enter =A3*C1
in A5 enter =SUM(A1:A4)

1. Select cell A5 and choose Tools, Solver
2. The Target Cell is A5
3. Set Equal to to Value of and enter 1260
4. Set By Changing Cells to cell C1
5. Click Solve

Solver is an Excel Addin so choose Tools, Add-ins, and check it.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Kevin" wrote:

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