What’s Calculus Have to Do With It?
Using Excel to Calculate Investment Rates of Return

By August A. Saibeni

E-mail Story Print Story

Computing internal rates of returns (IRR) for capital budgeting problems or analyzing portfolio rates of return is easy to perform using XIRR, a variation of Excel’s IRR function. If the XIRR function is not available, click on the “Tools” menu and select “Add-ins.” In the Add-ins available list, select the “Analysis ToolPak.” In order to properly use IRR, capital contributions and withdrawals must be spaced evenly, for example, at the end or beginning of the year. Using XIRR, however, rates of return can be calculated with random capital contributions and random withdrawals. (If contribution dates at the beginning or end of the year are entered, then XIRR and IRR would produce the same result.) For example, assume an individual invested $100,000 at the beginning of 2000 and makes subsequent investments of $100,000 at the beginning of 2001 and $50,000 at the beginning of 2002, and withdraws $265,000 at the end of 2002. What is the IRR considering these staggered investments and ultimate withdrawal?

Three Methods

One can derive this answer using trial and error, IRR, XIRR, or Newton’s Method, which is the algorithm behind IRR and XIRR. Exhibit 1 shows the traditional method of computing internal rate of return, prior to the advent of programmable calculators and spreadsheet programs. The process involves trial and error to find the interest rate that equates the present value of capital contributions and withdrawals, such that the summed present value of all contributions and withdrawals is zero. For simplicity, assume that after a series of trial-and-error calculations, the estimated 2.68% rate of return causes the sum of the present values to equal zero. To compute the required present values, multiply column B amounts by column C discount factors to derive the present value of the contributions from the beginning year (0) to the withdrawal year (3).

The well-known mathematical relationship solved by the trial and error method is:

$100,000 + $100,000/(1+i) +$50,000/
(1 + i)2 – $265,000/(1 + i)3 = 0

where (i) is the IRR with the discount factors calculated as 1/(1 + i)n. For example, the discount factor for Year 3 is 1/(1.02675)3, or 0.9239. Prior to computers and programmable calculators, other than trial and error, one could use a method developed more than 300 years ago by Isaac Newton. He substituted “x” for (1 + i) and then multiplied by the highest power of x to simplify the equation. For example, this equation, expressed in thousands, is:

f(x) = 100x3 + 100x2 + 50x – 265 = 0

The first derivative of this equation is:

f'(x) = 300x2 + 200x + 50 = 0

The next step is to “guess” at a first solution and then evaluate the two equations to determine an adjustment factor. After finding the adjustment factor, it is added or subtracted to the previous guess. The process continues until the adjustment factor becomes insignificant. Assuming one as a first guess, f and f' can be calculated as follows:

f(1) = 100 + 100 + 50 – 265 = –15
f'(1) = 300 + 200 + 50 = 550

Newton’s Method requires the negative ratio of f/f' (i.e., –f/f'), which is 15/550 or 0.02727. Added to the guess of 1, this makes a total of 1.02727. Completing another iteration yields a new correction factor of –0.00045. Combining with the previous estimate of 1.02727 yields 1.02675. Subtracting one from this yields i = .02675, or 2.675%, which is the same answer gotten through the trial and error method.

Excel’s IRR function, shown in Exhibit 2, calculates the same rate of return found by trial-and-error. Note that it does not matter if positive numbers represent contributions and negative numbers show withdrawals, as long as they are consistent; thus, only one column is needed. IRR assumes that transactions occur at the beginning or end of the year.

As shown in Exhibit 3, XIRR requires an additional input column for the time or date of the transaction. Note that the computed rate of return for the IRR function is 2.675% and the computed return using XIRR is 2.674%.

Exhibit 4 uses the same contributions and withdrawals as the prior examples, but now assumes that the contributions were made at dates other than the beginning or end of the year. Note that this change in timing means that the IRR is now calculated as 2.777%.

Assuming that the above examples represent contributions to a money market fund rather than some capital budgeting project, one can continue the analysis to compute an ongoing rate of return for any beginning and ending period.

Exhibit 5 assumes that an investor has a money market fund for excess cash and wants to compute a personal rate of return for the period from 1/1/2000 through 12/31/2003. In order to compute our personal return for the entire period from 2000 through 2003, the $265,000 that was assumed withdrawn at the end of 2002 must be added back in another entry.

The Easy Way

Without programs such as Excel, there is no easy way except numerous iterations to calculate the demonstrated returns. For two-period returns, one can use the quadratic equation; however, once the duration of the investment exceeds three periods, especially if the contributions and withdrawals are made randomly, an iterative approach is required. Students who have sat through a calculus class might be surprised to hear that a method devised by Isaac Newton 300 years ago, combined with modern technology, can be a useful financial tool. As shown above, Newton’s method requires an iterative process, which a computer can easily use to find desired answers to internal rate of return problems. Excel’s XIRR function proves that quite well.

©2009 The New York State Society of CPAs. Legal Notices