Financial - How long will "x" dollars last at "A" $/year & i%

  • Thread starter Thread starter Doug Dallmer
  • Start date Start date
D

Doug Dallmer

A little old fashion math problem.

I have put together a spreadsheet for my kids for planning
purposes. Generally they have no problem plugging in the
numbers and understanding what it says the nest egg will be
in "n" years.

Their question (that I cannot find the equation for) is:

Given that I have $500,000 (P) in some year in the future:

1-How many years (n) will it last (down to $0) at " A "
($/year) withdrawal while earning " i " interest rate?

--OR--

2-What annual payment (A) can I take over " n " years while
earning " i " interest rate before it goes to $0?

Where:
P = Principle amount when we start at a year in the future.
A = Annual $/year taken out.
i = annual interest rate.
n = number of years

I realize that inflation will be a complicating factor but
I plan to ignore it.

I have googled for the equation. Got lots of stuff but not
sure what the equation is supposed to be.

Can anyone help?

Doug
 
Doug,

There are Excel functions for this. To start, you can try

=NPER(5%,-64752,500000,0)
=PMT(5%,10,500000,0)

where you can see that
1. 500,000 will last 10 years with a yearly withdrawal of 64,752, and
2. with 500,000 you can yearly withdraw 64,752 in 10 years

In the real world maybe you would withdraw the money monthly (or quarterly)
instead of yearly, but that is just a matter of multiplying the years by 12 an
dividing the interest rate by 12.

It also makes a difference if you withdraw the money at the beginning or in the
end of the period.

For more details, take a look in Excel Help for these and other related
financial functions.

HTH
Anders Silvén
 
Back
Top