Calculating bank interest of a deposit.

  • Thread starter Thread starter AFB
  • Start date Start date
A

AFB

Hello,

I have a simple question, I think. I would like to
calculate the interest that a bank gives me on a deposit.
Each month they give me the correspondent interest.

I would like to know what function to use, for example
starting at 1st January with $1000 and having daily cash
flows (negatives and positives, since I would make
withdraws and deposit every day), with an interest rate of
2% (annual rate). If I didn't had cash flows i would use
the FV (future value) function, but since I have cash
flows positive and negative I don't know wich function to
use, so that I could know the interests (positive or
negative) at the 31st January.

Thank You for any help.

AFB
 
AFB said:
Hello,

I have a simple question, I think. I would like to
calculate the interest that a bank gives me on a deposit.
Each month they give me the correspondent interest.

I would like to know what function to use, for example
starting at 1st January with $1000 and having daily cash
flows (negatives and positives, since I would make
withdraws and deposit every day), with an interest rate of
2% (annual rate). If I didn't had cash flows i would use
the FV (future value) function, but since I have cash
flows positive and negative I don't know wich function to
use, so that I could know the interests (positive or
negative) at the 31st January.
Click to expand...

I will assume that the bank calculates interest daily. You need to calculate
the balance each night. Then you can calculate the interest that this earns
(for one day) by multiplying by the daily interest rate (maybe annual rate
divided by 365). Then you can add up these daily interest amounts for each
month (or however often the interest is actually paid).

Probably the easiest way of doing this is to set up three columns (credit,
debit and balance), like a bank statement, but with one row for each day.
Then your daily interest calculation can be done in a fourth column, which
can then be summed for the month/period.

Some of this can be integrated into fewer, more complicated array formulas,
but you can't get away from calculating the daily balances somehow. As you
can see, it's not just a simple one-line formula!
 
Dear Paul

Thank you for the help. I already have those colums, but
wich function should I use, so that I can find out the
interest at the end of the month.

Best regards

AFB
 
Well, if your overnight balance was in C1, the interest it earns could be
calculated in (say) D1 using
=C1*(rate/365)
This formula could be copied down column D as far as required for the month
(say as far as D31). Then simply add up these daily interests to get the
total for the month:
=SUM(D1:D31)
 
try this
DailyInterestRate = (1+YearInterestRate)^(1/366) - 1
or this
=loanamt*((1+yearlyinterstrate)^(1/365))^numofdays
 
Back
Top