Hi Duke!
A1
-30000
A2:A47
1000
A48
60000
=IRR(A1:A48,-0.9)
Returns: 4.05006818075943%
Which is a fairly low rate of return until you realize that it comes
from a monthly cash flow.
=IRR(A1:A48,-0.9)*12
Returns: 48.6008181691132%
Which is the APR12 (Annual Nominal compounded monthly) and a little
bit more attractive to the investor.
OR:
=(1+IRR(A1:A48,-0.9))^12-1
Returns: 61.0306068273266%
Which is the annual effective return and even more attractive to the
investor and which illustrates why I view annual effective returns as
a better guide than Nominal rates.
In IRR functions we tend to use a guess rate of -0.9 as it rarely
produces the #NUM! error message that you get if the (inefficient)
Excel IRR algorithm doesn't hit an accurate figure after 20 tries. The
guess rate argument has a default of 10% which usually works but I've
found that #NUM! is quite likely to be returned for very long cash
flows.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Saturday 19th July: Andorra (Canillo), France
(Edgar Degas Birthday), Laos (Independence Day), Malaysia (Birthday of
Yand di-Pertuan Besar of Mg Sembilan), Myanmar (Martyrs' Day),
Nicaragua (Revolution / Sandinista Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.