Thread: Calculator torture test in a FV problem [FV function for Jonah]

1. Calculator torture test in a FV problem [FV function for Jonah]

Stumbled across this seemingly simple FV problem here.

an accountant is paid \$0.01 per second every day and night for a year. the money is deposited directly into her bank account which pays 10% interest pa compounded every second. what is the correct bank balance after one year?

Author's scicalc gets 331667.006690776891780341908435
Both my calculators are only up to 331665.995684669
Maybe I should get a ti.
Samsung phone: 331667.01312868
Excel: ?????? Sorry, no access right now.
Perhaps Sir AbrahamA, with tadRATE function, could match the author's results.
@Jonah

Due to lack of English comprehension, I dragged your original thread without realizing you were looking for a solution to finding correct TVM calculations when interest rates are too small

As you have shown the Samsung phone, Google calculator, Excel and even tadXL were displaying inaccurate results for FV calculations as interest rates go so small with secondly compounding

I have amended the code I use in my tadFV version of the Excel FV function to address the issue of loss of precision as rates get too small

The test data shows the correct value for the investment you had described

Please note that running for the test data in the browser will take a little time and you may see a message by browser to continue running the script. This is so as there are large number of iterations being carried out to find present value of 31536000 payments in amount of 0.01

As I use the long form formula (sum of series) rather than closed annuity formula because in my version of TVM functions that are a lot more variables involved that can not be represented in short form annuity formula

I hope you find the code useful

The FV function
Code:
FV(rate, gradient, nper, pmt_deferred_by, pmt, pv, atype, compounding, period, distribution, infinity)

The results
Code:
RATE = 0.1
NPER = 31536000
PMT deferred by = 0
PMT = -0.01
PV = 0
TYPE = 0
COMPOUNDING = 3.1709791983764586e-8
PERIOD = 3.1709791983764586e-8
DISTRIBUTION = 1
INFINITY = 0
FV = 331667.00669082074
The source code
Code:
<script>

rate = 0.1;
nper = 31536000;
pmt_deferred_by = 0;
pmt = -0.01;
pv = 0;
atype = 0;
compounding = 1.0/31536000;
period = 1.0/31536000;
distribution = 1;
infinity = 0;

document.write("RATE = "+ rate +"<br/>");
document.write("NPER = "+ nper +"<br/>");
document.write("PMT deferred by = "+ pmt_deferred_by +"<br/>");
document.write("PMT = "+ pmt +"<br/>");
document.write("PV = "+ pv +"<br/>");
document.write("TYPE = "+ atype +"<br/>");
document.write("COMPOUNDING = "+ compounding +"<br/>");
document.write("PERIOD = "+ period +"<br/>");
document.write("DISTRIBUTION = "+ distribution +"<br/>");
document.write("INFINITY = "+ infinity +"<br/>");

fv = FV(rate, gradient, nper, pmt_deferred_by, pmt, pv, atype, compounding, period, distribution, infinity);
document.write("FV = "+fv);

function ln1 (x)
{
var s = x;
var c = 2;
var t;
var s1;

if (Math.abs(x) >= 0.01) return Math.log(1+x);
x = -x;
t = x;
for (;;) {
t *= x;
s1 = s - t/c;
if (s1 == s) break;
c += 1;
s = s1;
}
return s1;
}

function aey(r, c)
{
if (r==0.0)
return 0.0;
if (c==0.0)
return exp(r) - 1;
else
return Math.exp(1/c * ln1(r*c)) - 1;
}

function fvif(r, n, c)
{
return Math.exp(n * ln1(aey(r,c)));
}

function pvif(r, n, c)
{
return Math.exp(-n * ln1(aey(r,c)));
}

function pvif2(r, n, c, p, d)
{
t = (n-1)*p+p*d;
return Math.exp(-t * ln1(aey(r,c)));
}

function pvifga(r, g, f, dt, atype, c, p, d,inf)
{
pv=0.0;
t=0.0;
gt=0.0;
nn=0.0;
NN=0.0;
remaining=0.0;
aif=0.0;
af1n=0.0;
af1d=0.0;
af2n=0.0;
af2d=0.0;
af1=0.0;
af2=0.0;
af=0.0;

if (inf == 0)
{
n = Math.floor(f);
for (i=0; i<n; i++)
{
if (atype == 0)
t = (i*p+d*p) + ((dt-1)*p+d*p);
else
{
if (i==0)
t = ((dt-1)*p+d*p);
else
t = ((i-1)*p+d*p) + ((dt-1)*p+d*p);
}
if (i==0)
gt = 0;
else
gt = (i-1)*p+d*p;
pv += fvif(g,gt,c) * pvif(r,t,c);
}
}
else
{
pv += Math.pow(aey(r,c)-aey(g,c),-1.0) * pvif2(r,dt,c,p,d);
}

remaining = f - Math.floor(f);

if (remaining != 0.0)
{
NN = (f-1)*p+p*d;
nn = (Math.floor(f)-1)*p+p*d;
if (r==g)
{
af1n = f * fvif(aey(r,c)*atype,p*d,1);
af1d = fvif(aey(g,c),p*d,1);
af2n = Math.floor(f) * fvif(aey(r,c)*atype,p*d,1);
af2d = fvif(aey(g,c),p*d,1);
af1 = af1n/af1d;
af2 = af2n/af2d;
af = af1 - af2;
}
else
{
aif = fvif(aey(r,c)*atype,p*d,1);
af1n = fvif(g,nn,c)*pvif(r,nn,c);
af1d = (aey(r,c)-aey(g,c));
af2n = fvif(g,NN,c)*pvif(r,NN,c);
af2d = (aey(r,c)-aey(g,c));
af1 = (aif*af1n)/af1d;
af2 = (aif*af2n)/af2d;
af = af1 - af2;
}
pv += af * pvif2(r,dt,c,p,d);
}
return pv;
}

function FV(rate, gradient, nper, pmt_deferred_by, pmt, pv, atype, compounding, period, distribution, infinity) {
atype = (typeof atype !== "undefined") ? atype : 0;
compounding = (typeof compounding !== "undefined") ? compounding : 1;
period = (typeof period !== "undefined") ? period : 1;
distribution = (typeof distribution !== "undefined") ? distribution : 1;
infinity = (typeof infinity !== "undefined") ? infinity : 0;
return (-pv - pmt * pvifga(rate, gradient, nper, pmt_deferred_by, atype, compounding, period, distribution, infinity) ) / pvif2(rate, nper, compounding, period, distribution);
}

</script>

2. Re: Calculator torture test in a FV problem [FV function for Jonah]

Let us run the FV function through more torture and attempt to find future value of an annuity that pays half a cent (0.005) per half-second for all of the year at an interest rate of 10% compounded per half-second.

I will present the closed form version of the FV function as well, even though this is really quick but it lacks all of the features of its tadFV counterpart in tadXL.

More on tadXL here at Software R Us

Here are the results for FV calculation used closed form and sum of series methods

Code:
RATE = 1.5854895991882295e-9
NPER = 63072000
PMT = -0.005
PV = 0
TYPE = 0
FV = 331667.0069670699

RATE = 0.1
NPER = 63072000
PMT deferred by = 0
PMT = -0.005
PV = 0
TYPE = 0
COMPOUNDING = 1.5854895991882293e-8
PERIOD = 1.5854895991882293e-8
DISTRIBUTION = 1
INFINITY = 0
FV = 331667.00696708745
Here is the code
Code:
        <script>

rate = 0.1/31536000*0.5;
nper = 31536000*2;
pmt = -0.005;
pv = 0;
atype = 0;

document.write("RATE = "+ rate +"<br/>");
document.write("NPER = "+ nper +"<br/>");
document.write("PMT = "+ pmt +"<br/>");
document.write("PV = "+ pv +"<br/>");
document.write("TYPE = "+ atype +"<br/>");

fv = FV(rate, nper, pmt, pv, atype);
document.write("FV = "+fv+"<br/><br/>");

function ln1 (x)
{
var s = x;
var c = 2;
var t;
var s1;

if (Math.abs(x) >= 0.01) return Math.log(1+x);
x = -x;
t = x;
for (;;) {
t *= x;
s1 = s - t/c;
if (s1 == s) break;
c += 1;
s = s1;
}
return s1;
}

function pvif(r, n)
{
return Math.exp(-n * ln1(r));
}

function pvifa(r, n)
{
if (r==0)
return 1;
if (n==0)
return 0;
return (1-pvif(r, n))/r;
}

function FV(rate, nper, pmt, pv, atype) {
atype = (typeof atype !== "undefined") ? atype : 0;
return (-pv - pmt * (1+rate*atype) * pvifa(rate, nper) ) / pvif(rate, nper);
}

</script>
May be, you want to try finding FV using milli and micro-second payments and interest compounding as well

Just a thought

3. Re: Calculator torture test in a FV problem [FV function for Jonah]

I once ran a torture cell, here are the notes from the diary I kept

First floor of Gitmo Bay

Milli-second payments using milli-second compounding of interest

Code:
RATE = 10% / 31,536,000,000
NPER = 31,536,000,000
PMT = -0.01 / 1,000
PV = 0
TYPE = 0
 Excel FV 331,671.38221946 Author FV closed form using compounded PVIFA 331,667.0072428096 Author FV closed form using FVIFA 331,667.0072428101 Author FV sum of series using compounded PVIFA System hanged

Second floor of Gitmo Bay

Micro-second payments using micro-second compounding of interest

Code:
RATE = 10% / 31,536,000,000,000
NPER = 31,536,000,000,000
PMT = -0.01 / 1,000,000
PV = 0
TYPE = 0
 Excel FV 324,820.246866631 Author FV closed form using compounded PVIFA 331,667.00724336173 Author FV closed form using FVIFA 331,667.0072433619 Author FV sum of series using compounded PVIFA Don't bother running this one

The Ultimate punishment

Top floor of Gitmo Bay

Giga-second payments using giga-second compounding of interest

This one killed Excel that returned a FV or zero whereas the author's FV functions were still on the mark

Code:
RATE = 10% / 31,536,000,000,000,000
NPER = 31,536,000,000,000
PMT = -0.01 / 1,000,000,000
PV = 0
TYPE = 0
 Excel FV 0 Author FV closed form using compounded PVIFA 331,667.00724336255 Author FV closed form using FVIFA 331,667.0072433626 Author FV sum of series using compounded PVIFA Trying this would kill your computer

4. Re: Calculator torture test in a FV problem [FV function for Jonah]

There is no reason to do a sum of series. This problem involves a geometric sum.

$0.01\sum_{n=0}^{31535999}\left(1+\dfrac{.1}{315360 00}\right)^n = 0.01\dfrac{\left(1+\dfrac{.1}{31536000}\right)^{31 536000}-1}{\left(1+\dfrac{.1}{31536000}\right)-1}$

5. Re: Calculator torture test in a FV problem [FV function for Jonah]

Originally Posted by SlipEternal
There is no reason to do a sum of series. This problem involves a geometric sum.

$0.01\sum_{n=0}^{31535999}\left(1+\dfrac{.1}{315360 00}\right)^n = 0.01\dfrac{\left(1+\dfrac{.1}{31536000}\right)^{31 536000}-1}{\left(1+\dfrac{.1}{31536000}\right)-1}$
Yes indeed you are correct that such a series has a closed form formula

The Excel FV function and the earlier version of tadFV function used such a formula to find the future value of series of periodic payments in uniform amounts.

However as I was working to extend the features in tadFV and related TVM functions in tadXL back in March 2013, I was asked for many new features in finding present value and future value of annuities

for example. There are 20 start of period payments in amount of 1 dollar where the first payment is delayed by 3.5 periods. The period cycle is every 45/360 days, the payments are concentrated on 1st quartile of the 45 day span thus making use of a 11.25/360 days discounting convention. The 1 dollar payments earn an interest of 5% whereas such payment grow by a rate of 4% every third period. The first growth occurs at the 4th period. The compounding frequency of interest is every 22.5/360 days

Now to find the present value or future value of such 20 payments using the information provided, I was unable to solve correctly for PV or FV using the closed form formula. My knowledge of my math is rather limited thus there may be a formula that could solve for PV or FV of such an investment yet I didn't and still don't know it.

But since, I knew each of the terms in the geometric progression

Code:
(1+g)^(t-1)  (1+i)^(t)
where g is the growth rate and i is the interest rate thus I was able to find the present and future value of such as investment by at first finding the sum of such terms

6. Re: Calculator torture test in a FV problem [FV function for Jonah]

Originally Posted by SlipEternal
There is no reason to do a sum of series. This problem involves a geometric sum.

$0.01\sum_{n=0}^{31535999}\left(1+\dfrac{.1}{315360 00}\right)^n = 0.01\dfrac{\left(1+\dfrac{.1}{31536000}\right)^{31 536000}-1}{\left(1+\dfrac{.1}{31536000}\right)-1}$
The Wolfram tool produces results that do not match with those produced by Author's FV calculation

331,667.00701154734

331667.21887870785

331,335.6940709375

315,360

 PMT N Author's FV Wolfram FV 0.01 31,536,000 331,667.00669082074 331,667.00701154734 0.00001 31,536,000,000 331,667.0072428096 331667.21887870785 0.00000001 31,536,000,000,000 331,667.00724336173 331,335.6940709375 0.00000000001 31,536,000,000,000,000 331,667.00724336255 315,360

7. Re: Calculator torture test in a FV problem [FV function for Jonah]

Wolfram drops precision when decimals are used. To force it to add precision, use fractions:

$0.01 \sum_{n=0}^{31535999}\left(1+\dfrac{.1}{31536000} \right)^n = \dfrac{1}{100}\sum_{n=0}^{31535999}\left(1+\dfrac{ 1}{315360000}\right)^n$

Better precision: 331667.0066907768917803419084359625614007644768385 1991...

If Wolfram spots even a single decimal point, it takes its cue to drop precision.

8. Re: Calculator torture test in a FV problem [FV function for Jonah]

Originally Posted by SlipEternal
Wolfram drops precision when decimals are used. To force it to add precision, use fractions:

$0.01 \sum_{n=0}^{31535999}\left(1+\dfrac{.1}{31536000} \right)^n = \dfrac{1}{100}\sum_{n=0}^{31535999}\left(1+\dfrac{ 1}{315360000}\right)^n$

Better precision: 331667.0066907768917803419084359625614007644768385 1991...

If Wolfram spots even a single decimal point, it takes its cue to drop precision.
Precise indeed!

The logical question that follows is how come the FV calculations in Excel or custom programming code in JavaScript or other programming languages fail to produce the results that match Author's results or those produced by Wolfram

What's going on here ?

9. Re: Calculator torture test in a FV problem [FV function for Jonah]

Excel uses a datatype called double to store most decimal numbers. The precision for that is only about 8 places. I would imagine Javascript does the same. The reason is that most operations do not require more precision than that. There are very few applications that notice an error of one thousandth of a percent. The coding behind Mathematica's (and Wolframalpha's) operations is far more complex than my comprehension. The idea is, a normal number is stored as 16 bytes of data. Mathematica has the capability to store numbers using an unbounded amount of memory (in other words, it decides how much memory to use depending on how much precision is needed). So, a single number stored as a decimal might default to a small number of bytes (as Mathematica assumes precision is already lost), but a fraction is stored as two separate numbers of any length, a numerator and a denominator. Additionally, Mathematica may not evaluate operations like square root, cube root, or any function that would require approximation until the user asks for an approximation. This way, Mathematica can attempt to simplify expressions to avoid losing precision from multiple calculations.

10. Re: Calculator torture test in a FV problem [FV function for Jonah]

Originally Posted by SlipEternal
Excel uses a datatype called double to store most decimal numbers. The precision for that is only about 8 places. I would imagine Javascript does the same. The reason is that most operations do not require more precision than that. There are very few applications that notice an error of one thousandth of a percent.
I wasn't speaking of Mathematica that would be way beyond my comprehension. All I know is to find differentials and nothing more

About mathematical calculations for engineering in programming languages such as C, Php, .net , I guess one can not be sure of the exactness of the results of mathematical calculations coded in such environments

11. Re: Calculator torture test in a FV problem [FV function for Jonah]

Another question I have that you haven't answered would help a lot

You remember the example FV calculation I listed earlier, is there any way in Mathematica for derive a closed form formula to solve such a problem because as you seen running loops over 100,000 times is not whats required.

for example. There are 20 start of period payments in amount of 1 dollar where the first payment is delayed by 3.5 periods. The period cycle is every 45/360 days, the payments are concentrated on 1st quartile of the 45 day span thus making use of a 11.25/360 days discounting convention. The 1 dollar payments earn an interest of 5% whereas such payment grow by a rate of 4% every third period. The first growth occurs at the 4th period. The compounding frequency of interest is every 22.5/360 days

12. Re: Calculator torture test in a FV problem [FV function for Jonah]

Originally Posted by AbrahamA
I wasn't speaking of Mathematica that would be way beyond my comprehension. All I know is to find differentials and nothing more

About mathematical calculations for engineering in programming languages such as C, Php, .net , I guess one can not be sure of the exactness of the results of mathematical calculations coded in such environments
There exists packages for precision mathematics for most programming languages. Using the built-in calculations will offer the built-in precision. For example, in Java, I believe it is the BigInteger and BigDecimal classes that offer additional precision. In .NET, I remember seeing something similar. In C and Php, it is more likely to be something you need to search for, and might be standard with some implementations (when C is used for programming scientific research, for instance).

13. Re: Calculator torture test in a FV problem [FV function for Jonah]

Originally Posted by AbrahamA
Another question I have that you haven't answered would help a lot

You remember the example FV calculation I listed earlier, is there any way in Mathematica for derive a closed form formula to solve such a problem because as you seen running loops over 100,000 times is not whats required.
I know only the bare minimum of finance jargon. I would need a little more information to better understand it. Can you break it down in steps? Like take me through one cycle? What does it mean that the first payment is delayed by 3.5 periods? Does that means that the account is empty for the first 157.5 days? What is a discounting convention? A little timeline would be helpful (not long, just so I can get the gist), like

Day 1: 1 dollar added to account
Day 11.25: Discount applied (and what it does to the account)
Day 22.5: Interest accrues: the account has $(1+0.05*360/22.5)$
Day 45: 1 dollar added to account and interest accrues: account has $(1+0.05*360/22.5)^2 + (1+0.05*360/22.5)$
Etc.

(If I am on the general right track, I might be able to help)

14. Re: Calculator torture test in a FV problem [FV function for Jonah]

That was just an example, let me generalize the situation

The way I set up the TVM calculations would require a number of items

1) Defining the length of the payment period

This could be any length from 1 second to a day, week, 10 days, fortnight, month, 45 days, 2 months, quarter, half-year, year, biennial etc

2) Using any compounding frequency of interest

From daily, weekly, fortnightly, 20/360 days, monthly, quarterly, semi-annually, yearly, biennial, triennial and so on

3) To allow for defining concentrations of cash flows

Usually in TVM calculation cash flows are assumed to be concentrated at the end of the year (period). But allowing for any sort of concentration of cash flows such as around the 1st quarter, mid-year, 3rd quarter, full-year, biennial, triennial. For periods of intra-year lengths concentrations around relative 1st Qtr, mid-period, 3rd Qtr, full-period and so on

If we now say that our cash flows are monthly using weekly compounding of interest where the monthly cash flows are concentrated around the 1st week

The time period for discounting of these monthly cash flows would require use of fractions such as 1/12

The concentration around the 1st week would require determining the t at which to discount the cash flow such as the 1st cash flow would be discounted at t = (1/12)(1/4) or t=1/48

The interest rate would need to be converted to an effective yield for discounting the cash flows

These are only three of the many options I have in my version of the 6 TVM functions for RATE, GRADIENT, NPER, PMT, PV and FV

The other options allow for geometric and arithmetic progression of annuity payments that may be periodic or their frequencies may be different than the payment frequencies

I hope I have been able to explain this, let me know if you want clarification on any of these options

15. Re: Calculator torture test in a FV problem [FV function for Jonah]

Having never done anything like this before, I think I am out of my depth. One thing that comes to mind, since you are using fractions for the frequency of compounding interest and for the frequency of payments, my inclination would be to find similar periods for everything. For example, suppose payments are fortnightly and interest is accrued every 20/360 days (once every 18 days). The LCM of 14 and 18 is 126. So, I would consider a full cycle to be a cycle of 126 days. The GCD of the two is 2, so I can consider steps of 2 days. Every two days, there is the potential for a payment and the potential for interest to accrue. When payments occur during the 126 days, they will occur at the same relative time during the next 126 days. Same goes for interest payments. So, whatever happens during that period of time is going to essentially provide a template for how the system will work. With some research, it might be possible to simplify some of the formulas. Then again, it might be simplest exactly how you are doing it. Sometimes looking for simpler formulas is more effort than it is worth.

Page 1 of 2 12 Last

,

,

how to calculate biannually compounding pvifa

Click on a term to search for related topics.