XIRR by default is an annualized return. I thought I could de-annualize for period 365 days by using the formula ((1+XIRR)^(Hold Period days /365))-1. The formula seems to hold if just one large distribution on last day of hold period, but just adding $1 to distribution a month later, pushing out hold period, is increasing returns significantly, whereas I was expecting IRR to decrease with the extra month. Anybody know the best way to use XIRR correctly for this?
1/1/18: -$100 3/31/18: $200 ((1+XIRR)^(120/365))-1 = ~101%
1/1/18 -$100 3/31/18: $200 4/30/18 $1 ((1+XIRR)^(151/365))-1 = ~142%
Thats how you'd do it using the =IRR function. The IRR is the annualized return. The =XIRR function is intended for exactly what you're trying to accomplish. XIRR combines Cash Flows with uneven dates. It links Cash Flows with hard dates. Just add dates, whether real or hypothetical and you're there.... Any 2-minute YouTube video would show you this. XIRR=(CashFlows:CashFlows,Dates:Dates).
Nothing to it
Thanks. I'm taking verbatim out of NCREIF reporting manual:
Excel XIRR function - User inputs multiple cash flows along with the date that each cash flow occurs. - The periodicity of the cash flows is daily - Annualizes result. - No user adjustment needed if the holding period is greater than one year. If the holding period is less than a full year than the result must be de-annualized using the formula: (1 + Rate%) ^ (#days/365) — 1
If I were to run simple XIRR on this cash flow, I'd get the annualized return of 1,616%, but I want to be calculating the de-annulized return of 100% 1/1/18: -$100 3/31/18: $200
Second this - no need to do anything to XIRR to account for date ranges - that is already implicit in the formula. The only tricky thing about XIRR is that you MUST have a cash outflow in Period 1, or it won't work.
Your rate of return is 1,616%. The IRR function by default is annualized. The XIRR function was created for CFs with shorter durations or uneven timing of CFs. I'm unsure why NCREIF is suggesting to de-annualize your CFs? It's very hard to purchase a property for $1M then flip it in 3 months for $2M.
Appreciate the feedback. I'm checking with some folks more involved with fund reporting , will let you know what I learn.
The scenario was just an example for simple math demonstration though.
Intuitively it makes sense to me de-annualize when reporting returns. If you give me $100 in equity and 3 months later I told you that you made a 1,600% return, you might think I'm going to give you $1,500 in profit and be confused when I only give you $100. If I told you that you made 100% return, you'd probably only think you made $100 in profit, which is what I'm trying to convey.
Highlight the equity multiple.
XIRR for less than 365 Days Formula (Originally Posted: 10/08/2018)
XIRR by default is an annualized return. I thought I could de-annualize for period 365 days by using the formula ((1+XIRR)^(Hold Period days /365))-1. The formula seems to hold if just one large distribution on last day of hold period, but just adding $1 to distribution a month later, pushing out hold period, is increasing returns significantly, whereas I was expecting IRR to decrease with the extra month. Anybody know the best way to use XIRR correctly for this?
1/1/18: -$100 3/31/18: $200 ((1+XIRR)^(120/365))-1 = ~101%
1/1/18 -$100 3/31/18: $200 4/30/18 $1 ((1+XIRR)^(151/365))-1 = ~142%
Hi RE Dude, check out these resources:
No promises, but sometimes if we mention a user, they will share their wisdom: lanceng Marcitwithac Bogdan_G
You're welcome.
Doing some personal research on this topic. I thought this was pretty useful.
https://www.financialwisdomforum.org/gummy-stuff/xirr.htm
Distinctio tempora at animi cumque rem. Sit voluptatem earum placeat ut nobis consequatur pariatur ratione.
Velit odit veniam consectetur unde dolore enim sed. Veniam perspiciatis eveniet a necessitatibus impedit expedita. Corporis et laborum ut similique necessitatibus.
Nihil et autem sit veniam et nostrum odit. Nesciunt odit aut laboriosam tempora. Eaque veritatis dicta et et error.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...