XIRR Function Using Months Instead of Years

[TKrepitch]

I am trying to modify a model that uses the XIRR function. Instead of
breaking revenue and costs down by year, I want to do it by month.

The result of the XIRR function when I do that, though, is overstated
quite a bit. Any ideas what I am doing wrong? Thanks!

 

[Bernie Deitrick]

Do you have a list of dates for you cash flow? And are they dates?

From help:

"Dates should be entered by using the DATE function, or as results of other formulas or functions.
For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are
entered as text."

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

I am trying to modify a model that uses the XIRR function. Instead of
breaking revenue and costs down by year, I want to do it by month.

The result of the XIRR function when I do that, though, is overstated
quite a bit. Any ideas what I am doing wrong? Thanks!

XIRR will only return a yearly rate of return. So you'll have to convert that
to a monthly rate.

Maybe something like

=(1+your_xirr_formula_result)^(1/12)-1


--ron

 

[TKrepitch]

They are in there as dates. Hmm....

Do you have a list of dates for you cash flow? And are they dates?

From help:

"Dates should be entered by using the DATE function, or as results of other formulas or functions.
For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are
entered as text."

HTH,
Bernie
MS Excel MVP

 

[TKrepitch]

It's ok if the IRR is an annual number

I have $697,000 in revenue and $226,931 in cost spread over five years
and the XIRR formula gives me 46%.

I've spread $662,500 in revenue and $226,931 in cost over sixty months
and the XIRR formula gives me 84%.

I was hoping I'd get a similar IRR, but it's not working and I'm
stumped. Ack!

 

[Niek Otten]

Please give your values, your formula, the result and the expected result.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| They are in there as dates. Hmm....
|
| Bernie Deitrick wrote:
| > Do you have a list of dates for you cash flow? And are they dates?
| >
| > From help:
| >
| > "Dates should be entered by using the DATE function, or as results of other formulas or functions.
| > For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are
| > entered as text."
| >
| > HTH,
| > Bernie
| > MS Excel MVP
| >
| >
| > >I am trying to modify a model that uses the XIRR function. Instead of
| > > breaking revenue and costs down by year, I want to do it by month.
| > >
| > > The result of the XIRR function when I do that, though, is overstated
| > > quite a bit. Any ideas what I am doing wrong? Thanks!
| > >
|

 

[Bernie Deitrick]

Timing is everything. If you invest $1,000 today, and tomorrow get back 1,001, your rate of return
is 44%, on an annualized basis. If you get your 1001 next week, it is ~5%, next month is ~1%, next
year it's ~.1%. That's why dates are CRITICAL.

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

It's ok if the IRR is an annual number

I have $697,000 in revenue and $226,931 in cost spread over five years
and the XIRR formula gives me 46%.

I've spread $662,500 in revenue and $226,931 in cost over sixty months
and the XIRR formula gives me 84%.

I was hoping I'd get a similar IRR, but it's not working and I'm
stumped. Ack!

The values XIRR returns, and the "correct" return, depend critically on when
the expenses and income occur.


--ron

 

[TKrepitch]

Hi, Niek. I think I have figured out the issue I had (I had mistakenly
moved the revenue up too far in time - so everyone who said the timing
was the issue was correct - I just blew it). But I do have another
question.

The old model that I am revising was built with 1/1/2000 as the
starting date. If I change that to 1/1/2006, the IRR changes slightly.
Why is that? I was thinking that the model is just calculating the
time span and that the actual dates wouldn't have an impact.

Thanks!

 

[Bernie Deitrick]

Does your time period extend over a Feb 29th of leap year?

HTH,
Bernie
MS Excel MVP