IRR on monthly intervals

  • Thread starter Thread starter duke
  • Start date Start date
D

duke

I am running a $30MM negative month 1, a $1MM positive
for 46 mo. and then a $60MM positive in month 48. How do
I calculate IRR over the 48 months?
 
Duke

Since your time periods are all the same and you cash flows are not all
identical, the easiest way is to lay out the data in 48 rows and apply the
IRR formula to the range. Note, that it will assume that period 1 is time
=0, which for all practical purposes, it is.

To annualize the monthly rate use (1+IRR(range))^12

PC
 
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.
 
Back
Top