Cost increase rate

[Kevin]

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.

 

[Shane Devenshire]

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.

 

[Dana DeLouis]

Hi. Would a simple macro function work? This version doesn't have
error checking. It assumes your data is in a vertical area of the
worksheet, such as A1:A4

=MyRate(A1:A4)
0.0326182491815394

Function MyRate(v)
Dim t, n, p, s, k
Dim r, og, ct

t = WorksheetFunction.Sum(v)
n = v.Cells.Count
s = v(1)
r = 0.1
Do
og = r 'Old Guess
k = r + 1
p = k ^ n
r = r - (k * r * (s * (1 - p) + r * t)) / _
(p * s * (k - r * n) - k * s)
ct = ct + 1
Loop While r <> og And ct < 30
MyRate = r
End Function


= = =
HTH
Dana DeLouis

 

[Kevin]

As I said in my original message, I know I can use the solver tool to get the
answer, but the 'equal to' value is a manual entry rather than a cell. Each
month I get new cost information and I don't want to have to reset the solver
tool each month on multiple properties for multiple cost calcutions each
month. Even if I create a macro, the set value will need to change each
month. I want to set something up so the calcs are done automatically.

thx for your response.