Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Calculate APY

  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: Calculate APY

    I currently use this formula in Excel to calculate APY: =TRUNC((1+(C7/365))^365-1,4) where C7-Base Rate. I can't seem to get a converted function to work in a query (QBE). Have tried: ((1+[RATE]/100)^(Fix([TERM]/12))*([RATE]/100*([TERM]-Fix([TERM]/12)*12))^(12/[TERM])-1 however although not erroring out, it returns the wrong APY. An example of the Excel formula returns 2.75% for a term of 1 year, with a base rate of 2.72%. Ex24.00% from a base of 3.93% for a term of 10 years). Can anyone help me correct this formula?

    Larry

  2. #2
    Join Date
    Jan 2009
    Posts
    11
    That Excel formula will work the same in Access. You would just substitute the control for your cell reference. If you need to truncate the result you could use the Int or Round functions in access.

  3. #3
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks IllinoisREI for the reply;

    Here's just some of the iterations I have tried, and none have worked to date.

    Exp7: Round((((1+[Rate])/365)^365-1),4)
    Exp6: ((((([Rate]/100)/365)^365)+1)/100)
    Exp4: (1+[Rate]/365)^365-1
    Expr3: (((1+([Rate]/365))^365)-1)
    Expr2: ((1+[RATE]/365)^365)*([RATE]/365*([TERM]-Fix([TERM]/365)*12))^(365/[TERM])-1
    Expr1: ((1+[RATE]/100)^365)*([RATE]/100*([TERM]-Fix([TERM]/12)*12))^(12/[TERM])-1

    None of the above produced an error, however ALL produce the wrong result.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to tell us how they are wrong. I can write millions of expressions that would be wrong too
    I suspect Exp7 is close, but is rounding not truncating - as such it is sometimes correct, sometimes wrong. Is this true?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In fact Larry, could you tell us the result of the Excel formula given a particular value for C7? I get different to what you seem to be saying.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Akshully, there's an error in 7:
    Code:
    Round(((1+([Rate])/365)^365-1),4)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2009
    Posts
    11
    Larry,

    Just to check the math, I put "=(1+(0.0272/365))^365-1" in a text box and got 0.0275722555006532. Should be close enough right?

    Kevin

  8. #8
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    1.25 should return 1.25
    1.99 should return 2.00
    2.72 should return 2.75
    2.96 should retund 3.00
    3.20 should return 3.25
    3.40 should return 3.45
    3.73 should return 3.80
    3.93 should return 4.00

    When I enter Exp8: (1+([Rate]/365))^365-1 into QBE field it returns 51.3764625308631 on a value [Rate] of 3.93. What am I doing wrong?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT TOP 1 (1+(0.0393/365))^365-1 AS test
    FROM tester;
    returns 4.008....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Here's what I'm working with:
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Larry - please confirm:
    your copy of the database returns 51 for 3.93? You are certain? Coz it doesn't for me.

    Copy and paste this into Excel - what does it return?
    =TRUNC((1+(3.93/365))^365-1,4)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Pootle... it returns 48.8487. However in another spreadsheet =TRUNC((1+(C18/365))^365-1,4) returns 4.00 (where C18= 3.93.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    c18 won't equal that. I'll bet it equals 0.0393 and is formatted to look like 3.93. Double click the cell and see what the value actually is.

    Can you confirm that the database you posted definitely returns 51 because it returns 48.8487 for me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Pootle: The database I sent you returns 48.8487 on my system. Also, C18 on the working spreadsheet is formatted for Percent, so it does display the number in 3.93 (4.00) format.

    Thanks...

    Larry

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Larryg
    When I enter Exp8: (1+([Rate]/365))^365-1 into QBE field it returns 51.3764625308631 on a value [Rate] of 3.93. What am I doing wrong?
    Quote Originally Posted by Larryg
    Pootle: The database I sent you returns 48.8487 on my system. Also, C18 on the working spreadsheet is formatted for Percent, so it does display the number in 3.93 (4.00) format.
    Mate - you need to take more care with your posts then.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •