Excel IRR Function

The Excel IRR function has the syntax shown below.

Arguments used in the Excel IRR function
Values = A range of cash flows
Guess = Initial guess at IRR

Use of the Excel IRR Function

The Excel IRR function is used to calculate the internal rate of return of a series of cash flows. The internal rate of return is the discount rate which produces a net present value of zero.

The cash flows can be unequal but must occur at the end of each period.

The Excel IRR function uses an iterative (trial and error) process to find the internal rate of return. If the argument Guess is omitted, then the Excel IRR function assumes a value of 10%, and in most cases the function will find a solution. If a solution is not found then entering a different guess may help the function to produce an answer.

As the use of the Excel IRR function relies on finding the discount rate which produces a net present value of zero, the cash flows must contain at least one positive and one negative value.

Internal Rate of Return of an Investment Project

The Excel IRR function can be used to calculate the internal rate of return of an investment project.

Suppose for example, a business plans to invest 2,300 today (start of year 1) and will receive cash flows of 1800, 600, and 300 at the end of each of the following 3 years. As numeric values cannot be entered directly into the Excel IRR function, a spreadsheet needs to be set up as shown below.

excel irr function v 1.0
Excel IRR Function

In this example spreadsheet, the variable arguments are entered in cells B5 to B10, and the Excel IRR function is entered at cell B13 as =IRR(B5:B8,B10).

It should be noted that the original investment is negative as it represents a cash flow out.

By changing any of the variables in B5 to B10, the internal rate of return of the cash flows can be recalculated without having to enter the Excel IRR function each time. It should be noted that if a cash flow is zero it has to be entered as zero (0), it cannot be left blank otherwise the Excel IRR function will return the wrong answer.

In this example the internal rate of return of the project is given by the Excel IRR function as 11.96%.

Excel IRR Function when Cash Flows Change Sign

To produce a meaningful answer, the Excel IRR function is expecting an initial investment (negative cash flow) followed by a series of cash receipts (positive cash flows). While is will provide solutions if the cash flows change sign multiple times throughout the period of the investment, they may or may not be meaningful solutions.

Consider the following example cash flows -200, 3000, -2,900, -400. The cash flows change sign throughout the term of the project. The Excel IRR function will produce a solution at 17.20% and 1,295.03% depending on where the initial guess is set.

Both answers are mathematically correct in that they produce a net present value of zero. However, from an investment point of view, if we ignore discounting and simply add the cash flows, they total -500. Whatever the discount rate, it cannot be possible to lose money but have a positive rate of return, the answers are not meaningful and should not be used.

The Excel IRR function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

Excel IRR Function November 6th, 2016Team

You May Also Like

Related pages

monthly balance sheet excel templatesample of adjusting entriessubsidiary ledgers and special journalschange in nwcowner's drawingssum of years digits formulaaccounts payable excelcalculating perpetuitytrial balance template excelgross margin and contribution marginpayback period formula uneven cash flowsdefine daybookprovisioning in accountinginventory spreadsheet templatecontinuos compoundingpetty cash voucher template free downloaddeclare dividends journal entrybalance sheet spreadsheetthe amount of a promissory note is called theassets equals liabilities plus capitaldebits and credits t accountsis accounts payable a credit or debitcash flow projections template excelaccounting for outstanding checksnotes on consignment accountingcash reconciliation test citcopayment booklet templatediminishing balance depreciation methodaccounting quizzaging of accounts receivablegross margin excelweighted average formulastotal asset turnover ratio calculatorcoupon bond price formulapresent value annuity duepresent value in perpetuitywhat is the meaning of retained earningsannuity due sample problems with solutionsjoint venture accounts examplescushions definitionformat of petty cash bookdouble entry bookkeeping practice questionssample restaurant chart of accountsinventory obsolescence accountingwhat does gaap stand for in accountingadjusted and unadjusted trial balanceprepayments in balance sheetaccrued expenses in accountingsingle entry bookkeepingvariable costs are controllable and fixed costs are notexample of a single step income statementinventory sheets templatebills receivable definitionhow to calculate manufacturing overheadcontra account to accounts receivableaccounting overheadsaccounts receivable debitaccounts receivable allowance methodcalculate zero coupon bonddepreciation slm methodwhat is a compound journal entry in accountingjob sheet templatesbookkeeping partnerscollection of accounts receivable journal entryretained earnings formula on balance sheetbasics of book keepingstraight line method of depreciation examplesteps involved in adjusting entriessale of receivables without recoursecommon bookkeeping entriestrade discount entrysegregation of duties checklistgross margin formula excel