0
$\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.

$\endgroup$
1
  • 1
    $\begingroup$ in Python you can just use mpmath instead of numpy, it handles numbers of arbitrary size $\endgroup$
    – Vinzent
    May 11 at 17:33
4
$\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.

$\endgroup$
2
  • $\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$ May 11 at 8:04
  • $\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 at 8:20
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).

$\endgroup$
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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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