Iteration for Valuation Purposes

[irie267]

[FONT=&quot]I was hoping that someone here could help explain to me what is exactly going on in the attached formula. It is a circular exercise where the IRR is based on the entire cash flow stream, and the capitalization rate is...the same IRR. I am really just asking for an explanation of what the IRR (22.03%) and terminal value ($9,080) "mean" using this method and how excel knows which IRR to "use" since there is no goal defined, etc. I realize this is more of a question regarding valuation than excel, per se, but any insight would be appreciated.

[/FONT]https://imgur.com/a/w9eWsZk

 

[joeu2004]

I'm not familiar with capitalization rate beyond what I just read online. And yes, this is more of a finance question than an Excel question. It's not clear how much you know. So forgive me if I'm preaching to the choir.

First, I would not use a circular formula -- IRR(D11:M11) in C11, where M11 is =2000/C11 -- and enable Iterative calculation. It is highly unstable.

For example, it did not work for me in Excel 2010 (returned #VALUE ) unless first I calculate IRR(D11:L11), then IRR(D11:M11). Moreover, the formula in C11 is recalculated each time I press f9 or edit the worksheet, as least for several times.

Instead, I would enter the formula =NPV(C11,E11:M11)+D11 into B11 (for example), and use Solver to modify C11 until B11 becomes (close to) zero.

In practice, it is necessary to put some value into C11 (e.g. 100%) in order to avoid a #DIV/0 error, which aborts Solver.

Also, Solver derives a more accurate IRR in C11 if we execute it twice, using the first result as a "guess", or change the Solver iteration parameters (convergence etc).)

-----

The IRR is a discount rate that causes the NPV to be (close to) zero. It is one measure of return on investment (they are others) of a capital investment of 10,000 followed by 9 years of net operating income (NOI), which are the cash flows.

The value in M11 (about 9080) is the market value in Year 9 (treating the initial investment as Year 0) that results in the Year 9 NOI (2000).

To that end, I believe the formula in M11 should be =2000/C11 + 2000, since the last cash flow should account for the NOI (2000) as well as the market value (NOI / IRR). In that case, the IRR is about 22.86%.

-----

The question you probably want to ask is: what is the significance of assuming that the cap rate and the IRR are the same?

I have not been able to find an answer with a brief web search. My wild guess is: it is a break-even point of some sort. (TBD)

-----

You ask: "How does excel know which IRR to use?"

With these cash flows, I believe there is at most one IRR. There might be no IRR. And Excel IRR might return #NUM or #DIV/0 because it needs help from us by providing a "guess".

Again, the IRR is the discount rate (r) that causes the NPV to be zero. The NPV is the sum the discounted cash flows, to wit:

CF0 + CF1/(1+r) + CF1/(1+r)^2 +...+ CF9/(1+r)^9

 

[irie267]

Thank you for your response. For context, I was given a spreadsheet to audit and this is the first instance I've ever come across of using iteration to derive a cap rate (and inversely, a cap rate to derive an IRR). If I may, I will elaborate on your question: "what is the significance of assuming that the cap rate and the IRR are the same and are co-dependent on one another?" I am not a fan of using iteration normally, but here I have no other alternative since it isn't my spreadsheet. I haven't been able to find an answer online either. I am just struggling to conceptually understand what this circular relationship means.

 

[joeu2004]

Well, in this instance, that's all it means: the IRR (return on investment) is assumed to be the same as the cap rate. The phrase "co-dependent" adds nothing, IMHO.

That requirement creates a circular relationship only because there is no algebraic way to drive the IRR, in this case. That is, the IRR must be derived by iteration -- that is, the iteration internal to the Excel IRR algorithm.

But again, for the purpose of the audit, note that the formula in M11 is incorrect for the IRR calculation.

 

[joeu2004]

Well, in this instance, that's all it means: the IRR (return on investment) is assumed to be the same as the cap rate.

I meant to add: "to what end, I don't know". But I might have found the source of the (incorrect!) assumption.

In one online conversation [1], someone asserted: "IRR is the cap rate that makes the NPV of an asset equal to 0". Put another way, he is asserting: the cap rate is the discount rate that causes the NPV of the NOI cash flows to be zero.

Since the last cash flow must be the NOI plus the implied value of the capital assets (depreciated or appreciated), the last cash flow should be: NOI + NOI / IRR. NOI / IRR is the implied value because capRate = NOI / marketValue, ergo marketValue = NOI / capRate, and IRR = capRate by assumption.

However, that assertion is pretty-much trashed by other respondents, as well as by everything else I have read about cap rate and cap rate v. IRR.

The upshot is: they are unrelated.

Cap rate is determined by market factors, which is influenced by a lot of external factors like location.

IRR is a mathematical concept that is determined by the cash flows. It is the IRR, not the cap rate, that determines the discount rate that causes the NPV to be zero.

There is no reason to assume that the two would be related; at least, no reason that I've found so far.

This comes as no suprise. But I thought (wild guess) that assuming the two are equal would tell us something. That is, "when the two are equal, the following is true: blah blah blah".

But I think the prevailing opinion is: that is not the case; they are apples and oranges.

Bottom line: IMHO, the calculation that you are auditing is based on incorrect information.

But again, I am not knowledgable in this area.


-----
[1] https://www.wallstreetoasis.com/forums/cap-rates-vs-irr

 

[irie267]

Thank you for going above and beyond for this.