How to Calculate Internal Rate of Return (IRR) with Excel

Internal rate of return (IRR) that is also known as the discounted cash flow rate of return (DCFROR), is commonly used to evaluate the profitability of an investment. For folks who like to sign up for an investment plan, endowment plan and wealth accumulation plan to enhance their wealth management and diversification certainly would like to know the internal rate of return. Normally, the IRR is not mentioned in the plan, but can be easily calculated with Microsoft Office Excel application. With Microsoft Excel, you can calculate IRR yourself to avoid any misleading cases due to dishonest financial planner and agent, you need a series of periodic cash flows like that shown in the figure below.

The IRR can then be calculated using the following formula, with 0.1 being the initial guess at the rate:

=IRR(values,guess)
=IRR(B2:B6,0.1)

The IRR formula requires at least one negative and one positive value. Normally, the negative value at t=0 represents the initial investment. The future cash flows can be negative or positive, but they need to be periodic (occuring at t=1, t=2, t=3, etc). If you have non-periodic cash flows, you can use the XIRR formula.

VLOOKUP function in excel an extremely valuable function

VLOOKUP is the first complex formula they learn.  Performing a lookup is an extremely valuable function for any situation where you’re dealing with large data sets.  While the VLOOKUP function itself has become somewhat dated, as there are other better alternatives such as INDEX MATCH, it is still important to learn this function because:

§  VLOOKUP is probably the simplest lookup formula of the ones available and if you have no prior spreadsheet experience, it should be the formula you start off with

§  Despite being dated, many people still use VLOOKUP in their spreadsheets and if you ever end up auditing someone else’s spreadsheet, you’ll need to know how this formula works

Understand the Objective

The first step to learning VLOOKUP is knowing when to use it.  VLOOKUP is used for two primary purposes:

1.    To look up a value or a series of values from a large database

2.    To append a column to a table using data from a larger database

The process for using VLOOKUP doesn’t change much between the two objectives, so we’ll focus on the first objective.  In the example below, we have a value called ID circled in blue and a table of data circled in green.  The cell highlighted in yellow is the return value, where we are missing a value for State.  Essentially, we want to know which State is relevant to ID number “5”.

Syntax

After understanding your objective, it’s important to learn the syntax, or inputs required, to write out the VLOOKUP formula.  The syntax for VLOOKUP has changed over time to make it simpler and easier to use.  In the latest version of Excel (2010 at the time of this writing), the syntax for a VLOOKUP automatically appears once you type in the formula and an open parenthesis.  We’ll look at each component individually.

=VLOOKUP ( lookup value , table_array , col_index_num , [range_lookup] )

Lookup value

The lookup value is the value we need more information on.  In the example below, this is theID number “5″.  We will start by typing our formula in the blank cell where we want to return the missing information.  Then we select the lookup value for the first component of the syntax and follow it with a comma.

Table Array

The table array is larger data set where your return value exists.  In most cases this dataset will be large enough that you’ll want to use a formula to pull the value, rather than looking it up manually.  Once you become proficient at VLOOKUP, it also reduces the possibility of making a mistake.

To select your table array, simply click and drag around the entire data set that is relevant to your lookup formula.  And again, complete the entry of this component by inputting a comma.

Column Index Number

This component simply references which column you want to pull back your data from.  It’s important to note that the minimum number for this input is 2 – you cannot look up and return the same value  (as doing so probably doesn’t make much sense).  The maximum value depends on how large your table array is.  In the example below, we only have 5 total columns.  Therefore, our maximum input for column index number is 5.  Inputting any number higher than 5 will return an error value.

In the example below, since we want to return the State related to our ID, and City is the fourth column in the table array we selected, we input the number “4″.

Range Lookup

Few people actually know what a range lookup does, because range lookup is a functionality that few people ever use.  The range lookup simply tells your lookup formula to look for values that are close to your lookup value, not ones that are exactly the same.  The rules on what’s “close to” your lookup value are vague and if you’re working on a project where you need some level of precision, you can see why this functionality can be problematic.  You basically have three options to deal with the range lookup portion of the syntax:

1.  Ignore it

Since it isn’t a popular function, the latest version of Excel allows you to simply leave out this last piece of syntax.  Therefore, instead of entering anything for this, you can simply input a close parenthesis and finish writing the formula.

2.  Choose not to use range lookup

To choose this option, input either a “0″ or the word “FALSE”.  This has the same effect as ignoring the last piece of the syntax; it just adds more keystrokes to your task.  However, many of us who’ve used this formula for years are so used to incorporating it that we always put this last piece of syntax in.  That’s why you see so many VLOOKUP formulas end in with the world FALSE.

3.  Choose to use a range lookup

To turn on the range lookup functionality, simply do the reverse of the prior inputs, and enter either “1″ or “TRUE”.

For our particular example, because we want an exact match, we will input “0″ so the VLOOKUP does not perform a range lookup.  After you’ve written your formula with your desired inputs, close the formula with a “)” and press ENTER to finish writing it.

What Excel Does

If you’ve written the formula properly, the value you were looking for will be returned in the lookup result cell.  After you’ve written all of your parameters, the formula performs a vertical lookup:

1.    Starting from the top of the leftmost column of your table array, it searches for your lookup value

2.    Once it finds your lookup value, it stops moving down and begins moving over to the right based on the column index number you specified

3.    After moving right by the specified number of columns, it returns whatever value it lands on

If your lookup result cell comes up with an error, there are a number of reasons why this might have occurred.  Over time you will learn how to troubleshoot your errors.

MS Excel RANK Function

RANK Function Basics

 If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order.

For example, here's a list of 10 student test scores, in cells B2:B11. To find the rank of the score in cell B2, enter this formula in cell C2:

=RANK(B2,$B$2:$B$11)

There are 3 arguments for the RANK function:

• number: in this example, the number to rank is in cell B2
• ref: We want to compare the number to the list of numbers in cells $B$2:$B$11. I used an absolute reference, so the referenced range will stay the same when we copy the formula down to the cells below
• order: (optional) Use zero, or leave this argument empty, to find the rank in the list in descending order. For ascending order, type a 1, or any other number except zero. I left this blank, to find the rand in descending order. If you were comparing golf scores, you could type a 1, to rank in ascending order.

I copied the formula down to cell C11, and the scores were ranked in descending order, as promised.

 

Excel INDIRECT Function

The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

INDIRECT Function Arguments

The INDIRECT function has two arguments:

1. ref_text: A cell reference or text string (or both), that create the range reference. The referenced range can be a cell, a range of cells, or a named range.
2. a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE, or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.

 

Note: If the INDIRECT formula refers to a different workbook, that workbook must be open, or the formula will return a #REF! error.

Lock a Cell Reference

If you create a simple link to a cell, e.g. =A4, then insert a row above row 4, your formula will automatically change to =A5. To prevent this change, you can use the INDIRECT function.

To demonstrate the difference, you'll create two formulas -- one that uses a simple link, and one that uses a text string with an INDIRECT function.

1. In cell A4, type a number (10 in this example).
2. In cell B2, type the following formula:
      =INDIRECT("A4")
3. Press the Enter key, and the formula will return the number in cell A4.
4. In cell C2, type the following formula:
      =A4
5. Press the Enter key, and the formula will return the number in cell A4. 
6. To insert a row, right-click on the Row 4 button, and from the pop-up menu, choose Insert.
7. As shown below, cell B2, which uses the INDIRECT function, now returns 0, because cell A4 is empty. The text string, "A4", in the INDIRECT formula did not change when the row was inserted.
8. Cell C2, which contains the link, still returns 10, because its formula has changed to =A5. 

 

Create a Reference from a Cell Value

To create the ref_text argument, you can also refer to a cell, and use its contents. This makes the formula more flexible, as its results will change, if the cell contents change.

In this example, you'll create formulas with the INDIRECT function, to compare the A1 and R1C1 reference styles.

1. In cells A1:A5, type a set of numbers, as shown below.

2. In cell C2, type:  A4
3. In cell C2, type:  R4C1
4. In cell D2, type the formula: =INDIRECT(C2)
5. Press the Enter key, and the formula returns the number in cell A4
6. In cell D3, type the formula: =INDIRECT(C3,FALSE)
7. The FALSE in the second argument indicates that the R1C1 reference style will be used.
8. Press the Enter key, and the formula returns the number in cell A4