Solved

Calculate APY in Access

Posted on 2004-08-31
2
343 Views
Last Modified: 2009-12-16
Hello, I have an access table that has these two fiedls - TERM and RATE. To calculate the APY, currently I have to export this table to excel and run this formula:
=((1+RATE/100)^(TRUNC(TERM/12))*(1+(RATE/100*(TERM-TRUNC(TERM/12)*12))^(12/TERM)-1. Then, I have to import the excel file back into access and perform the rest of the queries.
Is there a way to perform this calculation in access? Thank you
0
Comment
Question by:lnchopo
2 Comments
 
LVL 4

Expert Comment

by:naivad
ID: 11943107
I think your problem is with the TRUNC function.

Try changing it to INT, (look up help on INT and FIX to see which function is better for you. Both drop the decimals, but handle negative numbers differently.)

=((1+RATE/100)^(INT(TERM/12))*(1+(RATE/100*(TERM-INT(TERM/12)*12))^(12/TERM)-1)
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 250 total points
ID: 11943113
You can use this expression in a query:

((1+[RATE]/100)^(Fix([TERM]/12))*([RATE]/100*([TERM]-Fix([TERM]/12)*12))^(12/[TERM])-1

In other words, you can write a query like this:
SELECT *, ((1+[RATE]/100)^(Fix([TERM]/12))*([RATE]/100*([TERM]-Fix([TERM]/12)*12))^(12/[TERM])-1 As APY FROM MyTable

This will return all of the fields in your table, plus a calculated field called APY with the results of your calculation.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question