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

provision for doubtful debts meaningsimple annuity formulafinancial ratios calculator excelgeneral ledger entry examplessales markup calculatorhow to make adjusting journal entriesvertical analysis financial statementsfuture value of an ordinary annuity calculatorloan receivable journal entryledger reconciliation formatstraight line amortization tablebasic accounting knowledge testaccumulated depvoucher receipt sampleshare repurchase journal entryperpetuity present valuevariable cost definition and examplemicrosoft excel forms templatesraw material requisitionexcel input form templatepetty cash expenseseffective interest method of amortizationcushions definitionstraight line amortization tableaccounting for uncollectible accountsdepreciation amortisationcalculate mirr onlinewhy is prepaid expense an assetprepaid rental incomesimple accounting equationhow do you calculate capital employedadjusting entries can be classified ascash flow to creditors calculatoraccount receivable turnover calculatorjournal entry for capital accountnature of bills receivable accountpetty cash expensesdeclining method of depreciationdefine cusionsample cashflow statementsimple balance sheet template excelwhen closing entries are maderecording notes payablebookkeeping debits and creditsfreight prepaid and freight collecteconomic entity assumption accountingadjusting entries bad debt expensepresent value annuity duesingle entry bookkeeping definitionexample of lifofactoring of debtorsaverage trade debtorsoperating lease payment calculatordouble-entry accounting transactions must alwaysprepaid expense t accountchart of accounts for non profit organizationpresent value of increasing annuityhow to compute net sales in accountingfinancial statement projections templatedefinition of capital employedaccounting spreadsheet templateleverage ratios examplesperpetuity calculator future valuezero coupon calculatoraccounting quiz questions and answersdepreciation formula accountinghow to reverse accrued expensesaccounting fifo methodstatement of retained earnings exampleaccount receivable credit or debitimprest accountingjournal ledger templatecalculate variable costs per unithow to calculate average accounts receivablebookkeeping worksheetppe disposal