which financial function should I use?

  • Thread starter Thread starter Ken W
  • Start date Start date
K

Ken W

I'm not sure which financial function to use. I'm trying to determine the
rate of return of a known investment, with a known time period and known
ending value. What do I use to solve for the rate of return over the life of
this investment?
 
If you have a starting value (PV) and an ending value (FV), then you want
the Rate function.

If, as is more normal in the real world, you have a series of cash flows and
and ending value, you want XIRR.

Regards,
Fred.
 
I only have an investment and future value and number of periods - no annual
payments are made (it's an equity investment in a company). It returns
"#NUM". I changed the guess around alot, but it hasn't helped.
 
What returned #NUM??????? Excel we're good at, mindreading isn't our
specialty. We can't help you until you show us the formula you used.

Regards,
Fred/
 
I only have an investment and future value and number
of periods - no annual payments are made [...].
It returns "#NUM". I changed the guess around alot,
but it hasn't helped.
Click to expand...


If you have only the two data points, you should use RATE. Remember
to enter the initial investment and future value with opposite signs.
For example, =RATE(5,0,-1000,2000).

If the #NUM error persists, yes, it could be because you need to
provide a guess. Unfortunately, it is often difficult to choose a
good guess.

Also remember that RATE returns a result in terms of the number of
periods. In my example above, if 5 is years, RATE is an annual rate;
but if 5 is months, RATE is a monthly rate. You can convert a monthly
rate to an annual rate in two ways:

=12*RATE(5,0,-1000,2000)

=(1 + RATE(5,0,-1000,2000))^12 - 1

It is debatable which formula to use; it depends on your intent. The
latter compounds the rate.
 
Thanks - didn't have PV negative!

joeu2004 said:
I only have an investment and future value and number
of periods - no annual payments are made [...].
It returns "#NUM". I changed the guess around alot,
but it hasn't helped.
Click to expand...


If you have only the two data points, you should use RATE. Remember
to enter the initial investment and future value with opposite signs.
For example, =RATE(5,0,-1000,2000).

If the #NUM error persists, yes, it could be because you need to
provide a guess. Unfortunately, it is often difficult to choose a
good guess.

Also remember that RATE returns a result in terms of the number of
periods. In my example above, if 5 is years, RATE is an annual rate;
but if 5 is months, RATE is a monthly rate. You can convert a monthly
rate to an annual rate in two ways:

=12*RATE(5,0,-1000,2000)

=(1 + RATE(5,0,-1000,2000))^12 - 1

It is debatable which formula to use; it depends on your intent. The
latter compounds the rate.
Click to expand...
 
Back
Top