Bank Loan Database Help

[morg59jeep]

I have to setup a system to track our loans. Need it to work like a bank would handle the loans. Here is what I have so far.

Tables
LotInfo
LotID(primarykey)
LotLoanRef
LotID(primarykey)
LoanID(primarykey)
LoanInfo
LoanID(primarykey)
LoanType
Bank
LoanNumber
Interest Rate
CurrentBalance
LoanType
LoanTypeID(primarykey)
LoanType
LoanTransInfo
LoanTransID(primarykey)
TransDate
TransType
TransAmount
TransPrincipal
TransInterest
TransType
TransTypeID(primarykey)
TransType

So how do I keep track of the current balance on the loan once payments are applied. Also.. other things to note. The interest rate on the loan can change as well. Also… A loan can have more that one lot on it sometimes and a lot can have more than one loan sometimes. That is why I setup the LotLoanRef table.

There are TransTypes that need to do different things. If the Trans Type is a payment I need to calculate the interest since the last payment and deduct that from the payment and then apply the difference towards principal. I have no idea how I would do this.

 

[Remou]

Banks here calculate the interest each day. It is quite complicated.

 

[morg59jeep]

Yes they do calculate it every day... so I figure I could do something like

transactiondate - lastpaymentdate = interestdays

then do something like

interestdays x dailyrate = interest

then

paymentamount - interest = principal

then I deduct that from the current balance...

problem is I have no idea how to code that....

 

[Remou]

When I did something similar for mortgage calculations, I used Excel. I think it might be best, unless you are obliged to use Access.

 

[morg59jeep]

I need it to be intergrated into my access system.

 

[MichaelRed]

not trying to get to deep, but /the Excel financial functions DO NOT actually calculate the interest on a daily basis (this would require some looping, as well as some decision on wheather the accrued interest was included as what you are paying interest on). The Excel Help has (or at least did have ) quite complete documention on all of the functions, including the actual formulas (at the algebraic level) used. You can easily see these and determine that there is no "Daily" compounding of interest.

There are, however, several additional items to consider:

[tab]different banks -and even different loans from the same instution- may easily have varying rules on the accrual of interest.

[tab]there may (WILL) be different 'business rules' regarding the individual instution and loan. to generically replicate the activity across these you will need to know the details, as well as how to incorporate them into your app.



MichaelRed

 

[Remou]

Michael Red's comments show why I used Excel, not for the functions, but because the whole thing required constant tweaking to match the particular bank that the mortgage was with. To start with, the bank said it did not know how the calculations were done, then, after expressions of surprise and outrage, it provided a rough outline.

As an aside, at one point the expected amount did not match the bank's amount. When this was reported to the bank, it adjusted its amount, which seems to show some element of manual calculation on the bank's part.