How do I use Excel to calculate a trend (x, y coordinates)?

How do I use Excel to calculate the missing value in the data table below indicated by question marks?

Date:               4/1/2005                                  9/15/2009                                          7/31/2013
Amount:             $22,918.80                                   $13,000.00                                              ???????
Comments:      Purchased New       Appraised by Otts Farm Equipment                        Excel
bobbatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Danny ChildIT ManagerCommented:
This should do it... I'll explain below.
EE---linear-trend.xlsx
0
Danny ChildIT ManagerCommented:
As I understand it, the items concerned have lost a fixed price per day since their purchase, and you want to extend this trend to the new date.

So, for the loss per day, it's just
(PurchasePrice - 1stAppraisalPrice) / (1stAppraisalDate - PurchaseDate)

And therefore the loss at the 2nd date is
LossPerDay * (2ndAppraisalDate - PurchaseDate)

and the overall remaining value is
PurchasePrice - LossToDate

The attached table does all this in one hit, and the benefit of making it a table is that it makes the (admittedly complex) formula a bit more readable.

I added the 2nd row as a simple test of the maths.
If an item loses 200 in 10 days, it should lose 600 in 30 days... which it does.
note, with the dates, to get a 10 day gap, you have to specify the 1st and the 11th of the month, not the 10th...

Hope this is what you're after!
cheers, Danny
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.