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

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

Please have a look at the current work

This is the newer version I am working on, but this will turn into a nightmare as compared to what I currently have as this allows for annuities that are themselves annuities. See the sample worksheet showing NPV calculations

http://finance.thinkanddone.com/32_b....0_en_demo.zip

100+ financial functions (see the 6 TVM functions in the function bar Fx in Excel)
I need to get rid of the iterative calculations that were shown in the first post of this thread

http://finance.thinkanddone.com/32_b....5_en_demo.zip

Online version of IRR and NPV functions

IRR calculator

NPV calculator

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

Ok, I will take a look a bit later.

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

It is not just the trouble with finding the FV - future value of a constant stream of annuity payments when interest rates drop to near zero.

There are in total 5 TVM (time value of money) functions in Excel and on popular financial calculators such as TI BA II+, HP BA II yet they will struggle to produce correct answers if any when the Feds drop the interest rate to almost zero to stimulate the economy.

Excel even had problem finding the PMT of -0.01 for this example as it returns #DIV0! error as to Excel the rate is so small that it thinks it is causing division by zero.

But the real trouble is the RATE function. Excel is usually good at finding the IRR on constant annuity payments but as it turned out Excel RATE function wasn't even able to find the interest rate for the example when interest is compounded per second and it returned #NUM! error. I tried near zero guess rates with the Excel RATE function yet nothing good turned out of the product Microsoft is so proud of having 1 billion users worldwide. I didn't even bother putting Excel through water-boarding as I knew the ugly beast was already dead on arrival and it would take a miracle to resurrect the dead to life. Judgement day, indeed!

So I edited one of my "many" versions of the tadRATE function to check whether the guidance offered by the "author" would help in determining the IRR of such an investment. The tadRATE function was taken to rooftop of the Gitmo detention center as there were no other options left but to throw them off the roof for the final episode of "The Patriot Act". And they sent me back to where I came from may be it was because I loved America a bit too much, I suppose!.

Anyway I ran the code to find RATE with various compounding frequencies of interest to find that tadRATE produced correct results for all cases even when I dropped the payments and compounding to a tune of 1 / 1,000,000,000,000,000 (count that 15 decimal places).

Here are the results

Code:
Secondly payments, secondly compounding frequency of interest
FV(i= 3.170979198376459e-9 n= 31536000 pmt= -0.01 pv= 0 type= 0)
331667.0066907767
RATE(fv= 331667.0066907767 n= 31536000 pmt= -0.01 pv= 0 type= 0)
9.999999999999753%

Milli-second payments, milli-second compounding frequency of interest
FV(i= 3.170979198376459e-12 n= 31536000000 pmt= -0.00001 pv= 0 type= 0)
331667.0072428101
RATE(fv= 331667.0072428101 n= 31536000000 pmt= -0.00001 pv= 0 type= 0)
10.00000000000019%

Micro-second payments, micro-second compounding frequency of interest
FV(i= 3.170979198376459e-15 n= 31536000000000 pmt= -1e-8 pv= 0 type= 0)
331667.0072433619
RATE(fv= 331667.0072433619 n= 31536000000000 pmt= -1e-8 pv= 0 type= 0)
10.000000000000087%

Giga-second payments, giga-second compounding frequency of interest
FV(i= 3.170979198376459e-18 n= 31536000000000000 pmt= -1.0000000000000001e-11 pv= 0 type= 0)
331667.0072433626
RATE(fv= 331667.0072433626 n= 31536000000000000 pmt= -1.0000000000000001e-11 pv= 0 type= 0)
10.000000000000036%
And here is the code

Code:
<script>
i = 0.1/31536000;
n = 31536000;
pv = 0;
pmt = -0.01;
atype = 0;

document.write("Secondly payments, secondly compounding frequency of interest");
document.write("<br/>");
fv = FV(i,n,pmt,pv,atype);
document.write("FV(i= "+i+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+fv);
document.write("<br/>");

rate = RATE( n, pmt, pv, fv, atype)*n;
document.write("RATE(fv= "+fv+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+rate*100+"%");
document.write("<br/>");
document.write("<br/>");

i = 0.1/31536000/1000;
n = 31536000*1000;
pv = 0;
pmt = -0.01/1000;
atype = 0;

document.write("Milli-second payments, milli-second compounding frequency of interest");
document.write("<br/>");
fv = FV(i,n,pmt,pv,atype);
document.write("FV(i= "+i+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+fv);
document.write("<br/>");

rate = RATE( n, pmt, pv, fv, atype)*n;
document.write("RATE(fv= "+fv+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+rate*100+"%");
document.write("<br/>");
document.write("<br/>");

i = 0.1/31536000/1000000;
n = 31536000*1000000;
pv = 0;
pmt = -0.01/1000000;
atype = 0;

document.write("Micro-second payments, micro-second compounding frequency of interest");
document.write("<br/>");
fv = FV(i,n,pmt,pv,atype);
document.write("FV(i= "+i+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+fv);
document.write("<br/>");

rate = RATE( n, pmt, pv, fv, atype)*n;
document.write("RATE(fv= "+fv+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+rate*100+"%");
document.write("<br/>");
document.write("<br/>");

i = 0.1/31536000/1000000000;
n = 31536000*1000000000;
pv = 0;
pmt = -0.01/1000000000;
atype = 0;

document.write("Giga-second payments, giga-second compounding frequency of interest");
document.write("<br/>");
fv = FV(i,n,pmt,pv,atype);
document.write("FV(i= "+i+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+fv);
document.write("<br/>");

rate = RATE( n, pmt, pv, fv, atype)*n;
document.write("RATE(fv= "+fv+" n= "+n+" pmt= "+pmt+" pv= "+pv+" type= "+atype+") <br/>"+rate*100+"%");
document.write("<br/>");
document.write("<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 future_value_of_a_single_dollar(r, n)
{
if (r==0.0)
return 1.0;
if (n==0.0)
return 1.0;
return Math.exp(n * ln1(r));
}

function future_value_of_periodic_payments_in_amount_of_single_dollar(r, n)
{
if (r==0.0)
return 1.0;
if (n==0.0)
return 0.0;
return (future_value_of_a_single_dollar(r,n)-1.0)/r;
}

function present_value_of_a_single_dollar(r, n)
{
if (r==0.0)
return 1.0;
if (n==0.0)
return 1.0;
return Math.exp(-n * ln1(r));
}

function derivative_of_present_value_of_a_single_dollar(r, n)
{
if (r==0.0)
return 0.0;
if (n==0.0)
return 0.0;
return -n*Math.exp(-(n+1)*ln1(r));
}

function present_value_of_periodic_payments_in_amount_of_single_dollar(r, n)
{
if (r==0.0)
return 1.0;
if (n==0.0)
return 0.0;
return (1.0-present_value_of_a_single_dollar(r,n))/r;
}

function derivative_of_present_value_of_periodic_payments_in_amount_of_single_dollar(r, n)
{
if (r==0.0)
return 0.0;
if (n==0.0)
return 0.0;
return ( r*-derivative_of_present_value_of_a_single_dollar(r,n) - 1.0 + present_value_of_a_single_dollar(r,n) ) / Math.pow(r,2);
}

function FV(rate, nper, pmt, pv, atype) {
atype = (typeof atype !== "undefined") ? atype : 0;
return -pv * future_value_of_a_single_dollar(rate, nper) - pmt * (1.0+rate*atype) * future_value_of_periodic_payments_in_amount_of_single_dollar(rate, nper);
}

function PV(rate, nper, pmt, fv, atype) {
atype = (typeof atype !== "undefined") ? atype : 0;
return -fv * present_value_of_a_single_dollar(rate, nper) - pmt * (1.0+rate*atype) * present_value_of_periodic_payments_in_amount_of_single_dollar(rate, nper);
}

function derivative_of_PV(rate, nper, pmt, fv, atype) {
atype = (typeof atype !== "undefined") ? atype : 0;
return -fv * derivative_of_present_value_of_a_single_dollar(rate, nper) - pmt * derivative_of_present_value_of_periodic_payments_in_amount_of_single_dollar(rate, nper);
}

function RATE(nper, pmt, pv, fv, atype)
{
eb = 0.00000000000001;
if (pv)
x0 = (-fv-pv-pmt*n)/pv/n;
else
x0 = -fv/pmt/n/n;
for (i=0; i<100; i++)
{
f = PV(x0, nper, pmt, fv, atype);
fbar = derivative_of_PV(x0, nper, pmt, fv, atype);
if (fbar == 0.0)
return null;
x = x0 - f/fbar;
if ( Math.abs((x-x0)/x) < eb )
return x;
x0 = x;
}
return null;
}
</script>

Page 2 of 2 First 12

,

,

### how to calculate biannually compounding pvifa

Click on a term to search for related topics.