Re: Interest calculation

On Mar 1, 10:00 pm, KeenKiwi <KeenK...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Interest is calculated on the daily closing balance
through the month and posted as a single amount the following month.
[....]
1) Where the balance or rate changes from day to day, the daily interest is
(balance * rate/360), rounded to 2 decimals.

The daily interest probably is not actually rounded. I expect only
the sum of the interest for the month to be rounded.

Note: The bank might use "banker's rounding" rules, which always
rounds a half-cent to even cents. The easiest way to implement
"banker's rounding" is to create a UDF that uses the VBA function
Round(). I forego this complexity in the solutions below.

2) Where the balance and rate are the same from day to day, the system
aggregates the balance, so the calculation is (sum of balances * rate/360) =
X.

My bank uses 365 as the denominator, not 360. (I suspect they use 366
in leap years.) I'm sure that varies from bank to bank.

There should be no difference between #1 and #2 when you eliminate the
rounding of intermediate amounts, other than the fact that #2 is more
efficient

3) X/days = Y, rounded to 2 decimals.
4) Y * days = interest.

I am not sure what you are getting at with #3 and #4, since #3 seems
to presume the answer that you say you are trying to compute, namely
the interest (X). And again, I would not round in #3, which computes
the daily interest. Instead, only the total interest for the month
should be rounded.

I have the precise balance and rate for each day (Col A = date, Col B = bal,
Col C = rate). The balance and/or rate may change daily, or be constant for
several days.

Then the interest for the month can be computed by:

=round(sumproduct(A2:A13 - A1:A12, B2:B13, C2:C13), 2)

where A1:C1 is an entry for the ending balance of the previous month,
and A2:C13 are entries for each change in balance and/or rate during
the month in ascending date order, including an entry for the ending
balance of the month. If C1:C13 contains the annual rate, change the
formula to round(sumproduct(...)/365, 2).

If the rate is constant for the entire month (the policy of some
banks), this can be simplified to:

=round(C13*sumproduct(A1:A12 - A2:A13, B2:B13), 2)

where A13:C13 is presumed to always be the ending balance and ending
daily rate (for the month). Again, change to
round(C13*sumproduct(...)/365, 2) if C13 is an annual rate.

.

Relevant Pages

  • Re: Balance sheet for a dormant company
    ... That would mean the other £40 would in the bank would simply ... even if Shares were not called for. ... there cannot be a balance where shares have not been paid for. ... I know all about Dormant Company Accounts. ...
    (uk.finance)
  • Re: Cost of Sales query!
    ... >> Banks operate one of two methods of presenting credit interest. ... >> account, ... You begin the year from scratch with an empty bank account and you ... and your Trial Balance looks like this: ...
    (uk.business.accountancy)
  • Re: Bank of America is trying to trick you into an overdraft!
    ... So your bank giving you false information about your balance and/or ... records that all known transactions have cleared and the bank balance ... account $20 for the feedback you give them. ...
    (misc.consumers)
  • Have a present.
    ... Every evening the bank deletes whatever part of the balance you failed to use during the day. ... Every morning, it credits you with 86,400 seconds. ... Each day it opens a new account for you. ...
    (uk.people.silversurfers)
  • Re: Overdraft protection can be an expensive convenience
    ... When you make a deposit, you write it in your register, and add the ... the amount from the running balance. ... Forged or altered checks and checks on which the bank misread the amount. ... Bank fees for various stuff like monthly account fee, safe deposit box, ...
    (misc.consumers)
Loading