I'm trying to figure out how to create a dynamic IRR syntax in Excel. If I change my disposition date, my cash flows and all other projections adjust automatically. I can't seem to come up with a proper way to have my IRR calculate only certain years cash flows as I change my sale date. I'm sure it's an =IF statement of some type...
Any help is appreciated.
you can probably do an if(current date = sales date,sum(Forward 12 month rows)/cap rate)...easiest way imo
so you are just summing the forward 12 months in each cell
In REFM, he's pulling in monthly cash flows, then consolidating them for annual IRR. When you F2 the syntax below, whether disposition is yr1 or yr 10, all his cash flows are still in the range, even if they're $0 because the building is sold. I just can't seem to crack it.
This is my struggle with REFM. The data is there but he only covers about 10% of his model with instruction. You're left to reverse engineer.
=(IRR(O109:EQ109,0.2/12)+1)^12-1
I'm pretty new at modeling. I have created a decent but basic MF model. Annual CF's only at this point.
Are you wanting the IRR formula to adjust to a different cell range depending on the disposition date? Shouldn't matter as long as there aren't any CFs in the dates after the sale if you move it up, right?
Yeah, just do an IF statement making all values in the "Cash Flow to Equity" row equal 0 after your disposition date. Should have the same effect.
At the moment this is working.
I already had an IF statement that would produce $0 CF's after disposition date. With those post sale date years at $0 my IRR is calculating properly.
have you tried =sumif(year0:year5:cfyr0:cfyr5)? adds up cash flows based on year headers
What JimboUSC said. Assuming your hold period input is a number of years (i.e. 10), just multiply your main equation by a binary:
Items that only calculate ON the sale period: ex: terminal value, sales cost, outstanding principal =if(current pd=hold pd,1,0) * main equation
Items that only calculate BEFORE and ON the sale period: ex: TI/LC/tenant buyouts/cap reserve/asset mgmt fee/whatever else you want to include here =if(current pd>hold pd,0,1)*main equation
Just make sure you have a "period" line item at the top of your pro forma that tracks how much time has elapsed, and make sure the period's unit of measurements in the above equations are consistent. That's all it is, unless I'm completely missing your point.
If my sale is year 4 and years 5-6 in that cell range have $0 in them, won't the IRR calc take into account those periods?
Edit - just took my cell range all the way to year 10 even though sale was year 4 and years 5-6 showed $0. Still got the same IRR even though sale date was year 4. Maybe I'm making this hard on myself.
With that being said, I'll review these last few posts later. Gotta jam for a bit. Thanks for the feedback guys.
No no no. I don't know what tutorial you're looking at but simply do this to improve your underwriting:
Assumptions page should have timing table which includes dates and periods daily, monthly annually whatever is required. (LOI date, due diligence period, contract date, due diligence period, closing date, break ground date, periods, completion, investment period, sale date, etc. etc. etc.)
If i can figure out how to upload a screenshot I will.
I personally use xirr with the offset function in excel. The offset function paired with count will allow the xirr to adjust to the dates you enter.
Dude just use indirect(address()), where your start and ending cash flows will be captured by the address() function and embed that within XIRR, always use XIRR, for the monthly cash flows
this is the way.
Edit: lmao didn't realize this is 6 years old.
Repellendus natus velit perspiciatis consequatur voluptatibus dolor sunt. Ratione qui iure dolores deserunt sit in quo. Totam deserunt laboriosam esse rem. Commodi sint veritatis dolore rerum occaecati.
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...