Calculating Interest Charges with Changing Rates

charlie442

Registered User.
Local time
Today, 06:33
Joined
Jan 14, 2011
Messages
53
Hi

I am really stuck on how to do this. I am no vba expert but I took this project on thinking how hard can it be. Unfortunately it has totally flummoxed me :(.

Essentially I have a table of historical interest rates (fields are : Interest_Rate, Rate_Start_Date and Rate_End_Date).

Then I have a query which calculates, for each payment we recieve, the payment due date, the actual payment date, the difference in months.

But now that I have this information (which I think is all I require) I am struggling to make the link. Basically what I need my database to calculate is, for all late payments what is the interest charge given that the interest rate charged may have changed since the due date of payment (the interest is calculated monthly). In other words it could be the case that for the first 3 monts one rate is charged, for the next 6 months a different rate is charged and so on, each time charging interest on previous periods interest as well as the capital amount.

Can anyone point me in the direction of an article or an example database where this is explained/done. I dont really want to waste you experts time by explaining everything and am happy to work through an example. This is a better way to learn how to do it again should I need to.

Any ideas most welcome.
Thanks
 
Hello Chalrlie,

I would pretty much appreciate if you direct me to some references for solving this issue. I have EXACTLY same query, the same table for rates and I have table with due date, payment date, and amount and next of the column of amount I need excel VBA UDF function to calculate the interest. Did you solve this and did you find some web page ore some book that deals this issue. HOW do you solve this? I desperately need solution. HUGE thanks in advance.
 
you also need to consider how frequently the interest is applied

if its not annually then the capital value may change for each interest application

I don't think you can easily do this with a query. you need a function

Code:
 function interestcalc (capital as currency, fromdate as date, todate as date) as currency
  
 look up successive interest rates starting at from date until you get to to date, and calculate the interest based on elapsed days plus rate.
  
 etc
 
Yes I need a Function,

To be completely clear, here is example.
The interest rate is changing twice in year.
01. July 2013 - 11 %.
01. Jan 2014 - 11,5 %
01.July 2014 - 10,5%
the calculated interest is sum from interest calculated in all rate periods.
Due date - 01 Sep 2013; Payment date 20 Sep 2014. ; Amount - 10.000 $;
Interest 1. (from 01.Sept 2013 to 31.Dec 2013) - rate 11 % ( 554.52 $)
Interest 2 (from 01.Jan 2014 to 30.Jun 2014) - 11,5 % (570.27$)
Interest 3 (from 01.Jan 2014 to 20.Sep 2014) - 10,5% (233.01 $) (d*r*a/365)
Interest = Interest 1 + Interest 2 + Interest 3 (1,357.8)
This is easy for one invoice :) , that's why I need function.
I want to have table where I can input rates (2 columns; Date from and Rate)
 

Users who are viewing this thread

Back
Top Bottom