Financial Question

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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

|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


|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
Click to expand...

Thanks Niek, I didn't know to list the future value as negative for
whatever reason that requires ?
 
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
 
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
 
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/excel/HA011117451033.aspx


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


Hi Steve,

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

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|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
Click to expand...

Thanks Niek, I didn't know to list the future value as negative for
whatever reason that requires ?
 
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
 
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

Hi Steve,

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

Will give you a yearly amount of

36,585.57

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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
Click to expand...

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







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

- Show quoted text -
Click to expand...

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.
 
Back
Top