IRR Calculation

Hello there

In my understanding and context (in financial services), when we refer to an interest rate, we always use it in the references to 1 year. So a rate of 3% would be 3% per annum.

IRR essentially calculates a rate that, when used to discount all the cashflows (positive and negative) yields the net present value equal zero. It would be expressed generally as an annual rate, which then you can convert to a monthly rate or 6 monthly rate. Usually rates are kept as annual rates though, also it would be misleading to compare a 4 month investment project at 20% return for the 4 months to a 6 month project which yields 24% return for the 6 months. Which one is better ?

Regards

Originally Posted by andrewc

Hi,

I have used the IRR function with six monthly cash flows to calculate an IRR of 3%. Is this 3% a monthly return or for the whole 6 month period?

Thanks

IRR, MIRR, and NPV functions assume periodic cash flows where the period is not defined and is a generic period with each period separated by an interval of 1

If you have monthly cash flows then Excel will give you a monthly IRR as your period was a month

From here you can find either an annual or an annualized IRR

Annual IRR
= IRR(cash flows) * 12

Annual IRR
= ( 1 + IRR(cash flows) )^12 - 1

But this is one of the many limitations when you are using Excel financial functions, to address the shortcomings of Excel's own functions, 3rd party software such as tadXL may be very useful tool

tadXL is a library of financial functions currently consisting of 95 functions that not only extend the existing financial functions of Excel but offer many more new financial functions such as Payback period, Discounted payback period, NFV, B/C ratio, profitability index, equivalent annual annuity and equivalent annual cost.

But that is just a start, Excel has only 3 functions for investment analysis where as tadXL has 60 such functions of which many people have never even heard of and I hear that those at Microsoft are working on copying all these functions off tadXL for their own gains. I guess the bottom line matters to the greedy companies more than survival of little guy who makes $100 a week in sales

tadIRR is tadXL version of Excel IRR function and it permits you to specify what the period represents such as in your case you would use a value of 1/12 for the variable called PERIOD in tadIRR function. You can also specify the compounding frequency of interest if it was monthly you would then use a value of 1/12 for a variable called COMPOUNDING and if it were to be annual then a value of 1 is used. Other values that you can use for compounding of interest may be just about anything you can think of from a value of 0 for continuous or infinite compounding to a value of 2 for biennial compounding. Same holds true for values for PERIOD

But then if you are working on cost benefit analysis for Federal Government you may find it useful that tadXL functions allow for mid year discounting of cash flows rather than the Full year discounting and other values for cash flow concentration

tadXL is not FREE but it is worth each cent you pay for it and those who use it never complain and are happy troopers

Originally Posted by andrewc

Hi,

I have used the IRR function with six monthly cash flows to calculate an IRR of 3%. Is this 3% a monthly return or for the whole 6 month period?

Thanks

Wait your wording seem a bit confusing

My last reply assumed there were 6 six cash flows for each of the month

Now that I reread your original post, it comes across as you have semi-annual cash flows thus the IRR given by Excel is semi-annual ( 6 monthly )

In which case the annual rate and annualized rates are calculated as follows

Annual IRR
= IRR(cash flows) * 2

Annual IRR
= ( 1 + IRR(cash flows) )^2 - 1

Whereas had you used tadIRR then this is how you would have called the function for semi-annual cash flows

=tadIRR( { -1000, 500, 400, 300, 200, 100, -75, 50, 125, 150, -150, 250 } , 1/2, 1/2, 1 )

The Excel IRR function listed above accepts these values

=tadIRR( cash_flows, compounding, period, distribution, iterations, precision )