Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated FV Question
Hi All,
I have a complicated situation, i have the scenario where we have a starting value i.e. £100k and it grows at say 9% per annum, this could be calculated daily/weekly/monthly/quarterly/biannually/annually etc. Say it is monthly, I know i can use =FV(9%/12,12,0,-100,000). Now the situation is complicated by the fact that there is going to be a payment out (i.e. withdrawal) however this is not fixed it is a percentage of the value at that point in time i.e. 12%, it is futher complicated by the fact that this withdrawal is also periodic (i.e. daily/weekly etc) and at a different timescale to the growth of the fund. Finally, the FV is not actually the figure i want, it is the number of periods. i.e. i am trying to work out how long the money will last if it grows at X% and Y% is removed from the fund (where Y X obviously). If i knew the period for each part, i could just work it out but it could be anyone of the six periods for growth and for withdrawals i.e. 36 scenarios, is there any other way than building a massive spreadsheet with each of these 36 scenarios and then working out when the figure gets to 0 and then finding out that period? Thanks for any help you can give. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated FV Question
You should use Tools|Goal Seek
For an excellent visual introduction go to http://mistupid.com/viewlets/excel/xlgoalseek.htm You may also like to see http://www.homeandlearn.co.uk/ME/mes8p4.html -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Emma Hope" wrote: Hi All, I have a complicated situation, i have the scenario where we have a starting value i.e. £100k and it grows at say 9% per annum, this could be calculated daily/weekly/monthly/quarterly/biannually/annually etc. Say it is monthly, I know i can use =FV(9%/12,12,0,-100,000). Now the situation is complicated by the fact that there is going to be a payment out (i.e. withdrawal) however this is not fixed it is a percentage of the value at that point in time i.e. 12%, it is futher complicated by the fact that this withdrawal is also periodic (i.e. daily/weekly etc) and at a different timescale to the growth of the fund. Finally, the FV is not actually the figure i want, it is the number of periods. i.e. i am trying to work out how long the money will last if it grows at X% and Y% is removed from the fund (where Y X obviously). If i knew the period for each part, i could just work it out but it could be anyone of the six periods for growth and for withdrawals i.e. 36 scenarios, is there any other way than building a massive spreadsheet with each of these 36 scenarios and then working out when the figure gets to 0 and then finding out that period? Thanks for any help you can give. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated FV Question
Goal Seek won't work with this number of variables!!!
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated FV Question
Hi,
FV calculates the future value but you want the number of periods so you may want to look at the NPER function. And if that doesn't do it you because your senario is too complex you can combine it with SOLVER, instead of GOAL SEEK. Solver handle much more complicated problems with many variables. First you will need to attach solver, Tools, Add-Ins, and check Solver Add-in. Then you will find Solver on the Toos menu. To get instructions on using solver Google Solver Examples. -- Thanks, Shane Devenshire "Emma Hope" wrote: Goal Seek won't work with this number of variables!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated FV Question
On Oct 17, 7:08*am, Emma Hope
wrote: is there any other way than building a massive spreadsheet with each of these 36 scenarios and then working out when the figure gets to 0 and then finding out that period? Try the following for an approximate solution: =nper((1 + rg / fg)^(fg / fw) * (1 - rw) - 1, 0, -pv, 0.0001) / fw where rg is the annual growth rate, fg is the growth compounding frequency (times per year), rw is the withdrawal rate (use rw/fw if rw is the annual rate), fw is the withdrawal frequency (times per year), and pv is the initial investment. The result of NPER is the number withdrawal periods. So =nper(...)/fw is the number of years. Setting NPER"s "fv" to 0.0001 is a kludge, which may or may not be necessary and which may or may not work for you. Try zero first. I am using Excel 2003. NPER does not work when I set "fv" to zero. It does seem to work when I set "fv" to a "very small number". Theory of operation: (1+rg/fg)^(fg/fw) is approximately the compounded growth factor applied to the initial balance in a withdrawal period. (Note: This might not match real life because payments to the account are not made for fractional growth periods.) 1-rw is the reduction factor applied to the compounded balance at the end of a withdrawal period. If this approximate NPER is not adequate because of the parenthetical note in the previous period, I think you're stuck with "simulating" the growth and withdrawal in a spreadsheet, either by using formulas or by using VBA. Hope this helps. ----- original posting ----- On Oct 17, 7:08 am, Emma Hope wrote: I have a complicated situation, i have the scenario where we have a starting value i.e. £100k and it grows at say 9% per annum, this could be calculated daily/weekly/monthly/quarterly/biannually/annually etc. Say it is monthly, I know i can use =FV(9%/12,12,0,-100,000). Now the situation is complicated by the fact that there is going to be a payment out (i.e. withdrawal) however this is not fixed it is a percentage of the value at that point in time i.e. 12%, it is futher complicated by the fact that this withdrawal is also periodic (i.e. daily/weekly etc) and at a different timescale to the growth of the fund. Finally, the FV is not actually the figure i want, it is the number of periods. i.e. i am trying to work out how long the money will last if it grows at X% and Y% is removed from the fund (where Y X obviously). If i knew the period for each part, i could just work it out but it could be anyone of the six periods for growth and for withdrawals i.e. 36 scenarios, is there any other way than building a massive spreadsheet with each of these 36 scenarios and then working out when the figure gets to 0 and then finding out that period? Thanks for any help you can give. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated FV Question
PS ....
On Oct 17, 4:41*pm, I wrote: =nper((1 + rg / fg)^(fg / fw) * (1 - rw) - 1, 0, -pv, 0.0001) / fw [....] Setting NPER"s "fv" to 0.0001 is a kludge [...]. NPER does not work when I set "fv" to zero. Probably because we can reduce a number by a percentage almost infinitely (limited by computer arithmetic). It is probably good enough to use 0.01 or 0.005 for "fv". After all, this is only an approximation anyway. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated question, for me anyway! :) | Excel Worksheet Functions | |||
Complicated question | Excel Discussion (Misc queries) | |||
Complicated Question | Excel Worksheet Functions | |||
Complicated question... | Excel Worksheet Functions | |||
complicated sum formula question | Excel Worksheet Functions |