XIRR by default is an annualized return. I thought I could de-annualize for period 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%
| +82 | LP coming into deal at higher land basis. How to model returns to GP? | 19 | 2h | |
| +76 | Increasing RE Industry's Cash Compensation, Collectively | 43 | 1d | |
| +73 | Major Decisions | 13 | 2d | |
| +33 | Niche down to an operator or stay an allocator | 11 | 4s | |
| +33 | Being asked to stay behind and train my replacement | 8 | 14m | |
| +24 | Breaking into CRE late 20s | 7 | 4d | |
| +23 | Thoughts on joining an early-stage REPE fund | 7 | 1d | |
| +22 | Self Storage ECRI | 9 | 3d | |
| +20 | How to get good at RE Modelling? | 18 | 2h | |
| +20 | Public Homebuilders | 6 | 2s |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
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 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
Adipisci corrupti iste eveniet sit. Excepturi amet ullam numquam autem a. Pariatur perspiciatis voluptates ipsum vitae.
Consequuntur minus dolorum aut numquam rem nostrum est ut. Nesciunt occaecati quia et rerum quo reiciendis vero et.
Officia impedit ea inventore natus libero veritatis praesentium. Sed dolore ut itaque voluptatem nesciunt aspernatur.
Eos sed mollitia et enim. Omnis placeat animi repellendus tempore est. Sapiente delectus aut qui veritatis omnis facilis. Quas eaque dolorem ipsam recusandae.
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...