For Nerds Only: Calculating IRR –

Warning: Unless you are a nerd, VCJ strongly advises you to avoid reading the following story. It may cause migraines. -Ed.

In its proposed guidelines, the AIMR defines the internal rate of return as “the annualized implied discount rate (effective compounded rate) which equates the present value of all of the appropriate cash inflows (paid-in capital such as draw downs for net investments) associated with an investment with the sum of the present value of all the appropriate cash outflows (such as distributions) accruing from it and the present value of the unrealized residual portfolio (un-liquidated holdings). The proposed guidelines of BVCA and the EVCA define “IRR [as] that rate of discount which equates the present value of the cash outflows associated with an investment with the sum of the present value of the case inflows accruing from it and the present value of the valuation of the unrealized portfolio.”

Got that? One phrase in that turgid text leaves a barn-sized hole through which VC firms can drive their IRRs, that phrase being “the value of the unrealized portfolio,” which is the focus of this month’s cover story. American firms are happy with this large opportunity to exercise their judgment. However, in an attempt to make IRR calculations understandable, and perhaps replicable (for accountancy purposes) AIMR guidelines also provide the formula (see below) for the calculation of interim IRR. The BVCA guidelines’ discussion of a formula is akin to a footnote, while the EVCA spends five pages providing a complete derivation of the formula’s variables and solution. It also considers an alternate formula (Newton-Raphon’s method).

The AIMR formula for the generation of IRR for a given sub-period is as follows:

Where CF is the cash flow for period i, n is the total number of cash flows, i is the period of the cash flow, c is the number of annual cash flow sub-periods (e.g., c = 365 for daily cash flow), and r is the sub-period IRR.

The sub-period IRR is converted to an annualized IRR, R, as follows: R = (1+r)c- 1

The EVCA guidelines provide more detailed explanation of all of the variables of their similar version of this formula. Michael Pritchard, research coordinator at the EVCA, says that IRR is traditionally calculated on an annual basis. However for more up-to-date reporting to investors and calculation of portfolio values, many firms are using more frequent monthly or even daily IRR calculations for their portfolios. No wonder then, that many, if not most American firms simply generate IRR from within Microsoft Excel. The EVCA guidelines, however, point to problems in generating IRR with Excel.

Pritchard says that when using a program like Excel, it is best to choose the XIRR function. He says that if you try out a few cash flow problems with the IRR and XIRR you will see that the IRR function is more basic and may give you strange figures unless it is understood properly. XIRR, however, allows you to add in cash flows on an irregular basis (as is the case for many portfolios). Pritchard also recommends care when using either IRR or XIRR, so as to ensure that the Excel formula incorporates the period basis that you’re calculating (annually, monthly, etc.). In these cases, the interpretation of the percent IRR should be made carefully.

Most VC firms have small administrative staffs, so they don’t have time to apply exotic formulae to their day-to-day work or for less frequent IRR calculations. In fact, they use application software that will allow them to extract numbers from existing applications like databases, and then apply them to accounting and reporting applications.

At Walden International, CFO Nancy Lee has used two software packages, templates for Microsoft Excel, in generating her firm’s IRRs: TimeValue Software’s Tvalue Engine and Entisoft’s Entisoft Tools (see box, opposite page). At Alta Partners, CFO Hillary Strain uses AnalytX’s Venture Complete Software and Financial Technologies’ Investran. Strain, a former CPA at Arthur Andersen, says that she has observed a real range of software being used by peers at venture firms-from Excel to home-grown solutions to all-in-one packages like Venture Complete.

Many venture firms provide data to companies such as Cambridge Associates or Grove Street Advisors for verification, and these third parties then supply the verified numbers to limited partners. Some LPs use their own software to develop IRRs.

For Lee, the key is not the software she uses but the clarity of information in the cash-flow statement, so that anyone looking at her data can perform their own IRR calculations.

Alta’s Strain says she does not generate IRRs for her quarterly reports. Instead, she provides a capital-based statement for the firm’s limited partners, so that the numbers revealed in public disclosures are generated by the institution making the disclosure, not Alta.

As to the broader question of whether a formula, like that of AIMR/EVCA, is useful and appropriate for calculation of IRR, VCJ asked Ray Rothrock, general managing partner of Venrock Associates. He holds a master’s in nuclear engineering from MIT and an MBA from Harvard MBA and he once did theoretical analysis of complex return on investment models for Enron. Rothrock posits the question in this way: “In general, is transparency better? Yes, open systems are theoretically better than closed systems,” so having a formula to apply to calculations of IRR is good. He continues: “Does using such a formula change the nature of the venture industry? Probably. Is the VC industry becoming institutionalized? Probably. But in the end, I don’t think that Adam Smith’s Invisible Hand allows such a simple formula in quantifying venture capital [returns].”

For comparison, Rothrock points to the use of the Black-Scholes formula for the valuation of stock options, and the use of Nash’s Equilibrium formula in economics. “Black-Scholes is taught in undergraduate schools, but it’s a complicated formula that has a lot of use without any real understanding of the values like stock prices and volatility, that underlie the formula,” he says.

Does Venrock use a spreadsheet or such for its own calculations? “Certainly not,” Rothrock says. “We’re an evergreen fund. The Rockefeller foundation has developed proprietary software which takes into account factors such as inflation, which aren’t reflected in simple spread sheet models.”

Hmmm. So much for standards.