Calculate How Long Funds Will Last

R

ridgerunner

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

Ron Rosenfeld

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
 
C

Chip Pearson

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

OssieMac

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

ridgerunner

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 said:
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]





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?
.
 
R

ridgerunner

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top