proposed formula for second hand value:
Assuming column A holds the purchase price, B the purchase date, try this formula in column C:
(the formula below is for cell C3)
=1000 * 0.7^( (NOW()-$B3)/365) + ($A3-1000) * 0.65 ^( (NOW()-$B3)/365)
Example 1: Premium brand bike
today is | 17/10/2013 | |
purchase price | purchase date | second hand value |
2700 | 17-Oct-13 | 2698 |
2700 | 17-Oct-12 | 1804 |
2700 | 17-Oct-11 | 1206 |
2700 | 17-Oct-10 | 808 |
2700 | 17-Oct-09 | 543 |
2700 | 17-Oct-08 | 365 |
2700 | 17-Oct-07 | 245 |
2700 | 17-Oct-06 | 165 |
2700 | 17-Oct-05 | 112 |
2700 | 17-Oct-04 | 75 |
Example 2: commodity bike:
today is | 17/10/2013 | |
purchase price | purchase date | second hand value |
800 | 17-Oct-13 | 800 |
800 | 17-Oct-12 | 570 |
800 | 17-Oct-11 | 405 |
800 | 17-Oct-10 | 288 |
800 | 17-Oct-09 | 204 |
800 | 17-Oct-08 | 145 |
800 | 17-Oct-07 | 102 |
800 | 17-Oct-06 | 72 |
800 | 17-Oct-05 | 51 |
800 | 17-Oct-04 | 36 |
The constants:
average bike price as a transport: £1000.
0.7: depreciation rate for the commodity element, 0.65: depreciation rate for the premium element.