Proforma Analysis Bug/Issue and IRR Formula Question

Hello, I'm having issues with a spreadsheet I've been working on. This is a real estate proforma analysis spreadsheet that I downloaded from a site and then tailored to my use. That said, I have not messed with any of the major formulas. I have recently begun having some issues, and I hope someone can take a look at the spreadsheet and help. I also want to be able to modify the IRR calculations. Here are the items I'm hoping to solve:

1. On the Underwriting tab, cell I59, this is the equity and debt table, based off the total sources and uses above. I59 currently has a hard input of $13,500,000. However, I would like to be able to calculate the equity as 35% of the total uses in cell I56, but when I put this formula in I59 (=35%*I56), it never stops calculating, jumps all over the place and affects the entire spreadsheet. Please note: the Capitalized Construction Interest in cell I51 is directly calculated by the Construction Debt in cell I60. Also note: I have iterative calculations on, which I thought was the issue originally. Perhaps this worksheet has some macros calculations that are causing issues?

2. When I try to save this worksheet, it loads for a while as if it is trying to calculate something. Only when I press the "Esc" key it will pop up "calculation is incomplete. Recalculate before saving?" If I click no, it will save right away. How to avoid this?

3. This question is a bit more in depth. On the Underwriting tab, starting in row 220-221, there are some project-level IRR calculations, and starting in row 234, there are some waterfall IRR calculations. The problem is that this spreadsheet seems to assume that all equity comes in at the same time. However, the LP in row 237 will bring in capital at Month 13, where as the GP in row 236 will bring in capital at Month 0. There is a relatively straight-forward way to adjust the timing of the costs spent in the Sources and Uses table above starting in row 23, but I don't know of a good way to adjust the timing of which money comes in when. Furthermore, I want to be able to calculate IRR based on the money coming in at different times, if possible.

Thank you for taking a look at this! It won't let me upload the file here (perhaps it is too big?) so I've provided the dropbox link here. Thank you!

https://www.dropbox.com/s/izsa0x8avf...7.20.xlsm?dl=0

We'd prefer attachments to be attached here, please, rather than on file-sharing sites.

There are instructions at the top of the page explaining how to attach your sample workbook. Follow the instructions: it's a SAMPLE file we need to see, not the whole thing, so a sample file, properly prepared as instructed, will not be too big.

when I put this formula in I59 (=35%*I56), it never stops calculating, jumps all over the place and affects the entire spreadsheet.

That sounds like this formula creates a circular reference and you have iteration enabled. Without seeing the entire spreadsheet, it is difficult to make a concrete recommendation, but you will need to think through the calculation more carefully to either avoid the circular reference or provide a more robust sequence of calculations that will converge rather than diverge/oscillate.

My apologies for being late with a response (especially the non-response this is)

with such an in depth analysis, it was important to provide the whole spreadsheet.

I think there is a careful balance between too much sample sheet and not enough. In this case, I quickly became overwhelmed by the size of your sample sheet (even the pared down sample attached to post #4). Answering your questions will need some understanding of what the spreadsheet is doing, but I was unable to reverse engineer everything the sheet was doing. It might help if you explain what the sheet is doing. It also might help if you can pare it down further to just the parts that are involved in your questions.

circular references with iterations have never caused this issue for me before,

If you have never before had a circular/iterative algorithm diverge or oscillate or converge on the wrong solution, consider yourself lucky. These kinds of algorithms all have cases where they fail for one reason or another, and a big part of successfully implementing them is being able to understand when they will succeed and when they will fail and what you can do as a programmer to improve your chances of success. Figuring out how best to change I59 is going to involve understanding how I59 currently fits into the calculation scheme and how the proposed change will change its place in the calculation scheme. A lot of that comes from the reverse engineering that I have not done.

they are necessary for these types of analyses.

I only have your word that this is the case, but I will note that I have seen many users claim a circular calculation is necessary when some study of the algebra of the problem would show that it is not necessary. Again, more of the reverse engineering that hasn't been done.

I need to be able to utilize the total project costs to find out the loan amount, and the construction loan interest is calculated by utilizing the exact loan amount, which is calculated as a percentage of the total project costs.

This sounds like:
totalcosts=rate*loanamount+othercosts
loanamount=rate*totalcosts
which may be more of a "system of equations (two equations with two unkowns)". While iterative algorithms can be used to solve these, there are non-iterative ways to solve many system of equations type of problems. It would require some reverse engineering to discover if this is the case here. Even though you have successfully used iterative algorithms for this in the past, you may find there is value in discovering non-iterative algorithms to perform the same task.


I'm not sure I understand your XIRR() question(s). From my limited understanding of the XIRR() function, as long as your cash flows are present in the range as they correspond to dates in the date range, the XIRR() function should correctly consider timing of each cash flow in computing the XIRR(). If it's not correctly considering the dates, can you point us to an example in your sheet where it is incorrectly considering the timing of the cash flow(s)?