Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: Excel RATE() function in Java behaving a bit differently

  1. #1
    Junior Member
    Join Date
    Mar 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Excel RATE() function in Java behaving a bit differently

    Hi guys,

    Just wanted to check something. I have the following function below which is the equivalent of the RATE function in Excel

    In Excel, if I do the following in any cell: =RATE(68, -118161.59, 4146470.27) - it gives me: 2.2014989%

    However, the below code gives me: 0.0220150136..

    I am expecting it to give me 0.0022014989

    It is only marginally out, but can anyone point me why it is marginally out?

    Any help would be much appreciated

    	public static void main(String[] args) 
    	{
    		double rate1 = Financials.rate(68, 118161.59, 4146470.27);
    		System.out.println(rate1);
    		System.out.println("finished");
     
    	}
    	public static double rate(double nper, double pmt, double pv)
    	{       
    	    double error = 0.0000001; 
    	    double high =  1.00; 
    	    double low = 0.00;
     
    	    double rate = (2.0 * (nper * pmt - pv)) / (pv * nper);
     
    	    while(true) {
    	        // check for error margin
    	        double calc = Math.pow(1 + rate, nper);
    	        calc = (rate * calc) / (calc - 1.0);
    	        calc -= pmt / pv;
     
    	        if (calc > error) {
    	            // guess too high, lower the guess
    	            high = rate;
    	            rate = (high + low) / 2;
    	        } else if (calc < -error) {
    	            // guess too low, higher the guess
    	            low = rate;
    	            rate = (high + low) / 2;
    	        } else {
    	            // acceptable guess
    	            break;
    	        }
    	    }
     
    	    //System.out.println("Rate : "+rate);       
    	    return rate; 
    	}
    Last edited by capricorn86; March 23rd, 2014 at 05:48 AM.


  2. #2
    Super Moderator
    Join Date
    Jun 2013
    Location
    So. Maryland, USA
    Posts
    6,070
    My Mood
    Mellow
    Thanks
    243
    Thanked 752 Times in 738 Posts

    Default Re: Excel RATE() function in Java behaving a bit differently

    This - the second answer - is one of the best general answers to your question I found without spending too much time at it. As is mentioned there and several other places, the precision you seek may require the use of BigDecimal.

  3. #3
    Junior Member
    Join Date
    Mar 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Excel RATE() function in Java behaving a bit differently

    Hi Greg, thank you very much for the reply. But I am still a bit confused as to BigDecimal, for example:

    double rate1 = Financials.rate(68, 118161.59, 4146470.27);
    System.out.println(new BigDecimal(rate1));
    // prints 0.02201501368381107714977673595058149658143520355224609375

    In Excel, I see 0.022014989..

    But java gives 0.022015013..

    Do you think there is some miscalculation in the java rate() method above for the number being slightly out?

    I even tried changing the input parameters to the rate function to BigDecimal and it still gives me the same results.

    Hope you can help, appreciate any feedback.

  4. #4
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,816
    Thanks
    55
    Thanked 2,344 Times in 2,315 Posts

    Default Re: Excel RATE() function in Java behaving a bit differently

    Can you post the code showing the new rate() method that was rewritten to use the BigDecimal class?

    --- Update ---

    Also posted at: Excel RATE() function in Java behaving slightly differently
    If you don't understand my answer, don't ignore it, ask a question.

Similar Threads

  1. Replies: 2
    Last Post: September 27th, 2013, 05:20 AM
  2. Working differently with array elements in the same loop?
    By jean28 in forum Java Theory & Questions
    Replies: 1
    Last Post: December 9th, 2012, 11:28 AM
  3. I need a little bit of JAVA related help!
    By RufioLJ in forum The Cafe
    Replies: 4
    Last Post: September 6th, 2012, 08:33 AM
  4. Bit Strings in Java
    By razakhan in forum What's Wrong With My Code?
    Replies: 18
    Last Post: February 5th, 2012, 06:21 PM
  5. [SOLVED] If, else if, else statement not behaving as expected
    By techwiz24 in forum Loops & Control Statements
    Replies: 5
    Last Post: April 15th, 2011, 01:02 PM