Complicated FV Question

[Emma Hope]

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.

 

[Sheeloo]

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

 

[Emma Hope]

Goal Seek won't work with this number of variables!!!

 

[ShaneDevenshire]

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.

 

[joeu2004]

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 -----

 

[joeu2004]

PS ....

=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.