0

Someone tells that over the next 10 years the value of a house will lose 13%. So if the value is 10000 now, the value after 10 years will be 8700.

Now I want to generate the following table

+------+-----------------+-------+
| Year | Percentage_drop | Value |
+------+-----------------+-------+
|    1 | ?               | ?     |
|    2 | ?               | ?     |
|    3 | ?               | ?     |
|    4 | ?               | ?     |
|    5 | ?               | ?     |
|    6 | ?               | ?     |
|    7 | ?               | ?     |
|    8 | ?               | ?     |
|    9 | ?               | ?     |
|   10 | ?               | 8700  |
+------+-----------------+-------+

How to split equally the whole 13% among the ten years? What is the math formula to generate the missing values (?) ? Is there any general math formula to calculate the missing values (?) with different number of years or percentage? Can this be done in Excel?

Paul
  • 1
  • Do you want the "percentage drop" to be the same every year, or do you want the house value to fall by the same amount every year? – Minus One-Twelfth Feb 15 '19 at 22:31
  • @MinusOne-Twelfth both solutions would be great. However, constant percentage drop is more preferable than amount. – Paul Feb 15 '19 at 22:35
  • Probably the 2 most common ways would be to deduct 130 each year, or to deduct currentamount*(1 - (.87)^(1/10)) each year. – Matthew Liu Feb 15 '19 at 22:37

1 Answers1

1

Hint: If you want a constant percentage drop, say a fall of $r$ every year (e.g. if $r$ were $0.015$, this would mean a $1.5\%$ fall each year), then you need to find the value of $r$ that solves the equation $$10000(1-r)^{10} = 8700.$$

If you want the house value to decline by a constant amount $K$ each year, then you need to find the value of $K$ that solves $$10000-10K=8700.$$

So try and solve these equations (they only require simple algebra), and also preferably try and understand why these equations are so, so that you can generalise them to different number of years or house values etc.

Minus One-Twelfth
  • 7,413