If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > Calculate APY

02-06-09, 07:26
 Larryg Registered User Join Date: Aug 2005 Location: Delaware Posts: 135
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
02-08-09, 23:44
 IllinoisREI Registered User Join Date: Jan 2009 Posts: 10
 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.
02-09-09, 07:31
 Larryg Registered User Join Date: Aug 2005 Location: Delaware Posts: 135
 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.
02-09-09, 07:45
 pootle flump King of Understatement Join Date: Feb 2004 Location: One Flump in One Place Posts: 14,907
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:
Quote:
 pootle flump ur codings are working excelent.
02-09-09, 07:50
 pootle flump King of Understatement Join Date: Feb 2004 Location: One Flump in One Place Posts: 14,907
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:
Quote:
 pootle flump ur codings are working excelent.
02-09-09, 07:56
 pootle flump King of Understatement Join Date: Feb 2004 Location: One Flump in One Place Posts: 14,907
Akshully, there's an error in 7:
Code:
`Round(((1+([Rate])/365)^365-1),4)`
__________________
Testimonial:
Quote:
 pootle flump ur codings are working excelent.
02-09-09, 08:57
 IllinoisREI Registered User Join Date: Jan 2009 Posts: 10
 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
02-09-09, 09:40
 Larryg Registered User Join Date: Aug 2005 Location: Delaware Posts: 135
 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?
02-09-09, 10:12
 pootle flump King of Understatement Join Date: Feb 2004 Location: One Flump in One Place Posts: 14,907
Code:
```SELECT TOP 1 (1+(0.0393/365))^365-1 AS test
FROM tester;```
returns 4.008....
__________________
Testimonial:
Quote:
 pootle flump ur codings are working excelent.
02-09-09, 10:43
 Larryg Registered User Join Date: Aug 2005 Location: Delaware Posts: 135
Here's what I'm working with:
Attached Files
 APY.zip (10.4 KB, 13 views)
02-09-09, 11:05
 pootle flump King of Understatement Join Date: Feb 2004 Location: One Flump in One Place Posts: 14,907
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:
Quote:
 pootle flump ur codings are working excelent.
02-09-09, 11:25
 Larryg Registered User Join Date: Aug 2005 Location: Delaware Posts: 135
 Pootle... it returns 48.8487. However in another spreadsheet =TRUNC((1+(C18/365))^365-1,4) returns 4.00 (where C18= 3.93.
02-09-09, 11:34
 pootle flump King of Understatement Join Date: Feb 2004 Location: One Flump in One Place Posts: 14,907
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:
Quote:
 pootle flump ur codings are working excelent.
02-09-09, 13:04
 Larryg Registered User Join Date: Aug 2005 Location: Delaware Posts: 135
 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
02-10-09, 03:09
 pootle flump King of Understatement Join Date: Feb 2004 Location: One Flump in One Place Posts: 14,907
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:
Quote:
 pootle flump ur codings are working excelent.