The basic concept of internal rate of return

The first one published is reproduced below. After explaining my reason for writing the series, it shows how to calculate a basic internal rate of return and why it matters.

Monitoring investments with Excel – Part 1

I have been a spreadsheet user since around 1981 when the Manchester tax department of Arthur Andersen bought its first IBM PC. That was in the days of VisiCalc. I was self-taught; ironically about a year later I ended up teaching the Manchester tax department’s first spreadsheeting course.

Spreadsheeting skills vary. In the early 2000’s, I met one of PwC’s professional Excel model builders. Despite my having over 20 years of spreadsheet experience, I was still awed by the complexity and elegance of the model that he built for a major mutual client.

Conversely, I will never forget the member of staff who created a spreadsheet of rows and columns, and then used her calculator to add up the columns, finally typing in the column totals by hand. For her, Excel was only a word processor for numerical tables. She really did not know how to make Excel add a column of numbers!

The real moral of that story is firms should not skimp on training costs by assuming that staff can use the software the firm provides them with.

Since the mid-1990’s, I have used a spreadsheet to monitor our investments. Since starting that spreadsheet, I have slowly added more features, making it far too complex to cover in a single article. Instead, I intend to write a series of articles, each covering just one bite-sized aspect of Excel.

Is your capital result good or bad?

You bought 100 shares of ABC plc for £1,000. They are now worth £1,900. Has it been a good investment, a bad investment, or a mediocre one?

Obviously, it depends on how long you have held the shares. Assume that you have owned them for N years.

Most investors will remember the compound interest formula from their schooldays.

Future Value = Present Value x (1 + Rate of Return per Period)^{Number of Periods}

Before scientific calculators became available, calculating the N^th root of a number required either log tables or a slide rule. Excel of course has the ability to calculate N^th roots built in.

If N = 2, say, your annual rate of return of is 0.378, or expressed as a percentage 37.8%. That is extremely good.

Conversely, if N = 20, say, your annual rate of return is only 0.033, or expressed as a percentage 3.3%. Unless you have also received a reasonable rate of dividends (since the above calculations only consider capital values) you should feel pretty disappointed.

My personal assumption, based on international stock market history, is that the long-term nominal rate of return on equity investments should be about 8%.

There are two linked PDF files. The first file shows what the Excel spreadsheet should look like. The second file shows the formulas you enter into Excel to carry out those calculations. There is no substitute for learning how to enter those formulas into Excel yourself.

There is no harm in using them, but you should ensure that you know how to do the calculations from first principles. Otherwise, you are blindly reliant upon the Excel financial function getting it right, and there is always the risk of using the wrong function by accident.

The next instalment

Mohammed Amin MBE FRSA MA FCA AMCT CTA(Fellow)
Editor's note: although Amin is a member of UKSA’s Policy Team, he is writing in a personal capacity.