Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Calculate APY in Access

Posted on 2004-08-31
2
Medium Priority
?
359 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

876 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