How to Calculate IRR in Excel and Financial Calculator
Calculating internal rate of return (IRR) can be tedious if you have multiple cash flow periods to work with. Fortunately, using financial calculators and Microsoft Excel to calculate IRR makes the process amazingly simple.
For both examples, we'll use the following data set:
Company ABC wants to know whether it should buy a $500 piece of equipment. It projects that it will increase profits by $100 in Year 1, $200 in Year 2, and $300 in Year 3. With this information, you can calculate the IRR of the proposed project.
How to Calculate IRR with a Financial Calculator
: the steps in this tutorial outline the process for a Texas Instruments BA II Plus financial calculator.
1. Enter the Cash Flow Values
For each period, enter cash flow values into the calculator's cash flow register (press the Cash Flow [CF] key to open the cash flow register). The calculator should read CF0=, which tells you to enter the cash flow for time 0.
Because you need to send cash out of the company to make the initial $500 investment, this value has to be negative. Type in -500 for CF0 and hit the [ENTER] key.
2. Enter the Cash Flow Values for Subsequent Periods
This is done by hitting the down arrow once. The calculator should read CF1=. Type in the amount for the first cash flow, 100, and hit [ENTER]. The calculator should now say C01= 100.
To enter cash flow from Year 2, hit the down arrow twice. The calculator should read CF2=. If it says F1=, hit the down arrow one more time.
Type in the second year's cash flow, 200, and hit [Enter]. The calculator should read CF2= 200. Hit the down arrow twice again and do the same thing for the third cash flow period, CF3. If the data set has more periods, follow the same procedure for C04, C05, etc.
3. Calculate the IRR
Press the [IRR] key so screen will read IRR= 0.000. To display the IRR value for the data set, press the [CPT] key at the top left corner of the calculator. If you have followed this process correctly, the calculator will display the correct IRR. For our example, the IRR is 8.208264%.
How to Calculate IRR in Excel
Finding the IRR using Excel is fairly straighforward.
1. Enter Initial Cash Flow Into Any Spreadsheet Cell
This initial investment has to be a negative number. Using our original example, type -500 into the A1 cell of the spreadsheet.
2. Enter Subsequent Cash Flow Values
For each period, enter the subsequent cash low values into the cells directly under the initial investment amount. Following our example, type 100 into cell A2, 200 into cell A3, and 300 into cell A4.
3. Calculate the IRR
To instruct the Excel program to calculate IRR, type in the function command "=IRR(A1:A4)" into the A5 cell directly under all the values. When you hit the enter key, the IRR value (8.2%) should be displayed in that cell.
This same procedure can be followed for any data set if the cash flow values are listed one after another in a column – directly under the initial investment amount. You should then include the range of cells between the parentheses of the IRR command function.
Get More Great Financial Advice at InvestingAnswers
Click here to see financial calculators developed specifically for InvestingAnswers' readers, including Return, Mortgage, and Yield Calculators.
Personalized Financial Plans for an Uncertain Market
In today’s uncertain market, investors are looking for answers to help them grow and protect their savings. So we partnered with Vanguard Advisers -- one of the most trusted names in finance -- to offer you a financial plan built to withstand a variety of market and economic conditions. A Vanguard advisor will craft your customized plan and then manage your savings, giving you more confidence to help you meet your goals. Click here to get started.