Calculating Internal Rate of Return Using Excel or a Financial Calculator

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...

Memcache statistics

operation total ms total hits total misses
get 13.26 11 (91.7%) 1 (8.3%)
getMulti 254.86 18 (94.7%) 1 (5.3%)
ms operation bin key status
2.36 get cache_bootstrap esmithpass-cache_bootstrap-variables hit
0.15 get cache_bootstrap esmithpass-cache_bootstrap-bootstrap_modules hit
0.46 get cache_bootstrap esmithpass-cache_bootstrap-lookup_cache hit
3.86 get cache_bootstrap esmithpass-cache_bootstrap-system_list hit
0.89 get cache_bootstrap esmithpass-cache_bootstrap-module_implements hit
0.21 get cache_path esmithpass-cache_path-ckeditor%2Fxss miss
0.25 get cache_libraries esmithpass-cache_libraries-colorbox hit
0.63 get cache esmithpass-cache-schema%3Aruntime%3A hit
0.16 getMulti cache esmithpass-cache-.wildcard-schema%3A hit
0.15 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Afrequencies miss
0.20 get cache_views esmithpass-cache_views-ctools_export_index%3Aviews_view hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Acomments_recent hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Atracker hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Afeeds_log hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Aarchive hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Afrontpage hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Aglossary hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Alist_node_page_titles hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Abacklinks hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3ASimilar+Entries hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Apopular hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Ataxonomy_term hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Aweight hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Aadmin_views_node hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Aadmin_views_user hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Aadmin_views_taxonomy_term hit
15.90 getMulti cache_views esmithpass-cache_views-ctools_export%3Aviews_view%3Aadmin_views_comment hit
0.27 get cache esmithpass-cache-filter_formats%3Aen hit
0.44 get cache esmithpass-cache-filter_list_format hit
3.54 get cache esmithpass-cache-theme_registry%3Ainvestinganswers hit
0.15 getMulti cache esmithpass-cache-.wildcard-theme_registry hit