Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with formula for Capital replacement Cost 3

Status
Not open for further replies.
jeepdakota

jeepdakota

Technical User
Oct 3, 2003
12
US
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
 
Sort by date Sort by votes
Skip

I tried your example(s), couldn't get them to make any sense. Sorry, maybe it's just me?
 
Upvote 0 Downvote
You have my worksheet, try your formula based on what I'm looking for. Let me know if it works.
 
Upvote 0 Downvote
BYW,

Your formula...
Code: 
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
Code: 
=PurchasePrice*(1+(DateDifferenceInYEARS)*0.04)
TELL ME...

What's wrong with this formula?



Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
This will give you the results.
Make sure you format the cell for currency.


=G4*POWER(1.04,((I4-F4)/365))
Jim
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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 [dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
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 [dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
And what the heck did I get a star for :)



Blue [dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
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?)


 
Upvote 0 Downvote
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))


 
Upvote 0 Downvote
Jim: Sorry, I just noticed in your later posts that you were dividing by 365.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
303
SkipVought
S
TomSalvato
  • Locked
  • Question
EXCEL-formula help 2
Replies
6
Views
175
TomSalvato
TomSalvato
Tropometer
  • Locked
  • Question
Help with dates
Replies
1
Views
195
SkipVought
S
scubafrog79
  • Locked
  • Question
Formula for checking cells for value and then subtract value 1
Replies
8
Views
216
SkipVought
S
cneill
  • Locked
  • Question
Hi, I need some help with Pre an
Replies
7
Views
174
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top