MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Calculate How Long Funds Will Last

• Follow

```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?
```
 0

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

```
 0

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

```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?
```
 0

```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?
```
 0

```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?
> .
>
```
 0

```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?
```
 0

6 Replies
1521 Views

Similiar Articles:

8/1/2012 12:19:42 PM