Calculating Internal Rate of Return Using Excel or a Financial Calculator

posted on 06-07-2019

Calculating Internal Rate of Return (IRR) can be tedious if you have multiple cash flow periods to work with.  Fortunately, financial calculators and Microsoft Excel make the process amazingly simple.

For both examples, we'll use the following data set:

Assume 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. Now you can calculate the IRR of the proposed project.

Using a financial calculator:

Note: 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 into the calculator's cash flow register.  This is done by pressing 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 investing, this value has to be negative.  Typed in -500 for CF0, and hit the [ENTER] key.

2. Next enter the cash flow values for the 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 and so on.

3. Once the cash flow values have been entered into the calculator you are ready to calculate the IRR. 

To do this press the [IRR] key. The 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%.

Using Microsoft Excel:

Finding the IRR using Excel is fairly straighforward.

1. First, type the intial cash flow into any cell on the spreadsheet. Keep in mind this initial investment has to be a negative number. Using our original example, type -500 into the A1 cell of the spreadsheet.

2.  Next, just like the calculator, you will type the subsequent cash flow values for each period 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. Finally you are ready to 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 intial investment amount. You would then put the range of cells in between the parentheses of the IRR command function.

Click here to see financial calculators developed specifically for InvestingAnswers' readers, including Return, Mortgage and Yield Calculators. Go to Financial Calculators...

by Christian Hudspeth What's even better than earning rewards for spending on your credit cards? Getting paid hundreds of dollars worth in sign-up bonuses in three months or sooner -- just for tr...
by Christian Hudspeth Tired of dragging credit card debt around with you? Taking 15 minutes to transfer your debt to a credit card with generous balance transfer perks could save you thousands in...
by Christian Hudspeth If you're going to spend money anyway, then why not get paid for it?Whether you're looking for credit cards with up to 6% cash back, double flight miles, or even a free hote...
by Christian HudspethIn times where interest rates are on the rise, you may start hearing financial advisors and bankers sing the praises of an income strategy called "CD laddering" (short for ce...
by Susan Campbell Those of us familiar with selling property know real estate agents don't come cheap. With real estate agent commission and fees amounting to as much as 6% of the sel...
Beverly Harzog is a nationally recognized credit card expert, author, and consumer advocate. She blogs about credit cards at BeverlyHarzog.com. Being in credit card debt is the pits. I've bee...
by Christian Hudspeth If you haven't already felt the pressure to refinance your mortgage, you're probably really feeling it now. Mortgage rates are still hovering near historic lows. But ...
by Christian Hudspeth If you or someone you know is thinking about getting a home mortgage, you may want to know about the thousands of dollars in hidden charges that some lenders are quietly...
by Christian Hudspeth Money market accounts (MMAs) and savings accounts make great places to set aside your emergency fund money and earn some interest income at the same time.Simply put, these s...
by Christian Hudspeth It's true that auto loans and home loans offer attractively-low annual percentage rates (APRs), while credit cards offer borrowing power without the risk of ever seeing the ...
by Christian HudspethWant to keep your emergency fund safe while earning interest yields that are three to five times higher than a typical savings account? Putting your money into an FDIC-insure...
by Christian Hudspeth Question: Hi there. I need your advice. I'm only 19 and I really need to start investing. Where can I start? -- Tirelo M., Gaborone, Botswana Answer: You've defini...