I am not very good with financial worksheet functions and hope that someone 
here can help.  Is there a function that will return how long funds will last 
if you earn a static interest rate and withdraw a static amount of money that 
is more than the interest earned?  Let's say, for example, the amount of 
money is $1,000,000, the interest rate is 4% and the yearly withdrawal is 
$50,000?

homework?


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"ridgerunner" <ridgerunner@discussions.microsoft.com> wrote in message 
news:EBE0BF20-04B0-48C3-BAA4-13EFCD991E33@microsoft.com...
>I am not very good with financial worksheet functions and hope that someone
> here can help.  Is there a function that will return how long funds will 
> last
> if you earn a static interest rate and withdraw a static amount of money 
> that
> is more than the interest earned?  Let's say, for example, the amount of
> money is $1,000,000, the interest rate is 4% and the yearly withdrawal is
> $50,000? 

On Sun, 20 Dec 2009 14:44:01 -0800, ridgerunner
<ridgerunner@discussions.microsoft.com> wrote:

>I am not very good with financial worksheet functions and hope that someone 
>here can help.  Is there a function that will return how long funds will last 
>if you earn a static interest rate and withdraw a static amount of money that 
>is more than the interest earned?  Let's say, for example, the amount of 
>money is $1,000,000, the interest rate is 4% and the yearly withdrawal is 
>$50,000?

Take a look at the NPER worksheet function.
--ron

The NPER (number of periods) function will  do this for you. E.g.,

=NPER(Rate,Payment,PresVal,FutureVal,Type)

where Rate is the annual interest rate = 0.04, Payment is annual
withdrawl = -50,000, FutureVal ending value = 0, Type indicates
whether withdraw is at end or begining of period (0 or 1). 

In your example, you'll exhaust the principle in 37.38 years.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Sun, 20 Dec 2009 14:44:01 -0800, ridgerunner
<ridgerunner@discussions.microsoft.com> wrote:

>I am not very good with financial worksheet functions and hope that someone 
>here can help.  Is there a function that will return how long funds will last 
>if you earn a static interest rate and withdraw a static amount of money that 
>is more than the interest earned?  Let's say, for example, the amount of 
>money is $1,000,000, the interest rate is 4% and the yearly withdrawal is 
>$50,000?

Lookup NPER function in help. Note that in the formula the interest is 
entered as its real decimal value. eg. 4% is 0.04 (or 4/100) and regular 
withdrawals is a negative amount eg. -50000.

-- 
Regards,

OssieMac


"ridgerunner" wrote:

> I am not very good with financial worksheet functions and hope that someone 
> here can help.  Is there a function that will return how long funds will last 
> if you earn a static interest rate and withdraw a static amount of money that 
> is more than the interest earned?  Let's say, for example, the amount of 
> money is $1,000,000, the interest rate is 4% and the yearly withdrawal is 
> $50,000?

Thank you for the great explanation of the formula.  That really helps me 
know how to construct it properly.  My husband and I are retired and are 
trying to determine how not to run out of money.

"Chip Pearson" wrote:

> 
> The NPER (number of periods) function will  do this for you. E.g.,
> 
> =NPER(Rate,Payment,PresVal,FutureVal,Type)
> 
> where Rate is the annual interest rate = 0.04, Payment is annual
> withdrawl = -50,000, FutureVal ending value = 0, Type indicates
> whether withdraw is at end or begining of period (0 or 1). 
> 
> In your example, you'll exhaust the principle in 37.38 years.
> 
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
> 
> 
> 
> 
> 
> On Sun, 20 Dec 2009 14:44:01 -0800, ridgerunner
> <ridgerunner@discussions.microsoft.com> wrote:
> 
> >I am not very good with financial worksheet functions and hope that someone 
> >here can help.  Is there a function that will return how long funds will last 
> >if you earn a static interest rate and withdraw a static amount of money that 
> >is more than the interest earned?  Let's say, for example, the amount of 
> >money is $1,000,000, the interest rate is 4% and the yearly withdrawal is 
> >$50,000?
> .
> 

Thank you for the clarification.  Sometimes these formulas are so cryptic (to 
me any way), I have trouble with them; even when trying to use help.

"OssieMac" wrote:

> Lookup NPER function in help. Note that in the formula the interest is 
> entered as its real decimal value. eg. 4% is 0.04 (or 4/100) and regular 
> withdrawals is a negative amount eg. -50000.
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
> "ridgerunner" wrote:
> 
> > I am not very good with financial worksheet functions and hope that someone 
> > here can help.  Is there a function that will return how long funds will last 
> > if you earn a static interest rate and withdraw a static amount of money that 
> > is more than the interest earned?  Let's say, for example, the amount of 
> > money is $1,000,000, the interest rate is 4% and the yearly withdrawal is 
> > $50,000?