The Future Value function and its expected behaviour
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.
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
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.
- 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.