Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18

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

  1. #16
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    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
    Last edited by AbrahamA; May 22nd 2014 at 08:04 AM.
    Follow Math Help Forum on Facebook and Google+

  2. #17
    MHF Contributor
    Joined
    Nov 2010
    Posts
    2,217
    Thanks
    860

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

    Ok, I will take a look a bit later.
    Follow Math Help Forum on Facebook and Google+

  3. #18
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    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>
    Last edited by AbrahamA; May 24th 2014 at 06:02 AM.
    Follow Math Help Forum on Facebook and Google+

Page 2 of 2 FirstFirst 12

Similar Math Help Forum Discussions

  1. Calculator torture test in a FV problem
    Posted in the Calculators Forum
    Replies: 10
    Last Post: May 21st 2014, 06:55 AM
  2. Replies: 3
    Last Post: Feb 9th 2013, 08:47 PM
  3. Replies: 2
    Last Post: May 6th 2012, 02:16 PM
  4. Replies: 8
    Last Post: Jan 9th 2010, 04:06 AM
  5. Replies: 3
    Last Post: Oct 17th 2007, 03:41 AM

Search tags for this page

Click on a term to search for related topics.

Search Tags


/mathhelpforum @mathhelpforum