I am looking for the right formula to use for the following
scenario. If I had $500,000 in an account at 5% interest per year
over a 15 year period and wanted the ending balance to be $250,000,
how much can I draw out per month and meet the criteria of still
having $250,000 at the end. I used PPMT but the results don't look
correct to me.

Thanks, Steve

Hi Steve,

=PMT(5%,15,500000,-250000)

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve" wrote in message ...
|I am looking for the right formula to use for the following
| scenario. If I had $500,000 in an account at 5% interest per year
| over a 15 year period and wanted the ending balance to be $250,000,
| how much can I draw out per month and meet the criteria of still
| having $250,000 at the end. I used PPMT but the results don't look
| correct to me.
|
| Thanks, Steve

On Feb 19, 12:18*pm, "Niek Otten" wrote:
Hi Steve,

=PMT(5%,15,500000,-250000)

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve" wrote in ...

|I am looking for the right formula to use for the following
| scenario. * If I had $500,000 in an account at 5% interest per year
| over a 15 year period and wanted the ending balance to be $250,000,
| how much can I draw out per month and meet the criteria of still
| having $250,000 at the end. *I used PPMT but the results don't look
| correct to me.
|
| Thanks, Steve

Thanks Niek, I didn't know to list the future value as negative for
whatever reason that requires ?

Hi Steve,

You put the 500000 in and take the 250000 out at the end. Opposite direction, just as the "payments"

Look here for a reasonably good description of Excel's family of financial functions:

http://office.microsoft.com/en-us/ex...117451033.aspx


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Steve" wrote in message ...
On Feb 19, 12:18 pm, "Niek Otten" wrote:
Hi Steve,

=PMT(5%,15,500000,-250000)

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve" wrote in ...

|I am looking for the right formula to use for the following
| scenario. If I had $500,000 in an account at 5% interest per year
| over a 15 year period and wanted the ending balance to be $250,000,
| how much can I draw out per month and meet the criteria of still
| having $250,000 at the end. I used PPMT but the results don't look
| correct to me.
|
| Thanks, Steve

Thanks Niek, I didn't know to list the future value as negative for
whatever reason that requires ?

Cash flow in Excel is negative if it goes away from you and positive if it
comes to you. You have to invest 500,000 thus that is going out of your
pocket and is negative. The 250,000 at the end is coming to you and thus is
positive. Another example: if you compute monthly payments on a loan of
10,000 at 10% annual interest for 10 years, you use =PMT(10%/12,10*12,10000)
and this produces the negative result of -132.15. The 10,000 is income to
you and is postitive, and the 132.15 is outgo from you and is therefore
negative.


Tyro

"Steve" wrote in message
...
On Feb 19, 12:18 pm, "Niek Otten" wrote:
Hi Steve,

=PMT(5%,15,500000,-250000)

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve" wrote in
...

|I am looking for the right formula to use for the following
| scenario. If I had $500,000 in an account at 5% interest per year
| over a 15 year period and wanted the ending balance to be $250,000,
| how much can I draw out per month and meet the criteria of still
| having $250,000 at the end. I used PPMT but the results don't look
| correct to me.
|
| Thanks, Steve

Thanks Niek, I didn't know to list the future value as negative for
whatever reason that requires ?

On Feb 19, 3:47*pm, "Tyro" wrote:
Cash flow in Excel is negative if it goes away from you and *positive if it
comes to you. You have to invest 500,000 thus that is going out of your
pocket and is negative. The 250,000 at the end is coming to you and thus is
positive. *Another example: if you compute monthly payments on a loan of
10,000 at 10% annual interest for 10 years, you use =PMT(10%/12,10*12,10000)
and this produces the negative result of -132.15. *The 10,000 is income to
you and is postitive, and the 132.15 is outgo from *you and is therefore
negative.

Tyro

"Steve" wrote in message

...
On Feb 19, 12:18 pm, "Niek Otten" wrote:





Hi Steve,

=PMT(5%,15,500000,-250000)

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve" wrote in
...

|I am looking for the right formula to use for the following
| scenario. If I had $500,000 in an account at 5% interest per year
| over a 15 year period and wanted the ending balance to be $250,000,
| how much can I draw out per month and meet the criteria of still
| having $250,000 at the end. I used PPMT but the results don't look
| correct to me.
|
| Thanks, Steve

Thanks Niek, I didn't know to list the future value as negative for
whatever reason that requires ?- Hide quoted text -

- Show quoted text -

Just an interesting observation that if withdrawals are at the end of
the month, total withdrawals over the 15 year period are $26,132 more
than if withdrawals are at the beginning of the month. That
difference amounts to 5.23% of the initial principal of $500,000. The
miracle of compounding, as it were.

Is the money deducted from principal prior to accruing interest, or
after accruing interest? If it's the former, the correct amount is
$34,843.40 per year. Most such amortization tables, however, would
accrue interest on principal before money is withdrawn. However,
there's an important distinction there that the OP should be aware of.

Dave

On Feb 19, 3:18*pm, "Niek Otten" wrote:
Hi Steve,

=PMT(5%,15,500000,-250000)

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve" wrote in ...

|I am looking for the right formula to use for the following
| scenario. * If I had $500,000 in an account at 5% interest per year
| over a 15 year period and wanted the ending balance to be $250,000,
| how much can I draw out per month and meet the criteria of still
| having $250,000 at the end. *I used PPMT but the results don't look
| correct to me.
|
| Thanks, Steve

You need the PMT (payment) function. The PPMT computes how much you are
paying down the principle not the entire loan ( of annuity)

PMT(rate,nper,pv,fv,type)
In your case rate =5%/12 since you want to have monthly withdrawals
Similarly, nper is 15*12
Now for the 'trick': getting the right signs for the monies involved!
You DEPOSITED $500,000 so for PV we use a negative value (money flows away
from you)
For future value we use a positive value since the balance of $250,000 is
coming to you (not to the bank!)
I left TYPE out (payments at end of month)
I get $3,018.65 which agrees with an amortization table I made for your
problem

B1 has =5000000*(1+5%/12)-3018.65
=(starting about) + (monthy interest) - monthly withdrawal
B2 has =B1*(1+5%/12)-3018.65
This is draged down to row 180 which displays 250,000.20
Twenty cents for some CPA to worry about!

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Steve" wrote in message
...
I am looking for the right formula to use for the following
scenario. If I had $500,000 in an account at 5% interest per year
over a 15 year period and wanted the ending balance to be $250,000,
how much can I draw out per month and meet the criteria of still
having $250,000 at the end. I used PPMT but the results don't look
correct to me.

Thanks, Steve

You can actually draw about $3018 per month assuming interest is computed on
a monthly basis as opposed to an annual basis.

=PMT(5%/12,15*12,-500000,250000)

Tyro

"Steve" wrote in message
...
I am looking for the right formula to use for the following
scenario. If I had $500,000 in an account at 5% interest per year
over a 15 year period and wanted the ending balance to be $250,000,
how much can I draw out per month and meet the criteria of still
having $250,000 at the end. I used PPMT but the results don't look
correct to me.

Thanks, Steve