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

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.

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.

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?

pootle flump ur codings are working excelent.

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.

pootle flump ur codings are working excelent.

Akshully, there's an error in 7:

pootle flump ur codings are working excelent.

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

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?

returns 4.008....

pootle flump ur codings are working excelent.

Here's what I'm working with:

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)

pootle flump ur codings are working excelent.

Pootle... it returns 48.8487. However in another spreadsheet =TRUNC((1+(C18/365))^365-1,4) returns 4.00 (where C18= 3.93.

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.

pootle flump ur codings are working excelent.

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

Mate - you need to take more care with your posts then.

pootle flump ur codings are working excelent.