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

closing income summary to retained earningsstock turnover ratio formula exampleformula to calculate simple interest and compound interestbook keeping templatesblank accounting ledgerthe purpose of closing entriesbookkeeping explainedgoods held on consignmentretail inventory method formulacommon size ratio formulahow to calculate bad debt expenseexamples of depreciating assetsfob shipping meaningdiminishing balance depreciation formulafactoring account receivablesgp markup calculatordefinition of notes receivablebookkeeping partnerscalculation of straight line depreciationjournal entries for depreciation of fixed assetdepreciation to fixed assets ratiojob card format excel sheetsundry definition accountingdiscounted cash flows exceljournal entries in accounts payable processperpetual annuity calculatordeferred taxation definitiondebt to equity ratio examplesbasic accounting quiz and answerswhat is brs with an exampleaccounting spreadsheets for small businesscreditors aging reportdiminishing balance depreciation formulabank reconciliation fraudthe amortization of bond discountdupont formulaaccounting inventory systemslease payment calculator excelexcel discount formulabasics of double entry bookkeepingunearned revenue on a balance sheetdefine fob destinationwhat is the difference between margin and markupaging the accounts receivablear turnover formulaformula for contribution margin ratiohow to use the fifo methodgross profit method formulawhat is prudence in accountingliquidity ratio formulasmall business chart of accounts spreadsheetbad debt reserve journal entryclosing stock debit or creditexcel formula future valuemargin vs markup calculationreverse stock split examplestock issuance journal entryaccounting entry for bad debtsending inventory calculatoraccounts receivable subsidiary ledgerdebit and credit quizchange in nwc formulawhat is inventory shrinkagesunk cost in accountingdiminishing balance calculatordepreciation residual value calculationinterest in suspense accounting treatmentthe accounts receivable turnoverbookkeeping spreadsheetswhat is the meaning of imprestcalculating mortgage constanttrade discount series formulabookkeeping excel templatefob destination definitionpetty cash on balance sheetaccounting accrued expensedays of receivables formula