Calculating Internal Rate of Return Using Excel or a Financial Calculator
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 Year 1, $200 in Year 2, and $300 in Year 3. Now you can calculate the IRR of the proposed project.increase profits by $100 in
#-ad_banner-#Using a financial calculator:
: the steps in this tutorial outline the process for a Texas Instruments BA II plus financial calculator.
1. Enter theopen the cash flow register. The calculator should read CF0=, which tells you to enter the cash flow for time 0.values for each period into the calculator's cash flow register. This is done by pressing the Cash Flow [CF] key to
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 screenread 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 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, youtype 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 thenthe range of cells in between the parentheses of the IRR command function.
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.