1. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139

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. Registered User
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. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139

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. King of Understatement
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?

5. King of Understatement
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.

6. King of Understatement
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)`

7. Registered User
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. Registered User
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. King of Understatement
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....

10. Registered User
Join Date
Aug 2005
Location
Delaware
Posts
139
Here's what I'm working with:

11. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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)

12. Registered User
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. King of Understatement
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.

14. Registered User
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. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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?
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.

#### Posting Permissions

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