Floating-Point Error in Future Value Calculations with Many Periods

# Future Value: the functions in Excel, Matlab and Numpy Financial don't work when the number of periods is large


# Future Value: the functions in Excel, Matlab and Numpy Financial don't work when the number of periods is large












### The Future Value function and its expected behaviour

Excel's function `FV(rate, nper, pmt, pv)` calculates the future value of an investment based on periodic, constant payments and a constant interest rate.

FV should be = -pv if pmt =-pv * rate ; think of it like paying only the interest of a loan: the present value is 100, the rate is 10%, you pay 10 every period, the future value is -100 regardless of the number of periods., i.e. you have paid only the interest but not amortised a cent.

E.g.

```
FV(0.1,10,-10,100) = -100

FV(0.1,20,-10,100) = -100

FV(0.1,300,-10,100) = -100
```

### The bug in Excel

HOWEVER, if nper (number of periods) is higher than 300ish, the results don't make sense.

```
nper = 320 --> FV =-100.25
nper = 350 --> FV = -104
nper = 390 --> FV = 256
nper = 400 --> FV = 0
```

### The same bug in Python's numpy_financial

I have noticed a similar behaviour in Python's numpy financial package (see this bug report):

```
conda install -c conda-forge numpy-financial
npf.fv(0.1,200,-10,100) --> -100.0
npf.fv(0.1,300,-10,100) --> -100.03125
npf.fv(0.1,380,-10,100) --> -128.0
npf.fv(0.1,400,-10,100) --> 0
```

### The same bug in Matlab

I don't have Matlab installed, but, from the website of the documentation for the Matlab Financial Toolbox, one can test run the function fvfix to calculate the future value; that function, too, behaves oddly when the number periods > 300:

```
fvfix(0.1,400,-10,100) = 3584
```

### No idea about R's packages

I have tried to install R's FinCal package but I couldn'tget it to work - apparently I have to compile it and don't know how.

### My questions

- Why does this happen?

- Is it a known bug?

- Does it happen with most financial libraries? E.g. how about in R, Matlab, etc?

- What is the recommended solution? Are there more reliable functions / libraries in Excel and Python?

- Is there any documentation on this? I couldn't find anything, other than the Python bug report linked, but surely I cannot be the first one to have come across this? Also, in most of these packages the financial functions tend to rely on one another, so an error in the calculation of future value can affect the other financial functions, too

### What I have understood so far

These formulas calculate `(1 + rate ) ^ nper` ; I suppose the issue arises because, when nper is large, the result can exceed the maximum precision allowed by the software? E.g. 1.1^400 = 3.6e16 Excel can only store 15 significant digits.

## Answer by AKdemy (score 5)

https://quant.stackexchange.com/a/63891

This is not a bug, just how computers work. Would have been better to ask in a non finance forum though. It is called (Integer) Overflow. If you are into reading humorous chats, you can have a look here.

You can find an explanation in most documentations. Numpy is a funny one as Python does not have this issue.

I assume it is fair to say the author's did not expect anyone to use such long periods in finance. After all, in the long run we are all dead.

Edit:

I experienced a somewhat related problem while working today and therefore looked at this in more detail. The issue you have here is more subtle than I initially assumed (explained). It is not just overflow but also floating-point-math that is in your way here. In binary (or base-2), only fractions whose denominator has 2 as a prime factor can be expressed cleanly as decimals.

- for example: 1/2, 1/4, 1/8 would all be expressed cleanly

- while 1/5 or 1/10 are repeating decimals in the base-2 system the computer uses (they are decimals in our base-10 system)

I recommend reading What Every Computer Scientist Should Know About Floating-Point Arithmetic, by David Goldberg for more details.

Long story short, programming languages express the Right Division Operator `/` as floating point results.

```
julia> 0.1 + 0.2
0.30000000000000004
```

or equivalently,

```
julia> 0.1 + 0.2 == 0.3
false
```

Therefore,

```
julia> i = 0.1
for N = 20:20:500
    println(10*(BigFloat(1+i)^N-1)/i-(100*(BigFloat(1+i))^N))
end
```

looks like this:

Note that it would silently overflow without the `BigFloat` in Julia.

Probably even more importantly though, the inexact representation of rational numbers as floating point numbers becomes more and more problematic the bigger the exponent is in the above function (as can be clearly seen in the first screenshot).

Fortunately, some programming languages like Julia also offer rational number types to represent exact ratios of integers. Setting i = 1//10 we get the following (expected result):

```
julia> i = 1//10
for N = 20:20:500
    println(10*(BigFloat(1+i)^N-1)/i-(100*(BigFloat(1+i))^N))
end
```

Using Python for example will be a bit messier (to my knowledge). Sympy as well as the fractions module offers rational numbers for example. However, these will be subject to overflow and the bigfloat package requires you to install the C libraries GMP & MPFR on your system prior to pip installing bigfloat. The `mpmath` package mentioned in a comment uses binary arithmetic according to the documentation; see the section called Decimal issues. I briefly tried mixing sympy rationals and fractions within mpmath but it does not seem to prevent from overflow (I suspect because the actual fractions and sympy implementation overflows.

Last but not least, the question has a link to a numpy "bug report" where a user writes that Julia's wraparound behaviour surprises him. Using native machine integer arithmetic was a deliberate choice that allows the LLVM compiler to optimize the machine code for "basic" integer algorithms, which also means that the range of Int values is bounded and wraps around at either end so that adding, subtracting and multiplying integers can overflow or underflow (which is the way machine code works). For anyone interested, Julia's documentation provides a thorough explanation for this choice in the FAQ section.

## Answer by nbbo2 (score 4)

https://quant.stackexchange.com/a/63898

The computer programs are evaluating the following expression:

$FV(i,N,PMT,PV)=-PMT[\frac{(1+i)^N-1}{i}]-PV(1+i)^N$

the test case you are running is the special case where you choose $PMT=-i\cdot PV$

If we evaluate the formula symbolically (as opposed to numerically) we get a fortunate cancellation:

$FV(i,N,-i \cdot PV,PV)= PV(1+i)^N-PV(1+i)^N-PV=-PV$

the troublesome term $(1+i)^N$ with large $N$ disappears entirely.

But, as sometimes happens, the numerical evaluation does not follow the rules of ordinary mathematics and something goes wrong. The cancellation does not quite take place.

(Admittedly this does not add much to what was already said).

## Answer by Pythonista anonymous (score 3)

https://quant.stackexchange.com/a/63901

As @noob2 posted, al these libraries do is to apply this formula:

$FV(i,N,PMT,PV)=-PMT[\frac{(1+i)^N-1}{i}]-PV(1+i)^N$

However, the same formula can be rewritten as:

$-PMT \frac{c}{i} + \frac{PMT}{i} - PV \cdot c$ , where $c=(1+i)^N$, which can be rearranged as:

$-c \left( \frac{PMT}{i}+PV \right) + \frac{PMT}{i}$

A possible solution is to use a function formulated as above: in my specific examples of paying only the interest on a loan, the items in the parenthesis become zero, and the formula returns the correct result even if $c$ overflows.

What I am not too sure about is whether this also makes the function "less imprecise" when the parenthesis is not zero because $c$ is calculated only once, or if it is a moot point because it overflows anyway.