Archive for the ‘Spreadsheets’ Category

The efficient frontier for financial modelling

June 17, 2012

There is a trade off between the explanatory powers of a model and its complexity: the more a model explains, the more complex it will be.

Would you disagree with that?

Before you answer, let me make a claim:

Mathematics is about revealing patterns that simplify.

The mathematician’s work is actually based on producing simplicity, showing how complex problems can be reduced to component pieces that are simpler.

Think about all the beautiful proofs you’ve known. They are beautiful because they have shown how a simple and harmonious vision can achieve a target better than alternatives that will seem like a hack in comparison. Wikipedia explains it well, here.

(more…)

Making neat Excel charts (quickly)

May 9, 2012

If, like me, you spend a lot of time building spreadsheets with charts, you will want to make sure they are all lined up and neat.

This is how I do it. You can get this whole process done in seconds with a bit of practice :-).

  1. Select the range of cells that  you want your chart to cover.
  2. Name it “foo”. You can do that quickly with the keyboard shortcut Alt+i n d (so Alt+i, then ‘n’ and then ‘d’) which brings up the Name Range box.
  3. Use Alt+F11 to bring up your VBA window and select View->Immediate (or use Ctrl+G).
  4. Select the chart.
  5. Paste the following code into the immediate window:
    ActiveChart.Parent.Left = Range("foo").Left
    ActiveChart.Parent.Top = Range("foo").Top
    ActiveChart.Parent.Height = Range("foo").Height
    ActiveChart.Parent.Width = Range("foo").Width
  6. Run each line: put your cursor at the end of the first line and hit Enter to run each line by line.

Project finance funding: a simple Excel model

May 4, 2012

Financing of infrastructure projects is a hot topic at the moment.

Penny Lynch has kindly made a simple cashflow Excel model available which shows the key components (equity vs debt) of project finance funding.

It’s called Simple IRR and DCF Calculations, you’ll find it on this page here (along with lots of other helpful spreadsheets).

The power of notation in problem solving

December 13, 2011

It’s trivial when you think about it:

good mathematical notation is one way of making a problem easier to solve.

In my introduction to advanced probability theory I put emphasis on how probability theory has developed a clever and natural way to describe the processes we deal with. If you think about it some more, the design of the notation is an important part of slicing the subject into manageable blocks.

(more…)