 


What’s
Calculus Have to Do With It?
Using Excel to Calculate Investment Rates of Return
By
August A. Saibeni
JANUARY 2008  Many
a student has thought: What’s calculus have to do with an
accounting major? Prior to desktop computer programs such as Microsoft
Excel, calculus had plenty of relevance if accountants wanted
to compute investment rates of return with random dates and amounts
of investments or payouts. Calculus is still behind the scenes,
but with functions such as Excel’s XIRR, most users can
solve investment rate of return problems without worrying about
calculus.
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
“Addins.” In the Addins 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 trialanderror 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 wellknown
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 trialanderror.
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 twoperiod 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.
August
A. Saibeni, MS, CPA, is an adjunct professor of accounting,
auditing, and taxation at Cosumnes River College, Sacramento, Calif.

