Tip of the Week #64 Tip Index

Go to the Prior Tip The Accidental Theorist and Other Dispatches from the Dismal Science
Go to the Next Tip Choice of Denominator in Investment Ranking Criterion
Return to MaxValue Home Page

Calculating Return on Your Investment with Excel

Be careful when using the IRR (Internal Rate of Return) and NPV (Net Present Value) functions in Excel. They will typically provide incorrect results unless your project matches the timing assumptions of the functions. NPV assumes that cashflows are realized at the end of periods. IRR assumes that the cashflows happen at regular intervals.

For well-behaved cashflow models, where there are not multiple solutions to IRR, the most reliable solution for IRR is so solve for i (the discount rate) such that NPV=0.

The definition of Internal Rate of Return: the PV discount rate, i, that provides an NPV equal to zero.

IRR is most commonly meant when people refer to "rate of return."

Multiple solution occur when the cumulative net cash flow changes sign more than once. This happens often with projects that have late investments, large abandonment and reclamation costs. Rate acceleration projects also have this effect.

Here is a variant that applies the method. A colleague recently wanted to calculate his client's return on investment for a retirement account investment portfolio . The data were:

	Date	Amount
Start Balance	31-Dec-97	$38,191.20
Contribution 1	15-Oct-98	$18,000.00
Contribution 2	15-Sep-99	$18,000.00
Ending Fund Value	31-Dec-99	$105,587.68

The IRR is the rate of return such that the Future Value of the Starting Balance and Contributions equals the Ending Balance. This table illustrates the calculations:

	A	B	C	D	E	F	G
6		Amount	Date	Future Value		27.54	Annual Rate of Return
7	Start Balance	$ 38,191.20	12/31/97	$ 62,098.24		.00066613	Daily Rate of Return
8	Deposit 1	$ 18,000.00	10/15/98	$ 24,160.11
9	Deposit 1	$ 18,000.00	9/15/99	$ 19,329.33
10			Future Value	$105,587.68
12	End FV target (end balance)		12/31/99	$105,587.68
13			error	$ 0.00

The Future Value calculation is::

FV = CFamount * (1+i)^t

where t is the time from the CFamount date to the future value. The dates are entered using the DATE(yyyy,mm,dd) function. I used i as a daily rate, with a calculation assuming 365.25-day years.

IRR normally is solved with an iterative process. The Goal Seek feature of Excel does this quickly and easily.

We target the sum of the FVs in for the three amounts,
D12 = SUM(D7:D9)
to match the actual FV, the account end balance in D12.

Cell D13 measures the error between the calculated FV, in D10, versus the actual and target FV value, in D12.

To solve, run Tools, Goal Seek.
Set cell error D13 to value 0 by changing cell F6.

IRR should be only a supplemental, not primary decision criterion. It works poorly with probabilities and does not represent value. Nonetheless, many companies have long traditions of using IRR in decision policy. Please ensure that your calculation is correct.

Download example Excel XP/2003/2000/2002 spreadsheet. The above table is on the worksheet tabbed 'Main.'

Download example Excel 5.0/95 spreadsheet.

One site visitor, after looking at the download example worksheet, asked about calculating the return directly as an annual rather than daily rate. The Excel 2003/XP/2000/2002 spreadsheet has an added worksheet (tab 'Alternate') demonstrating alternative calculations.

Additional notes:

Converting between daily, monthly, and yearly rates is easy knowing the following relationships:
(1 + i_daily) ^ 365.25 = (1 + i_yearly)
(1 + i_monthly) ^ 12 = (1 + i_yearly)

Rearrange the appropriate equation to get what you want. For example:
i_daily = (1 + i_yearly) ^ (1 / 365.25) - 1

The ^ (carrot) symbol is the power function. An alternate function is provided in some non-English versions of Excel.
Normally, I use discount rates as fractions. This example was derived from something I did for an accountant friend, and I thought he and his client would be more comfortable seeing percents.
More often, I'm solving for an investment's IRR. Then, the Goal Seek function is used to find i_yearly such that NPV=0.
As mentioned earlier, watch out for multiple IRR solutions if the cumulative net cash flow changes signs more than once. If you suspect multiple roots, then do two goal seek operations: Set i_yearly to a large rate (like 10 or 1000%) and solve for i_yearly that makes the FV error = 0. Then, set i_yearly to a very small number (like -1 or -100%) and solve. You will usually get the same answer. Instances with more than two solutions are rare without a late-life negative cashflow (e.g., abandonment cost).

—John Schuyler, January 2000, revised April 2004 and Jan. 2006