Calculate How Long Funds Will Last
 

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?

Answer: Calculate How Long Funds Will Last
 

Yes, there is a function in Excel that can help you calculate how long funds will last based on the given parameters. The function is called "NPER" and it calculates the number of periods required to pay off a loan or investment based on a constant payment and a constant interest rate.

To use the NPER function to calculate how long funds will last, follow these steps:

1. Open a new Excel worksheet and enter the following information in cells A1 to A3:
   - A1: $1,000,000 (initial amount of money)
   - A2: 4% (annual interest rate)
   - A3: -$50,000 (yearly withdrawal amount, entered as a negative value)
   
2. In cell A4, enter the following formula:
   Code:

   ---

   =NPER(A2/12,A3,A1)

   ---

3. Press Enter to calculate the number of periods required to exhaust the funds.

The NPER function takes three arguments:
- rate: the interest rate per period (in this case, the annual interest rate divided by 12 to get the monthly interest rate)
- pmt: the constant payment made each period (in this case, the yearly withdrawal amount entered as a negative value)
- pv: the present value of the investment (in this case, the initial amount of money)

The result of the NPER function is the number of periods required to exhaust the funds, which is expressed in months. To convert this to years, divide the result by 12.

For example, if the result of the NPER function is 20 years, 4 months, you can divide the number of months by 12 to get the number of years: 20.33 years.

So in this example, the funds will last approximately 20 years and 4 months if the interest rate remains constant and the yearly withdrawal amount is $50,000.

Calculate How Long Funds Will Last
 

homework?


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ridgerunner" wrote in message
...
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?

Calculate How Long Funds Will Last
 

On Sun, 20 Dec 2009 14:44:01 -0800, 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?

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

Calculate How Long Funds Will Last
 

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

Calculate How Long Funds Will Last
 

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?

Calculate How Long Funds Will Last
 

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

Calculate How Long Funds Will Last
 

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?