| Any geeky nerds about I've an excel question 10:31 - Jul 17 with 1967 views | Keno |
If I know the start value of something was say £100,000 and 5 years later it was worth £140,000 what I the excel formula for working out the annualise rate of growth
Ta xx [Post edited 17 Jul 2023 10:31]
|  |
| |  |
| Any geeky nerds about I've an excel question on 10:33 - Jul 17 with 1903 views | noggin |
The square root of 4 is 2, if that helps. |  |
| |
| Any geeky nerds about I've an excel question on 10:37 - Jul 17 with 1873 views | Keno |
| Any geeky nerds about I've an excel question on 10:33 - Jul 17 by noggin |
The square root of 4 is 2, if that helps. |
I think it may involve Duckworth Lewis | |
| |
| Any geeky nerds about I've an excel question on 10:38 - Jul 17 with 1863 views | Dubtractor |
The net present value formula (npv) will probably help for this.
[Post edited 17 Jul 2023 10:41]
|  |
| |
| Any geeky nerds about I've an excel question on 10:41 - Jul 17 with 1839 views | Keno |
| Any geeky nerds about I've an excel question on 10:38 - Jul 17 by Dubtractor |
The net present value formula (npv) will probably help for this.
[Post edited 17 Jul 2023 10:41]
|
ok Dubs, I know this may be difficult but just for the purposes of this assume I am a complete idiot ...
whats the The net present value formula (nov)? | |
| |
| Any geeky nerds about I've an excel question on 10:44 - Jul 17 with 1823 views | Dubtractor |
| Any geeky nerds about I've an excel question on 10:41 - Jul 17 by Keno |
ok Dubs, I know this may be difficult but just for the purposes of this assume I am a complete idiot ...
whats the The net present value formula (nov)? |
Should have said npv not Nov.
Net present value tells you what something is worth in real terms over a time period. Eg 100 quid in 5 years time won't be worth the same as 100 quid now. I'm thinking some reverse engineering of the formula may help solve your challenge.
But it might not. Possibly there is a simpler growth formula you can use. | |
| |
| Any geeky nerds about I've an excel question on 10:45 - Jul 17 with 1822 views | iamatractorboy |
Assume you mean the average growth as a percentage per annum?
This would be, using your example, ((140000/100000)V(1/5))-1, format it as a percentage. Substitute other numbers as required for starting amount, current amount and years.
The V is meant to be that character you get when you do shift and 6 which looks like an upside down V, can't seem to do it on my phone! [Post edited 17 Jul 2023 10:46]
|  | | |
| Any geeky nerds about I've an excel question on 11:04 - Jul 17 with 1717 views | Keno |
| Any geeky nerds about I've an excel question on 10:44 - Jul 17 by Dubtractor |
Should have said npv not Nov.
Net present value tells you what something is worth in real terms over a time period. Eg 100 quid in 5 years time won't be worth the same as 100 quid now. I'm thinking some reverse engineering of the formula may help solve your challenge.
But it might not. Possibly there is a simpler growth formula you can use. |
thanks
you can stop thinking Im a complete idiot now | |
| |
Login to get fewer ads
| Any geeky nerds about I've an excel question on 11:16 - Jul 17 with 1648 views | Bury_St_Edmundson |
You're looking at an assumed average, without the incremental annual values |  | | |
| Any geeky nerds about I've an excel question on 11:27 - Jul 17 with 1613 views | WeWereZombies |
| Any geeky nerds about I've an excel question on 11:16 - Jul 17 by Bury_St_Edmundson |
You're looking at an assumed average, without the incremental annual values |
That's how I took it, not as re-investing the £112,000 at 12% at the end of year one. You would end up with £176,234 at the end of five years doing that. |  |
| |
| Any geeky nerds about I've an excel question on 11:31 - Jul 17 with 1584 views | Keno |
| Any geeky nerds about I've an excel question on 11:16 - Jul 17 by Bury_St_Edmundson |
You're looking at an assumed average, without the incremental annual values |
I was trying to work out what the annualised return was
If something has grow by a total of 40% over 5 years what would the annualised return be | |
| |
| Any geeky nerds about I've an excel question on 11:36 - Jul 17 with 1565 views | Gogs |
If you enter Y0 to Y5 in cells A1 to F1 and 100000 in A2 and your year end values (if known) in A2 to F2 (just 140000 in F2 will suffice though), in a blank cell, e.g. A5 enter the following:
=(F2/A2)^(1/(6-1))-1
This will give you your answer of 0.06961, i.e. roughly 7%
You can check this by entering 100000 in cell A3, then in B3 entering:
=A3+($A$5+A3)
And copying across to F3, and you’ll see yearly incremental figures ending at 140000 at Y5
Cheers 😁 |  | | |
| Any geeky nerds about I've an excel question on 11:49 - Jul 17 with 1517 views | DanTheMan |
I wish it were something that fun!
What really made it fun was that the calculation doesn't consider the intricacies of how dividends work. In particular that the date the dividend is issued isn't the date that the money arrives in a portfolio, there's usually a few days in lag there (say T+3).
So what happened with annoying frequency is people had large portfolios and were issued dividends, then between the issue date the and payment date they'd withdraw a large chunk of it, and then they'd be paid. When they got paid, it looked like they had enormous returns when that isn't really what happened. Was very annoying to sort out as we didn't have enough information at the time I wrote it, so we didn't know precisely how much of a dividend they'd get, especially taking into account currency conversions. |  |
| |
| Any geeky nerds about I've an excel question on 12:11 - Jul 17 with 1464 views | fishbait1985 |
Compound Annual Growth Rate (CAGR) tells you how much growth would need to occur each year.
Here's a calculator:
https://cagrcalculator.net/result/
In excel use RRI formula which needs Years, Start and End so your formula would be: =RRI(5,100,140)
In this case a fund that starts at £100k and ends at £140k over 5 years would return 6.9% every year [Post edited 17 Jul 2023 12:14]
| | | |
| Any geeky nerds about I've an excel question on 12:42 - Jul 17 with 1387 views | Bury_St_Edmundson |
| Any geeky nerds about I've an excel question on 12:11 - Jul 17 by fishbait1985 |
Compound Annual Growth Rate (CAGR) tells you how much growth would need to occur each year.
Here's a calculator:
https://cagrcalculator.net/result/
In excel use RRI formula which needs Years, Start and End so your formula would be: =RRI(5,100,140)
In this case a fund that starts at £100k and ends at £140k over 5 years would return 6.9% every year [Post edited 17 Jul 2023 12:14]
|
Excel - Goal Seek
start cell (a1) 100,000
% change (a2) 1
then
B1 = a1*$a$2
drag/copy b1 to c1,d1,e1, f1 (so you have 5 "years"
then
Data -> What-if analysis - Goal Seek
set cell: $F$1
to Value: 140,000
by changing cell: $A$1
Excel will then calculate with 1.069610367
or 6.96% YoY
assuming steady annual growth | | | |
| |
By continuing to use the site, you agree to our use of cookies and to abide by our Terms and Conditions.
