The Credit crunch in Numbers

More or Less BBC Radio 4 and iPlayer Subscribe to the podcast

As the downturn takes hold, BBC Radio 4's More or Less programme looks at the maths behind the credit crunch. Read original article.

Since 2007, presenter and economist Tim Harford has been exploring and explaining the numbers which have contributed to - and have characterised - the global economic downturn.

He met the mathematicians at the heart of the City - and found out why some say they are to blame for the financial crisis.

He uncovered the flaws of the bankers' bonus system, and discovered a mathematical error which might have led the banks into trouble.

He interviewed quantitative finance expert Paul Wilmott and The Financial Times journalist Gillian Tett.

And he met the guardians of what could be the financial world's most important number.

You can listen to all of Tim's reports here.

PAUL WILMOTT, QUANTITATIVE FINANCE EXPERT

Paul Wilmott is a lecturer in financial mathematics and runs the profession's most popular website.

He is a fan of quantitative finance - but he thinks that its misuse has played a part in creating the current banking crisis.

In November 2007, More or Less asked whether the financial mathematicians known as "quants" - short for quantitative analysts - were to blame for what was then being termed "the credit squeeze".

WHO ARE THE QUANTS?

Paul Wilmott discussed his concerns with Professor William Perraudin of the Tanaka Business School at Imperial College London.

Tim Harford chaired the discussion.

The risks of risk management

In December 2008, Tim invited Paul Wilmott back to talk about the problems in more detail.

Banks and hedge funds rely on highly-paid mathematicians and economists - "quants" - to evaluate risk.

So why did they not they see the credit crunch coming?

Paul Wilmott says some mathematicians have a tendency to get fixated on the numbers, failing to think about the big picture.

RISKY RISK MANAGEMENT

He posed a scenario.

Imagine you are at a magic show. The magician takes an ordinary pack of 52 playing cards, and gives it to a man in the audience to shuffle. He then asks a volunteer to think of a card. "The ace of spades," she replies.

The magician turns to the man with the pack of cards and removes a single card from the deck. What is the probability that the card is the ace of spaces?

To hear the answer listen to the interview, or read Paul Wilmott's article on the risks of risk management.

A fundamental mathematical error

Paul Wilmott says an additional cause of the credit crunch is that people simply got their sums wrong.

GETTING THE SUMS WRONG

He told More or Less these errors might have contributed to the mispricing of financial derivatives, and thus to the travails of the banks, the credit crunch, and the economic downturn.

The maths of the bonus system

Many traders were paid bonuses if they made money.

And yet, collectively, their trades bankrupted some banks and nearly bankrupted many more.

THE TRADER'S DILEMMA

Why did traders, paid for performance, all make the same mistake at the same time?

Paul Wilmott set out the trader's dilemma.

AT HOME WITH THE QUANTS

In October 2007, Tim Harford got a glimpse into the world of the quants.

The most successful of these talented mathematicians will come up with mathematical formulae that make them and their bank or hedge fund employers millions of pounds per year.

They are highly secretive about their work, not wanting others to know the details of their systems.

MEET THE QUANTS

But one of them, William Hooper, invited Tim Harford into his beautiful London home.

Read more about quantitative analysts

A trader's apology

William Hooper has since left the world of finance to start his own business.

He has been reflecting on the global economic problems and the question of who is to blame - read A trader's apology.

GILLIAN TETT, THE FINANCIAL TIMES

Gillian Tett is an assistant editor of the Financial Times and oversees the global coverage of the financial markets.

Five years ago, she was shocked to discover what she says could best be described as an iceberg in the middle of the City.

The role of the media

She was studying media coverage of the City and began to realise journalists were doing lots of stories on stocks and shares, mergers and acquisitions, but nothing on what had become a much bigger part of finance - the credit and derivative markets.

She says business journalists were simply not covering the City in a representative way.

THE FINANCIAL ICEBERG

You had a small part of the financial system bobbing above the water but a vast shadowy mass of activity pretty much hidden beneath the waves.

And hidden not just from ordinary people, but hidden from politicians, from many regulators, and unfortunately from much of the media too.

UNDERSTANDING LIBOR

The London Interbank Offered Rate - LIBOR - has been dubbed the financial world's most important number.

Published each day in the UK, it is the rate at which the banks lend to each other and it influences over $150 trillion (£100 trillion) of funds worldwide.

The Libor number is compiled by putting together the estimates of the cost of borrowing from at least eight banks, and then discarding the highest and lowest of the sample to leave an average rate which then becomes the daily 'Libor Fix'.

LIBOR

But the figure's validity is being questioned, with critics dubbing it "the rate at which banks won't lend".

Tim Harford was granted exclusive access to the operations centre where the daily rate is compiled.

More or Less is broadcast on BBC Radio 4. To find out more, visit the programme website, or subscribe to the More or Lesspodcast.

Spreadsheet skills: exorcising phantom links

As part of our new series providing solutions to common spreadsheet issues encountered by finance professionals, we look at how to locate and remove unintended links. By Liam Bastick, associate director with BPM Analytical Empowerment.

Query

Whenever I open certain Excel workbooks, I get a message asking if I want to update the links. I’m unsure how to search my workbook to find out what these links are. Please help!

Update Links Prompt (Excel 2003)

Advice

In this instance, the user wishes to find the links and decide individually whether they should be retained or removed. (If the intention is merely to remove them, you may wish to consider the free Microsoft add-in, Delete Links Wizard.)

The first step is to ascertain what type of links you have. One way of doing this is to select Edit-->Links in Excel 2003 or earlier, or use the Connections section of the Data tab (see graphic below) in Excel 2007. Also Alt + E + K, the keyboard shortcut, works in all versions of Excel.

Location of Edit Links in Excel 2007

However this command will not be available in all instances. If it is, you will probably have Formula Links. If Edit-->Links is available, a dialog box will appear.

Edit Links dialog box (illustration)

There may be more than one file linked. Upon inspection, you may notice that one or more file may simply be an older version of the active workbook. If so, the active workbook can be substituted for each file in turn by clicking on the Change Source button (Alt + N) and following the directions. This will remove these referencing errors.

You may not have access to some files and this may cause errors if the file is inadvertently updated. By selecting the Break Link button, these links can be replaced by their current values. This action cannot be undone so you may wish to save the file beforehand in order to rectify errors.

Formula links

If you do have formula links, it is relatively straightforward to search for them:

• Close all workbooks except the active workbook with the links in.
• In Excel 2003 / earlier, on the Edit menu, click Find. In Excel 2007, click on Find & Select on the Editing section of the Home tab – or use Ctrl + F in all versions.

Location of Find & Select in Excel 2007

• Click Options
• In the Find what box, enter [
• In the Within box, click Workbook
• In the Look In box, click Formulas
• Click Find All
• In the box at the bottom, look in the Formula column for formulas that contain [
• To select the cell with a link, select the row in the box at the bottom.
  

Find Dialog box (illustration)

Other ‘phantom’ links

There are other types of links - often referred to as ‘phantom’ links as they are harder to locate than formula links. But once you know, it’s easy!

Name links

This is probably the most common cause of phantom links: names that reference ranges in other workbooks.

Using Define Name in Excel 2003 or earlier or Name Manager in Excel 2007 (or Ctrl + F3), we can get a list of all the names in the workbook:

Define Name (Excel 2003 or earlier)

Name Manager (Excel 2007)

By scrolling through the list of names and examining the ‘Refers to’ section (a little cumbersome prior to Excel 2007, admittedly), names referring to other workbooks or containing erroneous references such as #REF! can be changed or deleted.

Chart links

If you have charts in your workbook, there are various places where hidden links could be lurking. Click on each text box or title and examine the formula bar, , for references to other workbooks.

Click on each data series in the chart and examine the SERIES formula for external references. These links can be removed by copying (as values!) the data located into the active workbook.

Object links

External references can also be attached to objects. The simplest way of reviewing objects in a workbook is to use the highly underrated Go To-->Special function (use the F5 function key and then click Special). In the next dialog box, select Objects, then click OK.

Go To Dialog Box Go To Special: Selecting Objects

By pressing the Tab key and examining the formula bar, each object can be reviewed in turn for external references.

And finally…

Once you have completed the above process, unless your workbook includes web queries containing parameters (this could be an article in itself), all links should now have been reviewed. If the intention was to remove all such links, simply save and reopen once all deletions have been made.

If you have a spreadsheet query, email liam.bastick@bpmglobal.com or visit the BPM website.

Back to Insight front page