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 periodsIRR 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:


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

Copyright 2000-2006 by John R. Schuyler. All rights reserved. Permission to copy with reproduction of this notice.