Help with formula for Capital replacement Cost 3

[jeepdakota]

Setting up capital budget workshee, I need to take the purchase date of 1 column and the replacement date of another. Then come up with replacement cost in the last column. I would like to add 4% to every year.

So it would be like this purchase 1999
Replacement date 2004
Add 4% to each year
Come up with Replacement Cost for 2004 for that particular item.

Hope you understand this, Thanks for any help

 

[SkipVought]

Then tell me how my formula fails. WHAT IS WRONG!

How do you know it's wrong!!!!!!!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jeepdakota]

Skip

I tried your example(s), couldn't get them to make any sense. Sorry, maybe it's just me?

 

[jeepdakota]

You have my worksheet, try your formula based on what I'm looking for. Let me know if it works.

 

[jdhilljr]

Send it to jim.hill@procollectortips.com

Jim

 

[SkipVought]

BYW,

Your formula...

purchase price x replacement date x 4% each year = Replacement cost

does not make sense. Why are you factoring by REPLACEMENT DATE?

Your column, Life Expectancy is the number of DAYS bewteen Date Purchased & Replacement Year.

So YEARS is DAYS/365 or some other value very close to 365.

So I would expect that the formula would be

=PurchasePrice*(1+(DateDifferenceInYEARS)*0.04)

TELL ME...

What's wrong with this formula?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jeepdakota]

Skip

I'm sorry but I am trying to learn just everyone else here. It's obvious by your tone that you no everything and have run out of patience. So, thank you for your help. Have a nice weekend, I will try with someone else.

 

[jdhilljr]

This will give you the results.
Make sure you format the cell for currency.


=G4*POWER(1.04,((I4-F4)/365))
Jim

 

[SkipVought]

Jeep,

I keep asking for information and you keep ignoring what I ask for.

Yes, I am frustrated, but I have NOT run out of patience.

I just sent your workbook back with a page filled with Replacement Cost.

Please tell me HOW this result is incorrect...

NOT just that it IS incorrect.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

OK Jeep, you got to work with us here...

Let me ask you this,

If you have orginal cost 1000
and life expectancy of 6 years...

at 4 percent, would the replacement cost be $1265.32 ?



Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Blue,

I get $1249.

How did you calculate yours?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jdhilljr]

The difference is simple or compound interest.

Jim

 

[bluedragon2]

Skip,

I used 1000*(POWER(1.04,6))

I used that on the bases of:

Base Year: 1000.00
Year 1 +4%: 1040.00
Year 2 +4%: 1081.60
Year 3 +4%: 1124.86
Year 4 +4%: 1169.86
Year 5 +4%: 1216.65
Year 6 +4%: 1265.32



Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Jd,

Your answer and blue's answer are identical.

It must be a compounded rate result. Mine is an uncompounded rate.

So it depends what jeep is looking for. It has just been hard to get out of him just WHAT he is looking for. All I know is what he is NOT looking for.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

And what the heck did I get a star for



Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

well jeep,

jd had the answer BEFORE blue 6 posts ago!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Blue,

I got one too???

Beats me? Getting relevant info has been like pulling teeth! So I can't tell WHAT'S going on????

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Skip: You aren't compounding annually. That's why Jim is using the POWER function.

Jim: The difference between two dates is not the number of years. That's why Skip is dividing by 365.

Blue: I agree with your answer.

Jeep: You haven't been here long enough to pass judgement on Skip or his attitude. He is in fact one of the most helpful (if not THE most helpful) members here. He asked you for sample data and expected result. You still haven't responded. Very difficult to test our formulas if you don't give us something concrete to work with. Also, how do you handle the fractional year cases? Does the replacement cost jump 4% when the elapsed time goes from 5 years, 364 days to 6 years? Or is some pro-rata calculation made (e.g. similar to the half-year depreciation method?)

 

[wrbnoh]

Jeep,

I'm no programming expert either, but based on your information, Blue's suggestion is what you want.

IE.. a $5000 asset Purchased on 1/1/99 with a 5 year replacement date of 1/1/2004 @ 4% appreciation per year would result in a replacement cost of $6,083.27

Pur Date Orig Cost Life Repl Date Replacement Cost
1/1/1999 5000.00 5 12/31/2003 6083.264512


=B2*(POWER(1.04,C2))

 

[Zathras]

Jim: Sorry, I just noticed in your later posts that you were dividing by 365.

 

[wrbnoh]

I gave you the Star - Blue - you earned it