Solved

Doing currency Conversion and calculating Accrued Interest in a query

Posted on 2007-03-26
4
448 Views
Last Modified: 2012-05-05
I do collection work for creditors in the UK who have clients in the US.
I have a database where the debtor has been invoiced in £GBP which I convert to $USD on the day of invoicing.  When we receive payments from the debtor I need to convert it to £GBP based on the conversion rate that day.  In addition I have to track interest due on the account.  Presently the currency conversion and interest calculation is being done in an Excel spreadsheet and then entered back in Access.  

Is there a way to design a query that will do an accrued interest calculation which is based on the intrerest rate, date of last payment to the date of current payment
0
Comment
Question by:ukbusiness
  • 3
4 Comments
 
LVL 9

Expert Comment

by:TheSloath
ID: 18791789
You want to calculate the interest compounded on a daily basis?

If not, it is just a wee DateDiff("d", [LastDate], [ThisDate] to calculate the number of days (+ 1 if inclusive of the first day) and multiply this my the Daily interest rate (AnnualRate/365.25) and the Amount... I think...
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 18791795
I'm sure you could build a routine which did this, or perhaps even in a query. How exactly do you calculate this now? I'd assume something like:

(Amount Originally Owed) - (Payments Made) + Interest Charged

However compounding interest can be pretty complex, depending on the specific rules of your government and/or company or customers. If the Excel functions give you correct results, then you _could_ make a reference to Excel and use the formulas there to do the work for you.
0
 
LVL 9

Expert Comment

by:TheSloath
ID: 18791816
You want to calculate the interest compounded on a daily basis?

If not, it is just a wee DateDiff("d", [LastDate], [ThisDate] to calculate the number of days (+ 1 if inclusive of the first day) and multiply this my the Daily interest rate (AnnualRate/365.25) and the Amount... I think...
0
 
LVL 9

Expert Comment

by:TheSloath
ID: 18791848
If you wanted it compounded by the day, then I think the field would be:


[OrginalAmount] + ([OriginalAmount] * ((1 + [DailyInterestRate]) ^ DateDiff("d", [DateFrom], [DateTo]))


(^ is 'ToThePowerOf')
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

730 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