Hello,

What do you think is the best formula to calculate in Excel an
extimated value in january 1st 2008 of the used car bought new in july
1st 2004, only having the following data of it?

(today value - new: $9900)
today's value - used car bought new in july 1st 2004: $6200
today's value - used car bought new in january 1st 2004: $5700
today's value - used car bought new in july 1st 2003: $5200

I would like to find a formula that doesn't return a negative value if
I use it for example on an extimation on 1/1/2100 instead of 1/1/2008,
like the standard forecast formula do.

Any ideas?

Thanks in advance

Use the GROWTH function:
=GROWTH(B2:B5,A2:A5,A6,TRUE)

where A2:A5 are the known times (date of purchase, DOP + 1 year, DOP +
18 months, DOP + 2 years) and B2:B5 are the known prices (9900, 6200,
5700, 5200), and A6 is the forecast date.

Using this formula, with the data of purchase 1 July 2004, I get the
value on 1/1/2008 to be $3013.48 and the value on 1/1/2025 to be
$11.91. On 1/1/2100 it drops down to $0.0000000003.

Hi,

I tried but it seems to not work as I expect.
What is not working correctly? why do it give a 51665.68?

01/07/2004	? 6200.00
01/01/2004	? 5700.00
01/07/2003	? 5200.00
01/01/2003	? 51665.68   (=GROWTH($A$1:$A$3;$B$1:$B$3;A4;TRUE))

It looks like you lose a big chunk in year 1 (of 3700), then 1000 per
year (so 500 each half year).
So you buy in jul04 at about 9900 (or about 9700 if you think what the
new price was in jul2004, and add "reverse inflation" at 2pc). Then
lose 3700, then lose 500 per half year. So by jan08 after 6 lots of
500 you have a car worth about 3000.

I'm not convinced that a more complex model would be helpful -
prediction way out of your data set is never a great plan, and you
should think about checking that the model you use is working
properly...