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

accrual to cash adjustment worksheetadjusting entries merchandise inventorydouble declining method depreciation formulaprepaid insurance definitionreturn on capital employed examplejournal entry for deferred tax assethow to record closing entriestrial balance definition in accountingunearned revenueadjusting entries are recordeddays receivable outstanding formulajournal entry for stock dividendincome statements examplesnominal annual rate formulaexamples of accrued incomewhat is payback period formulapercentage complete formula exceldeffered tax meaningwhat is closing entries in accountingtvm calculator free downloadwhat is the formula for inventory turnovershort term notes payable definitionformula to calculate future value of annuitybookkeeping ledger templateprofit margin calculator excelaccounting for manufacturing overheadmarkup calculationstandard costing and variance analysis formulaannuity and perpetuityprepaid rent accountingpetty cash ledgerreconciliation sheet templatedebtors ledgerabsorption costing accountingdouble entry accounting transactions are made up ofdiscount bond calculatorinventory adjusting entrybasics of accounts receivablemonthly balance sheet excel templateprovision for doubtful debtexcel spreadsheet accounting templatesaccounting concepts principles and conventionsnper functionirr in accountingfuture value in excelperiodic matching of cost and revenue conceptpayroll clearing accountfactored invoiceproveit data entry testmarkup ratio formulapv of annuity due tablediminishing method of depreciationfuture value calculator excelbad debt journal entry examplelifo reserve accountmcq on accounting with answersfixed manufacturing overhead cost formulapetty cash float definitionrevaluation loss journal entrycollecting accounts receivabledepreciation entries for fixed assetsprepaid expenses balance sheet examplefind future value of annuityconsignor and consignee meaningexample of a petty cash booktrade discount in accountingexcel templates for small business accountinggoodwill journal entrymarkup and profit marginsalaries payable journal entrypresent value perpetuity calculatoronline tvm calculatorincome statement spreadsheetaccumulated depreciation journal entrypv lump sum tableannuity discount factorinterpretation of gearing ratioaccounting aptitude questions and answershow to calculate pbitdays accounts receivable formulareducing balance depreciation formulaperiodic inventory systemsretained earning balanceexcel formula future value