Correct formula for this

[Andrew Chalk]

I wonder if someone more familiar with Excel could tell me the name of the
financial function to solve the following problem:

I invest $1,000 and 20 year later have $56,000. What was the annual rate of
return?

Many thanks!

 

[Mike]

I wonder if someone more familiar with Excel could tell me the name of the
financial function to solve the following problem:

I invest $1,000 and 20 year later have $56,000. What was the annual rate of
return?

Many thanks!

The formula is
=RATE(20,0,-1000,56000,0)

and the answer is 22%.

Mike Anas
http://mikeanas.googlepages.com/


From Excel's help library:
Returns the interest rate per period of an annuity. RATE is calculated
by iteration and can have zero or more solutions. If the successive
results of RATE do not converge to within 0.0000001 after 20
iterations, RATE returns the #NUM! error value.

Syntax

RATE(nper,pmt,pv,fv,type,guess)

For a complete description of the arguments nper, pmt, pv, fv, and
type, see PV.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period and cannot change over the life
of the annuity. Typically, pmt includes principal and interest but no
other fees or taxes. If pmt is omitted, you must include the fv
argument.

Pv is the present value -- the total amount that a series of future
payments is worth now.

Fv is the future value, or a cash balance you want to attain after
the last payment is made. If fv is omitted, it is assumed to be 0 (the
future value of a loan, for example, is 0).

Type is the number 0 or 1 and indicates when payments are due.

Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

Guess is your guess for what the rate will be.
If you omit guess, it is assumed to be 10 percent.
If RATE does not converge, try different values for guess. RATE
usually converges if guess is between 0 and 1.

 

[TRYING]

In the example, it was not clear if Andrew made just one payment of $1,000
and after 20 years had $56,000 OR he made $1,000 payment once each year for
20 years (or is it 19?) and in 20 years had $56,000.

My own guess is that it was the former, just a one-time payment of $1,000.
If so, can the financial function be modified to apply to this? Or is there a
different function to find the rate?

 

[Andrew Chalk]

In my example. the initial investment ($1,000) was made only once, so RATE()
does not appear to be suitable.

Any suggestions?

Thanks,

Andrew

 

[Mike Anas]

In my example. the initial investment ($1,000) was made only once, so RATE()
does not appear to be suitable.

Any suggestions?

Thanks,

Andrew








- Show quoted text -

The formula I posted is the correct one. It assumes a one-time
investment of 1000. Look closely at the function and its requirements
and you will see why.

=RATE(20,0,-1000,56000,0)

In plain English, the above formula is asking, what is the interest
rate per period if I invest 1,000 dollars today, make no periodic
additional investments, and end up with 56,000 dollars after 20
periods?

 

[Andrew Chalk]

The formula I posted is the correct one. It assumes a one-time
investment of 1000. Look closely at the function and its requirements
and you will see why.

=RATE(20,0,-1000,56000,0)

In plain English, the above formula is asking, what is the interest
rate per period if I invest 1,000 dollars today, make no periodic
additional investments, and end up with 56,000 dollars after 20
periods?

Mike: Your verbal description is exactky what I want. Howver, my remarks (to
which you replied) wewre based on the following from the Excel help file for
RATE();

Pmt is the payment made each period and cannot change over the life of the
annuity. Typically, pmt includes principal and interest but no other fees or
taxes. If pmt is omitted, you must include the fv argument

It does say 'each' period. (Text from Office 2003 Help).

That is my only remaining confusion.

 

[Andrew Chalk]

Scrap my last response. I was referring to the wrong parameter!

Thanks for your explanation.

- A

 

[David Biddulph]

Same answer you get from =(A2/A1)^(1/A3)-1
if your 1000, 56000, and 20 parameters are in A1, A2, A3 respectively.

 

[Stan Brown]

Wed, 12 Dec 2007 17:29:14 -0600 from Andrew Chalk

I wonder if someone more familiar with Excel could tell me the name of the
financial function to solve the following problem:

I invest $1,000 and 20 year later have $56,000. What was the annual rate of
return?

Insert | Function, and in the serach window type
rate of return