*~*~ Can someone create an excel calculation for the following ....

[BobHat]

I would like a function that would do the following calculation

How much lump sum of money is required to
provide $1000 withdraw per month for 25 years
assuming the investment made 6.5% annually.

Thank you

 

[Don Guillett]

Look in HELP for financial functions.

 

[Harlan Grove]

Look in HELP for financial functions.

....

It's one thing to tell an OP to read online help for functions or features
of which they already know the name, but hunting through online help when
you don't know the name or a key term is, um, inefficient. Especially so
since some Excel versions (XL2K, which I'm using at the moment) doesn't have
an entry in the index for 'financial functions'. Not so helpful.

For the OP, see the PV function.

 

[Myrna Larson]

Hi, Harlan:

Agreed, the index doesn't seem to be too helpful, but Search/Find or the
Answer Wizard give hits for "financial functions" which lead you to a list
with a mini-description of what each one does.

It's probably the same list and description that you see if you click on the
"Insert function" button and select Financial as the category.

Myrna Larson

 

[Myrna Larson]

PS: In Excel 2002, if you go to the Contents tab, you find Function Reference,
and under that, Financial Functions. Isn't it the same in XL2000?

 

[Bernd Plumhoff]

=PV(6.5%/12,25,-1000,0,1)

Result is 23,448.58. Keep in mind I assumed payments at beginning of each
month (see help on PV).

Hope this helps,
Bernd

 

[Harlan Grove]

PS: In Excel 2002, if you go to the Contents tab, you find Function
Reference, and under that, Financial Functions. Isn't it the same
in XL2000?

....

In XL2K, I go to the Contents tab and I find no top-level entry for Function
Reference. I do find a top-level entry for 'Creating Formula and Auditing
Workbooks'. Under that is an entry for the Worksheet Function Reference. Not
self-evident this path of discovery.

Granted if I were to type in 'financial functions' in the online help answer
wizard, I'd get a list that includes the PV worksheet function, but that
this shows up in the answer wizard but not the index is unhelpful.

Why there isn't an entry in the index for 'Financial functions' when there's
a topic page with that precise title goes beyond stupid. There should be an
index entry for *EVERY* help page using that help page's title. Given the
bloat already present in Office, it's not like Microsoft can claim with
straight face that they were trying to conserve storage.

 

[BobHat]

Thank you Bernd

I do not think that this is the result I am looking for. I believe
that I may have not expressed the condition correctly.

I recently got divorced and need to provide a life insurance policy
with my ex wife as beneficiary. I need to provide a life insurance
policy that will provide $1,000 per month for 25 years. I believe
that the amount of the insurance policy would be somewhere in the
$150,000 range. Now she doesn't actually get $1,000 from my
retirement benefits due to taxes, so I would like a formula that will
allow me to play with the numbers.
I hope this helps.

TIA for your help.

 

[Myrna Larson]

Looks like the problem with Bernd's formula is the number of periods. For
monthly payments of $1000, it should be 25*12, not 25. After making that
change, I get $148,904.92.

As far as "playing with the numbers", you can substitute cell references for
any of the arguments, so if you put the APR in A1, the number of years in A1,
the monthly payment in A3, the formula becomes

=PV(A1/12,A2*12,-A3,0,1)

 

[Bob Hat]

Thank you very much for all your help.