1
$\begingroup$

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.

Improve this question
$\endgroup$
1
  • 1
    $\begingroup$ in Python you can just use mpmath instead of numpy, it handles numbers of arbitrary size $\endgroup$
    – user9413641
    May 11, 2021 at 17:33

3 Answers 3

Reset to default
5
$\begingroup$

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.

enter image description here

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: enter image description here

Note that it would silently overflow without the BigFloat in Julia. enter image description here

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

enter image description here

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.

Improve this answer
$\endgroup$
3
  • $\begingroup$ The issue arises with periods > 300. Note "periods", which doesn't necessarily mean yeas. E.g. 360 years is 30 years, and I can think of plenty of realistic, not far-fetched real-world applications with a 30-year horizon. At the very least the authors of all these packages should have documented it, especially because the average user will have no idea what integer overflow means. $\endgroup$
    – Pythonista anonymous
    May 11, 2021 at 8:04
  • 1
    $\begingroup$ The humorous link I included is exactly about documentation and where and how to explain it. I added the numpy documentation which explains the issue. It can be any period, true however, if monthly, you will not have 10% rates though. So the issue is a combination of high interest and long periods. Which realistically means years. $\endgroup$
    – AKdemy
    May 11, 2021 at 8:20
  • $\begingroup$ @Pythonista anonymous, if this answered your question please consider also accepting it or comment what's missing. $\endgroup$
    – AKdemy
    Oct 5, 2022 at 1:00
4
$\begingroup$

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).

Improve this answer
$\endgroup$
2
  • $\begingroup$ OK, but perhaps you meant to edit another answer, the one by AKdemy ;) $\endgroup$
    – nbbo2
    Dec 14, 2021 at 17:06
  • $\begingroup$ Indeed, my apology for spamming your answer. $\endgroup$
    – AKdemy
    Dec 14, 2021 at 17:15
3
$\begingroup$

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.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.