Tip of the Week #64                     Tip Index

Go to the Prior TipThe 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

Be careful when using the IRR (Internal Rate of Return) and NPV (Net Present Value) functions in Excel. Top oil companies to invest in right now 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.

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:

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:

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 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 D

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

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/// spreadsheet.  The above table is on the worksheet tabbed 'Main.'

Download example Excel /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 /XP// 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) ^ = (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 / ) - 1
  
  The ^ (carrot) symbol is the power function.  An alternate function is provided in some non-English versions of Excel.
  
• Normally, I use how to calculate annual rate of return on investment in excel 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 how to calculate annual rate of return on investment in excel 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 %) and solve for i_yearly that makes the FV error = 0. Then, set i_yearly to a very small number (like -1 or %) 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, Januaryrevised April and Jan.

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

Are you ready how to calculate annual rate of return on investment in excel kick it up a notch?

In this article, we will calculate the average and geometric average return of a stock, using Excel.

Suppose you are interested in buying Apple stock. Your first task is to calculate the stock return of Apple in the last 2 years.

First, we are going to obtain stock prices.

For this, we will use Yahoo Finance, how to calculate annual rate of return on investment in excel. We already know a thing or two about it, right?

We begin by typing the stock ticker. Do you remember what it is? “AAPL”!

Afterward, we select historical data. We download the stock prices over the last 2 years from July 1^st, to July 1^st,

Then, we need to set the data frequency.

There are 3 options to choose from &#; daily, how to calculate annual rate of return on investment in excel, weekly, or monthly.

Basically, the choice of data frequency depends on the return you want to calculate.

When you estimate daily returns, you go for daily frequency. Weekly returns require weekly frequency. And so on.

Let’s say we are interested in calculating monthly returns. This gives us 24 data points.

We press ‘apply’ and then ‘download’, in order to retrieve the needed numbers.

Time to check out what we’ve got…

We open the downloaded file and come across the date and a whole bunch of information about the stock price, such as the opening, as well as the highest and lowest value during the period we’ve specified earlier.

Which are the figures our calculations require, though?

Well, we want to use either the close or the adjusted close price. The former corrects for splits, whereas the adjusted one does so for both dividends AND splits.

Hence, we will use the adjusted close price. For conciseness, we get rid of all the information we won’t need and format the table in a more presentable way, based on the guidelines provided previously.

To save you some time, I have already done that.

Our next task is to calculate the holding period return for the stock.

What’s the formula we should be working with?

It is the ending value of an investment minus its beginning value, divided by the beginning value.

In our example, we have $ minus $ divided by $ which gives us approximately %.

Finally, we go ahead and drag this formula all the way down.

Don’t forget to convert the values to percentages. Actually, you can get away with a simple shortcut.

Magicians never reveal their secrets, but we will willingly tell you ours: Excel is all about shortcuts! Be smart and use them as much as possible! This can save you a lot of time that you will eventually need for analyzing your data.

Here is one shortcut we can opt for, at this stage. Select the range of values, and then press and hold control plus shift, plus 5.

The last piece of the puzzle is to estimate the simple and geometric mean.

To obtain the former, we use the average function of Excel. We select the first argument and, after that, drag the range down to the last term.

Alternatively, we can use another useful shortcut. Press and hold Control plus shift plus the down arrow, how to calculate annual rate of return on investment in excel. This function marks the entire row of values below the cell you initially selected.

So, we estimate the mean return to be %.

Now, let’s calculate the geometric mean return.

For this purpose, we will use the geometric function. Basically, it gives us the geometric mean of an array or a range of positive data.

We type “GEOMEAN” and we pick the data range we will use. Don’t forget to add 1 to the expression before closing the parentheses.

The last step is to subtract 1 from the total.

Excel interprets the expression in the following way: Add one to each of the returns and then take the geometric mean.

For those of you using earlier versions of Excel ( or older), you need to press Control, Shift and Enter after you key in the formula. This command makes Excel convert the expression to an array formula. In other words, it performs multiple calculations on one or more items in an array. If you have done that right, you will see braces that are added to the formula.

The geometric mean comes to %.

In our next article, we will learn how to measure the standard deviation of the stock’s returns.

Keep calm and invest on!