Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cost increase rate
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cost increase rate
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cost increase rate
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 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cost increase rate
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. -- Kevin "Shane Devenshire" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INCREASE COST COLUMN BY A PERCENTAGE | Excel Worksheet Functions | |||
Product of Hrs & minutes times rate per hr to give a cost | Excel Discussion (Misc queries) | |||
Starting payment when increase rate is known & total payment is kn | Excel Worksheet Functions | |||
How to calculate total cost using fixed hourly rate from [h:mm:ss:]? | Excel Discussion (Misc queries) | |||
How do I calculate the cost of credit figured as a yearly rate? | Excel Worksheet Functions |