Using IRR to find yield over time of cash contributed to an IRA, using monthly periods (with many with blank/zero amts since no contribution most months) over several years. Am getting a percentage that appears to be annual one without my havingt made any entry that would tell Excel that the period is monthly. Is the result really an annual percentage?

>-----Original Message-----
>Using IRR to find yield over time of cash contributed to
an IRA, using monthly periods (with many with blank/zero
amts since no contribution most months) over several
years. Am getting a percentage that appears to be annual
one without my havingt made any entry that would tell
Excel that the period is monthly. Is the result really an
annual percentage?
>.
I think IRR is normally calculated over yearly periods
rather than months. However, if you do not include amounts
in some months then this affects the percentage by raising
the IRR.
It is best (I think) to just include totals for each year
Excel will assume that each entry is a year.
Regards
Peter

When using IRR (and most other financial functions), Excel does not know (or
care) what the length of the period is. The result you get back is *always*
the rate per period (eg 4.2% per month, or 19.8% per year).

Your problem is that IRR assumes regular cash flows. You have irregular cash
flows. Use the XIRR function in this case.

Hi JCN!

I suspect that your problems lies in:

"with many with blank/zero amounts"

Don't insert blanks in cash flows that you use an IRR or NPV on!!!

If you have blanks instead of zeros, these functions will treat the
next zero / positive / negative flow as occurring immediately after
the last zero / positive / negative flow.

Consider:
-100000
blank
110000

=IRR(A1:A3)
Returns: 10%

-100000
0
110000

=IRR(B1:B3)
Returns: 4.88088481701527%

Excel's Help (under IRR) says that irregular flows are ok and even that blanks will be read as zero; but to be safe I will enter zeros where I now have blanks. That still leaves the matter of being able (according to Help) to use monthly periods without staing how that effects the resulting Result (though perhaps I should try the substitution of zeros for blanks before commenting on the Result I am getting). Thx

Changed the blanks to zeros instead. The result (even after trying guesses) is as follows: Div/0! which I assume it some type of error message; but nothing comes up re it in a Help search for that. Can anyone help?

To be more precise, the result is: #DIV/0! and the Help section on that code did not yield a fix. Even changing all of the blank entries to zero and further even changing all of the zero entries to $0.01 did not help. XIRR instead of IRR yields a Name error. Can anyone help? Is it possible that everything was ok originally (i.e. with the blank instead of the zero or 0.01 entries) was fine? it yielded 7.58 which would be a good ANNUAL rate guess (though, as indicated in my original message on this, the data entry cells are monthly not annual -- thus leading me to question how Excel could have automatically given me the desired annual rate without knowing from anything that I entered that the data entry cells were monthly, not annual. Help!!

Hi JCN!

My comments regarding the interpretation of blanks stand! Use the
example I gave to prove to yourself that this is correct.

Excel Help (2003) says:
"Values is an array or a reference to cells that contain numbers
for which you want to calculate the internal rate of return"

Note "reference to cells that contain numbers"

Also:
"If an array or reference argument contains text, logical values, or
empty cells, those values are ignored."

It's not worded as clearly as it should be, but by "ignoring" it means
not included in any way in the cash flow. "Ignore" certainly does not
mean "treated as zero".

I've looked at Excel 97, 2000, 2002 and 2003 and can find no reference
in Help that says "blanks will be read as zero"

To use XIRR, you must have installed and selected Analysis ToolPak.

Use:
Tools > Addins
Select Analysis ToolPak
OK

If you've never used it before you may need to use the Office
Installation CDROM.