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.