Spreadsheets: Excel: calculating internal rate of return (IRR)

The tutor finally brings up the elephant in the room.

I’ve been writing this blog since August 2012; herein are over 500 articles confronting problems, methods, or ideas that might occur in an academic milieu. Numerous of them centre on calculator use. Before today, however, I’ve never discussed using a spreadsheet.

My students generally don’t use spreadsheets for their math or science, except possibly for organizing lab data. Rather, like I did, they use hand-held calculators.

For the business courses I’ve taken, spreadsheets weren’t promoted; once again, hand-held calculators, such as the HP 10B, seemed to be preferred. The likely reason was that for the exam, they couldn’t provide each student a computer.

Everyday business, however, is not an exam. Only attaining the answer matters: if you can find an easier way, you’ll do well to use it. We can probably assume, in such context, that you have access to a PC with a spreadsheet program – likely Excel.

If, at your business desk, you have to solve an IRR (internal rate of return) problem, you’ll likely be drawn towards using Excel rather than a hand-held calculator.

For now, let’s exchange our lecture hall seat for an office workstation.

Back in my September 14/15 post, I describe IRR (internal rate of return) and show how to solve a sample problem using the HP 10B business calculator. The problem boils down to the following:

Find the internal rate of return for the following cash flows:
-130000, -60000, 0, 215000, -7500

Solution:

Open Excel and click a cell. Next, click the formulas tab near the top, then click Financial, which might have a green book icon. A dropdown menu will appear; scroll down to IRR, then click it.

Presently you should see a dialogue box that has IRR in the upper left corner. The cursor will likely be in an input box titled Values.

Type the cash flows list in the values box, enclosed in parentheses:

{-130000, -60000, 0, 215000, -7500}

As soon as you close the parentheses, you’ll likely see the number 0.033777697 appear in the dialogue box. After you click OK, the cell may just show 3%, but can be formatted to show more precision. (I’ll cover how to do that in another post.)

Excel hasn’t been told whether the period of each cash flow is months or years, so the answer 0.033777697 means 3.3777697% per period. In the original problem from my September 14/15 post, the cash flows happen at monthly intervals. Nominally, 3.3777697% per month leads to 40.53% per year (3.3777697%x12). Therefore, Excel gives 40.53% per year as the IRR for the given list of cash flows. That’s exactly how the HP 10B answers as well.

In future posts, I’ll no doubt be saying much more about Excel spreadsheet calculations. Furthermore, I’ll try to cover the corresponding function in a generic spreadsheet:)

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Tagged with: , , , ,

Leave a Reply