which financial function should I use?

[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?

 

[ryguy7272]

IRR:
http://www.tutorialized.com/view/tutorial/Calculate-IRR-in-Excel/23330
http://www.techonthenet.com/excel/formulas/irr.php

IPMT:
http://www.bettersolutions.com/excel/EDH113/QP446920522.htm
http://www.techonthenet.com/excel/formulas/ipmt.php

Rate:
http://www.ehow.com/how_2103566_calculate-interest-rate-using-excel.html
http://www.techonthenet.com/excel/formulas/rate.php


Regards,
Ryan---

 

[Fred Smith]

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.

 

[Ken W]

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.

 

[Fred Smith]

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/

 

[joeu2004]

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.

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.

 

[Ken W]

Thanks - didn't have PV negative!

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.

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.