crash2006 Report post Posted May 16, 2010 (edited) ok start again i took 12 month price values per year for 11 years, then i avg out 12 month to give me the yearly avg giving me an avg yearly house price of year avg price in 1998, =£65,067 yearly avg price in 2009 = £156,611. growth rate from 1998 -2009 was 140.69% in 11 years base on this year rate was12.79% per year. ok i did the same with value of loans take out i took 12 month loan values per year for 11 years, then i avg out 12 month to give me the yearly avg giving me an avg yearly loan for year avg price in 1998, =£62,525 yearly avg price in 2009 = £135,608 growth rate from 1998 -2009 was 117.02% in 11 years base on this year rate was10.63% per year. Edited May 16, 2010 by crash2006 Quote Share this post Link to post Share on other sites
Bloo Loo Report post Posted May 16, 2010 ok i have avg house price at 61334.26 - 164518.67 - 12.0166%I, n 1997, the house price was 61334.26 . This grew to 164518.67 in 2010. It grew 12 percent between 1997 and 2010 or at an rate of 1.2 percent annually. is that correct? not if the price was to the power of 2. Quote Share this post Link to post Share on other sites
ScrewsNutsandBolts Report post Posted May 16, 2010 The numbers in your post seem to have gone a bit funny on the formatting front. If you are saying £61334 in 1997 and £164518 in 2010, then the increase has been ((164518-61334)/61334) x 100 = 168 percent over that period. If you are saying it is twelve years, then you can not just divide that by 12 and say that it is 14 percent per year. The percentage increase in the second year, would not just be on £61334, it would also be on the increase from the first year. Basic principal of compound interest. You want to find Y in the following: 61334 x Y^12 = 164518 which gives a Y of around 1.085 which means 8.5 percent average increase over the 12 year period. Probably not very clearly explained Quote Share this post Link to post Share on other sites
Bubble&Squeak Report post Posted May 16, 2010 (edited) ok i have avg house price at 61334.26 - 164518.67 - 12.0166%I, n 1997, the house price was 61334.26 . This grew to 164518.67 in 2010. It grew 12 percent between 1997 and 2010 or at an rate of 1.2 percent annually. is that correct? I would work it out this way... although I could be wrong or not understand your post £61334.26 original price £164518.67 current value £103184.41 Total increase (total increase / original price) x 100 = 168.23% over 13 years Annual increase = (total increase / 13 years) = £7937.262308 av increase per year Annual average percentage = (Annual increase / original price) x 100 = 12.94% but of course this is only a very rough amount as you are looking at it as a whole from the end rather than per year, but it does for back of a fag packet type f calculations TBH Edit to add: This is quite a good question actually as it has made me brush up on my maths... The correct answer on a calculator is: 1/number of years (1/13) (save the result) (final value / start value) raised to the answer above (164518.67/ 61334.26 to the power of 0.083333333) subtract 1 and multiply by 100 to format as a percentage = 7.89% annual growth Year Price Increase1997 61334.26 4836.421998 66170.68 5217.791999 71388.47 5629.232000 77017.70 6073.112001 83090.81 6552.002002 89642.81 7068.652003 96711.46 7626.032004 104337.49 8227.372005 112564.87 8876.132006 121441.00 9576.052007 131017.04 10331.152008 141348.19 11145.802009 152493.99 12024.682010 164518.67 12972.87 Edited May 16, 2010 by Bubble&Squeak Quote Share this post Link to post Share on other sites
Dave Spart Report post Posted May 16, 2010 (edited) Assuming the period over which the price grew was 13 years then the compound annual rate of growth would have been 7.885347843721% per annum. 64133.26*(1.07885347843721)^13 = 164518.67 The general formula for finding the compound growth rate is : (((New Price)/(Original Price))^(1/Duration))-1 You can solve this either in Windows Calculator or Excel. Edited May 16, 2010 by Dave Spart Quote Share this post Link to post Share on other sites
rockhopper Report post Posted May 16, 2010 initial price = 61334 final price = 164519 after 13years ratio = 164519 / 61334 using tclsh % expr 164519.0 / 61334.0 2.68234584407 << ratio % expr log(2.68234584407) 0.98669172661 << take log % expr log(2.68234584407) / 13 0.0758993635853 << div by num years % expr exp(0.0758993635853) 1.0788539967 << convert to ratio per year ie 7.9% % expr pow(1.0788539967,13) 2.68234584421 << check by raising 7.9% to 13 years = what we started with so gain per year is 7.9% go on prove me wrong you know you want to rockhopper Quote Share this post Link to post Share on other sites
Giordano Bruno Report post Posted May 16, 2010 (edited) I would work it out this way... although I could be wrong or not understand your post £61334.26 original price £164518.67 current value £103184.41 Total increase (total increase / original price) x 100 = 168.23% over 13 years Annual increase = (total increase / 13 years) = £7937.262308 av increase per year Annual average percentage = (Annual increase / original price) x 100 = 12.94% but of course this is only a rough amount as you are looking at it as a whole rather that per year, but it does for bag of a fag packet TBH You are wrong. SNaB seems to be correct (on a hasty reading).It is easier to plug the figures in early on, but if you don't mind a bit of algebra:- If the value is x at the start and y is the value at the end of n years, and if z is the annual percentage increase over the period:- y/x is the proportion after n years (w.r.t the starting value x). 1 + z/100 is the proportion after one year. (1 + z/100)^n is the proportion after n years. i.e. (1 + z/100)^n = y/x => 1 + z/100 = (y/x)^(1/n) => z = 100*[(y/x)^(1/n) - 1] x = 61334 y = 164518 n = 13 so, z = 100* [2.6823^(1/13)-1] = 100* [1.07885 -1] = 7.885% I'm not sure if that will help. Edited May 16, 2010 by Giordano Bruno Quote Share this post Link to post Share on other sites
Bubble&Squeak Report post Posted May 16, 2010 (edited) . Edited May 16, 2010 by Bubble&Squeak Quote Share this post Link to post Share on other sites
Guest Steve Cook Report post Posted May 16, 2010 (edited) ok i have avg house price at 61334.26 - 164518.67 - 12.0166%I, n 1997, the house price was 61334.26 . This grew to 164518.67 in 2010. It grew 12 percent between 1997 and 2010 or at an rate of 1.2 percent annually. is that correct? See the excel model below I have just knocked up. You can vary the % annual increase in price and also the initial value to work out the average hpi per year given an arbitrarily decided final value. I've zipped the excel file up with windows compression facility because this forum doesn't seem to allow excel files to be directly uploaded for some reason. If you click on it, it will use windows unzip facility and allow you to open or save it trial and error model of hpi over a given poeriod.zip trial and error model of hpi over a given poeriod.zip Edited May 16, 2010 by Steve Cook Quote Share this post Link to post Share on other sites
TheBlueCat Report post Posted May 16, 2010 For discrete compounding, the formula you need is: final value = initial value * (1 + r)^t Where r = interest rate for the period (i.e. the annual growth rate you're looking to find) and t is the number of time periods (i.e. 13 years). Solve for r and you're done. If you're looking to compare this with other financial growth numbers you see out there, be aware that a lot of them are calculated using continuous rather than discrete compounding, in which case, the formula you need is: final value = initial value * e^rt Quote Share this post Link to post Share on other sites
Guest Steve Cook Report post Posted May 16, 2010 (edited) For discrete compounding, the formula you need is: final value = initial value * (1 + r)^t Where r = interest rate for the period (i.e. the annual growth rate you're looking to find) and t is the number of time periods (i.e. 13 years). Solve for r and you're done. If you're looking to compare this with other financial growth numbers you see out there, be aware that a lot of them are calculated using continuous rather than discrete compounding, in which case, the formula you need is: final value = initial value * e^rt mine's a lot simpler.... Actually that's not fair, mine is essentially what you have described. All you've got to do is plug in the initial value and rate of increase in price and see if the final price at the end of the period matches reality. If it doesn't then you change the rate of increase until it does Edited May 16, 2010 by Steve Cook Quote Share this post Link to post Share on other sites
Bloo Loo Report post Posted May 16, 2010 See the excel model below I have just knocked up. You can vary the % annual increase in price and also the initial value to work out the average hpi per year given an arbitrarily decided final value. I've zipped the excel file up with windows compression facility because this forum doesn't seem to allow excel files to be directly uploaded for some reason. If you click on it, it will use windows unzip facility and allow you to open or save it being lazy, id have used the trial and error method....with a good estimate start point you can straddle the correct result and score a hit very quickly. not sure if excel still has this iteration function to do this automatically. Quote Share this post Link to post Share on other sites
Bubble&Squeak Report post Posted May 16, 2010 See the excel model below I have just knocked up. You can vary the % annual increase in price and also the initial value to work out the average hpi per year given an arbitrarily decided final value. I've zipped the excel file up with windows compression facility because this forum doesn't seem to allow excel files to be directly uploaded for some reason. If you click on it, it will use windows unzip facility and allow you to open or save it Nice... Hope you don't mind but I have added a % increase calculator sheet to your workbook and reposted, very useful for the mathematically challenged like me trial and error model of hpi over a given poeriod.zip trial and error model of hpi over a given poeriod.zip Quote Share this post Link to post Share on other sites
Guest Steve Cook Report post Posted May 16, 2010 being lazy, id have used the trial and error method....with a good estimate start point you can straddle the correct result and score a hit very quickly. not sure if excel still has this iteration function to do this automatically. Mine is just the trial and error method as well bloo loo. You just vary the rate of increase until the final price matches reality. I suppose you could get excel to start at .1 % and keep adding .1 %to the rate of increase variable cell until the final price matched the value in another cell where you had manually inputted a given value. I think.... Quote Share this post Link to post Share on other sites
Guest Steve Cook Report post Posted May 16, 2010 (edited) Nice... Hope you don't mind but I have added a % increase calculator sheet to your workbook and reposted, very useful for the mathematically challenged like me cool... A darn site more concise than mine... Edited May 16, 2010 by Steve Cook Quote Share this post Link to post Share on other sites
Bloo Loo Report post Posted May 16, 2010 Mine is just the trial and error method as well bloo loo. You just vary the rate of increase until the final price matches reality. I suppose you could get excel to start at .1 % and keep adding .1 %to the rate of increase variable cell until the final price matched the value in another cell where you had manually inputted a given value. I think.... ah yes, without looking it all up, excel still has a Goal Seek method, as well as a Solver. still, its more fun to guess IMHO Quote Share this post Link to post Share on other sites
Guest Steve Cook Report post Posted May 16, 2010 (edited) Nice... Hope you don't mind but I have added a % increase calculator sheet to your workbook and reposted, very useful for the mathematically challenged like me . Edited May 16, 2010 by Steve Cook Quote Share this post Link to post Share on other sites
Guest Steve Cook Report post Posted May 16, 2010 (edited) Nice... Hope you don't mind but I have added a % increase calculator sheet to your workbook and reposted, very useful for the mathematically challenged like me Actually, I think one of our calculations must be wrong as they don't arrive at the same end figure.I've posted a copy of the one you posted and have put a simple compound list next to it. Mine arrives at a different figure. however, that figure matches my initial model on the previous sheet. If, it's me that has made a mistake, I would appreciate you showing me where I've gone wrong. trial and error model of hpi over a given poeriod2.zip trial and error model of hpi over a given poeriod2.zip Edited May 16, 2010 by Steve Cook Quote Share this post Link to post Share on other sites
Bubble&Squeak Report post Posted May 16, 2010 Actually, I think one of our calculations must be wrong as they don't arrive at the same end figure.I've posted a copy of the one you posted and have put a simple compound list next to it. Mine arrives at a different figure. however, that figure matches my initial model on the previous sheet. If, it's me that has made a mistake, I would appreciate you showing me where I've gone wrong. You have only included 12 increases + some rounding issues I think, see the attached and you'll see what I mean. This is why this sort of thing is so hard to do in yer 'ead trial and error model of hpi over a given poeriod2.zip trial and error model of hpi over a given poeriod2.zip Quote Share this post Link to post Share on other sites
Guest Steve Cook Report post Posted May 16, 2010 (edited) You have only included 12 increases + some rounding issues I think, see the attached and you'll see what I mean. This is why this sort of thing is so hard to do in yer 'ead Ah yes, I can see I have only included 12 years instead of thirteen. However, the rounding issue has me completely baffled. The two different methods of calculating this in excel should have produced identical results should they not? Oh no, I get it, your model has a number of extra decimal points buried in the % rate of increase that makes it more precise than mine I think.... Edited May 16, 2010 by Steve Cook Quote Share this post Link to post Share on other sites
Bubble&Squeak Report post Posted May 16, 2010 (edited) Ah yes, I can see I have only included 12 years instead of thirteen. However, the rounding issue has me completely baffled. The two different methods of calculating this in excel should have produced identical results should they not? Oh no, I get it, your model has a number of extra decimal points buried in the % rate of increase that makes it more precise than mine I think.... Yes exactly; I am using a cell format of percentage with 2 decimal places, the figure calculated in the field is actually 0.078853478 (or 7.8853478%) Edited May 16, 2010 by Bubble&Squeak Quote Share this post Link to post Share on other sites
TheBlueCat Report post Posted May 16, 2010 I'm going to sound like a maths teacher here, so sorry in advance. Using an iterative approach to solving something that has a simple analytical solution makes no sense. The equation for discrete compounding can be solved for r as follows: r = 10^(log(F/I)/t)-1 And for continuous: r = ln(F/I)/t Where I is the initial price of the asset, F is in the final price, t is the number of time periods and r is rate (0 = 0%, 1 = 100%). I've attached a spreadsheet with both of these calculations in. It will work in constant time for any number of periods up to the point where excel runs out of precision. No need to add new cells or anything else like that. interest.zip interest.zip Quote Share this post Link to post Share on other sites