How does a Bank Calculate Interest on an Account?

[PNGBill]

Hi forum,

Attempting a database that calculates interest on loan balances for a year.
Consider that the account may have credit and debit entries through the year.

How would a bank calculate the interest? Mine is daily compounding.

I know there are functions that can calculate the interest for a period when it is daily compounding - I use these on an excel spreadsheet that handles the issue now. But, given the credits and debits through the year??
Does a bank do a calculation each day on the balance and store the interest to be summed quarterly ??

I thought of having each credit as a record (like an Asset Register) and then calculate the interest due as you would for an asset purchased and or sold through the year - days held.
And treat any credits similar and any debits the same except the interest would be negative.

A report would group all the records and give a sum of interest due for each group (positive interest less negative interest for each person).

Sounds messy when you consider that every record will be kept alive so to speak but the report will only show the relevant totals and the records will only grow to 100 odd max whereas our asset register can produce a report easy to read when dealing with many more records.

Appreciate some direction on this before I go off on some wild goose chase.

 

[The_Doc_Man]

Banks have many ways to do this. Mine does "average daily balance" for the month to compute the monthly interest and then does a monthly transaction that adds to the balance just like it was a normal deposit.

After that, the money that came from interest loses its identity for the purpose of the next month's interest computation. However, due to tax laws, the interest transaction remains identifiable for a summary at the end of the calendar year.

 

[gemma-the-husky]

i think the calculation would also take into account a clearing period for cheques paid in - maybe 2 or 3 days

 

[PNGBill]

Cheque clearing not an issue because our system is not a bank, just loans.

Attached is my proposed table relationship.

TblInterestRecords will record Interest for records in TblLoanDetails (Funds Rec'd) and also for records in TblLoanRepay (Funds Repaid).

Queries and reports will show the amount of Interest earned for each record of TblLoanDetails including any negative entries created via TblLoanRepay. These records still hold a LoanDetailID.
Group facility of Reports will show sum of this data by LoanDetailID and LenderID.

Should work??

 

[gemma-the-husky]

well, the calculation ought to be based on the interest rate, balance o/s and the number of days - but the loan agreement may say something else.

i think it is more likely to be daily simple, not daily compounding.

daily compounding would increase the effective interest rate by a factor approaching e (rate of natural expansion approx 2.718) - and i doubt any bank does this.

[just checked this point about compounding, and my theory about e isnt correct - i know there is something like this going on, but my conjecture isnt correct

right - just checked again, and I am close - on a balance of £100, an interest rate of 100% compounded daily gets close to £271.80 at the end of year - but a interest rate of 4% (say) doesnt produce a daily compounded interest of about £11 (ie 4 x 2.718) so I am not quite sure how to bring e into the calculation. its there somewhere!]

 

[PNGBill]

I use Effect to get the interest rate. here is an extract from excel help.

EFFECT@import url(/Office.css);EFFECT

See Also
Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.
How?
Syntax
EFFECT(nominal_rate,npery)
Nominal_rate is the nominal interest rate.
Npery is the number of compounding periods per year.
Remarks

• Npery is truncated to an integer.
• If either argument is nonnumeric, EFFECT returns the #VALUE! error value.
• If nominal_rate ≤ 0 or if npery < 1, EFFECT returns the #NUM! error value.
• EFFECT is calculated as follows:
  
  
  

Example
EFFECT(5.25%,4) equals 0.053543 or 5.3543 percent

In this case I would use 5.3543 and prorata the days the funds are held in the year.
1,000 lent for 90 days of the year @ 5.25% daily compounding = ((1,000 x 5.3543%) /365.25)*90 to get the interest to be recorded in TblInterestRecords.

Next years calculation would sum the loan plus and minus any accumulated interest, and then calculate the interest to be added to TblInterestRecords and also calculate this years Interest on any loans or repayments for this year and add these records to TblInterestRecords.

The report will show Original Loan, Prior Years Accumulated Interest and This Years Interest Activity - summed. (this is used for GL Interest entries)