Testing mathematical calculations
April 20, 2007
Over the past few months I have been working quite intensively at implementing calculation algorithms for my employer. It has mainly been about computing rates (internal rates of return, time and capital weighted return rates) on cashflows and returns on investments. The kind of things you want to know when monitoring a financial placement...
Mathematically speaking, those numbers are rather well defined. You start with a clean formula, usually directly taken from a study book on finance. Implementing those formulas may look like a piece of cake, but it is not.
So far, I have encountered 2 major issues with implementing that kind of calculations:
First: formulas have variables, or in developer terms, input data. This data has to be extracted usually from a database. So you have to define it clearly in terms of what is available from the database. That can be tricky. Take an internal rate of return. In order to approximate it, you have to define a cashflow, meaning a list of transactions where each transaction has a date and an amount. Now, how much time does a financial transaction take in the real world? It depends... Stocks can be purchased over a day. Fund shares however may require more than a day to purchase, if you use an intermediary for example. There is the glap: the formula requires a cashflow per day, but your business does not support it because transactions are spread over multiple days. You end up having to redefine your notion of transaction in order to use the given formula. That's where the ground gets slippery...
Second: formulas are tricky to test. That's probably the biggest problem for a developer. A mistake in the implementation of a mathematical formula won't cause runtime exceptions. It won't stop your code from compiling. It may even produce good looking numbers, and still be wrong.
Errors in implementations of formulas can be of 2 kind: definition errors (see the first point) or implementation errors (you wrote a - sign instead of a + somewhere. oops).
Implementation errors are quite easy to catch with a couple of technics:
And definition errors then? How do you catch them?
If you are unlucky, you can't use the first technic listed above: a second implementation of the formula will use the same wrongly defined data as your own implementation and do the same misstakes.
But you can still look for unreasonable results using the 2 other technics.
You should definitely talk with others, especially with people who are more qualified in finance or mathematics and can see your problem from a different perspective, bring you fresh ideas...
But the fact is, there seems to be no way of being sure...
Mathematically speaking, those numbers are rather well defined. You start with a clean formula, usually directly taken from a study book on finance. Implementing those formulas may look like a piece of cake, but it is not.
So far, I have encountered 2 major issues with implementing that kind of calculations:
First: formulas have variables, or in developer terms, input data. This data has to be extracted usually from a database. So you have to define it clearly in terms of what is available from the database. That can be tricky. Take an internal rate of return. In order to approximate it, you have to define a cashflow, meaning a list of transactions where each transaction has a date and an amount. Now, how much time does a financial transaction take in the real world? It depends... Stocks can be purchased over a day. Fund shares however may require more than a day to purchase, if you use an intermediary for example. There is the glap: the formula requires a cashflow per day, but your business does not support it because transactions are spread over multiple days. You end up having to redefine your notion of transaction in order to use the given formula. That's where the ground gets slippery...
Second: formulas are tricky to test. That's probably the biggest problem for a developer. A mistake in the implementation of a mathematical formula won't cause runtime exceptions. It won't stop your code from compiling. It may even produce good looking numbers, and still be wrong.
Errors in implementations of formulas can be of 2 kind: definition errors (see the first point) or implementation errors (you wrote a - sign instead of a + somewhere. oops).
Implementation errors are quite easy to catch with a couple of technics:
- Write a second, different implementation of the formula and compare the results. Better: write this other implementation in a specialised language, such as Mapple or Mathlab, or (aouch, it hurts) Excell. Even better, let someone else write this second implementation. Then take a wide population of cashflows/input data and compare the results of the 2 methods.
- Write a third implementation. And a fourth. And a fifth. And go take some anti-depressive pills.
- Define a number of heuristics to recognize calculation results that are unreasonable. For example, a rate of return should not be less than -100% or more than +1000%. Or a rate of return for a cashflow should not be positive when the return on investment on that cashflow is negative (and vice versa). Then write a small program that calculates your formula on a wide number of cashflows and looks for unreasonable results. Once you have have identified black sheeps, look at them closely and see if you caught a bug.
- Make a continuity analysis. If your result is a rate of return calculated at a given rate, it should not differ much from the same rate calculated the day before or the day after, or a week later. So take your cashflow/input data and calculate your rate on it over a 3 years period and see how it behaves. Does it jump suddenly? Is it normal? How does it behave when switching months? And on new year's day?
And definition errors then? How do you catch them?
If you are unlucky, you can't use the first technic listed above: a second implementation of the formula will use the same wrongly defined data as your own implementation and do the same misstakes.
But you can still look for unreasonable results using the 2 other technics.
You should definitely talk with others, especially with people who are more qualified in finance or mathematics and can see your problem from a different perspective, bring you fresh ideas...
But the fact is, there seems to be no way of being sure...
