Excel Finance Trick 9: Daily Interest But Monthly Deposits?!

Hi, I am having a little difficulty determing the simplest formula to use for calculating my bank's savings interest. Here are the details:

1. Interest Rate is 00.20%
2. Interest is paid on 19th of month usually, but sometimes on 18th and 21st
3. Best example I could find: DaBank:
a. Since DaBank compounds daily, you are getting 5%/365 = .0137% every day. So, at the end of the first day, you will have 10,000 x 1.000137 = $10001.37. Using the same basic formula as above for 365 days:
b. $10,000 x (1 + .05/365)365 = $10,512.67.
i. FV=P(1+r/n)^n
c. Lacks: the normal process of making deposits and withdrawals.
4. Also, all the formulas immediately want to give you a future value result. However, I would like to know the the actual amount of interest paid out at the end of each month cycle which is the entire point of understanding interest rates, right?
5. If possible can you post the example in a spreadsheet online or email it to me at HiteshPatel30@gmail.com.

Thanks, I know it's not a lot but just trying to stay on top of the process.

Hi-

Does anyone know how to calculate a "draw down loan?" That is, I have a line of credit with a lender for a real estate construction. Every week or so various checks in various amounts are written to pay contractors/workers, etc. The interest starts to accrue the day the check is made and is based on a 6% annual rate. None of the money will be paid back until the project is completed--there are no payments made until the end.

I need to know how to calculate the accruing daily interest for each check written. So essentially, when I open Excel, each day the interest will change/increase. So If I draw money today for $100. The interest will change and increase each day/date (non-compounding) for that $100.

My columns a

Purpose/Use, Payee, Date, Check Amount, Interest Accrued To Date, Total Amount (Princple+interest).

How do you do this formula?

Thanks in advance for your help!!!

Steven
LA, Ca

Hi all. Happy new year to every one!!

I have a spread sheet that works fine for what i need, however i thought i might post on here to see if anyone has some better ideas as to how to do this.

What i have is a series of loan accounts that, as the title suggests, need interest calculated daily on totals and then credit monthly.

I have a simple spreadsheet that looks a little like this, but for every day of the year.


Code:

        
 
Date
 
Amount
 
Interest
 
Credited
 
                                                     
   
29/07/2009
 
36162.4
 
   6.94

 


   
30/07/2009
 
36162.4
 
   6.94
 


   
31/07/2009
 
36162.4
 
   6.94
 
214.99
   
1/08/2009
 
36377.39
 
   6.98
 


   
2/08/2009
 
36377.39
 
   6.98
 


  

The amounts in the total column may need to be updated manually when transaction occur within the account.

The formulas are as follows

Code:

        
 
Date
 
Amount
 
Interest
 
Credited
 

                                                    
   
40023
 
36162.4
 
=SUM(B30*7%/365)
 


   
40024
 
36162.4
 
=SUM(B31*7%/365)
 


   
40025
 
36162.4
 
=SUM(B32*7%/365)
 
     =SUM(C2:C32)
   
40026
 
=SUM(B32+D32)
 
=SUM(B33*7%/365)
 


   
40027
 
36377.39
 
=SUM(B34*7%/365)
 


  

If you need anything else, please let me know.

I have to undertake a number of financial projections based on an actual annual interest rate where interest is added either daily or weekly. If I have an actual annual interest rate of 5% and divide it by 12 and then compound that figure I get an actual annual interest figure of 5.1162%.

I am therefore looking for a formula that will do this where the only information required is the actual annual interest rate and the number of compounding periods.

Any help would be appreciated.

Thanks in anticipation

Francis

Hi All,

I have a spreadsheet that currently lists all of my clients(by account number) and all of their deposits made to my bank for the past 2 years. However, the clients are listed everytime they made a deposit, so if you can imagine, I have in column A1 many duplicated account numbers. (see attached)

My goal is to use this dataset and create a new worksheet that lists only each account number once in column A1, and then list all the months from Jan '07 to Dec 08 from column B1 to Y1. If account number 518500(See attached example) made a deposit in 11/07 for $329, 12/07 for $360, 12/07 for $50 , 12/07 for $600 (3 deposits in December), and so on, how do I get excel to plot the total deposits per month onto the new worksheet with the month/year listed from B1 to Y1? I'm okay with blanks in the months a certain client didn't make a deposit

Of course, as much automation as possible is ideal because I have over 13000 entries!

Thank You very much!

Frank

Can anyone tell me which financial functions to use to solve this/methods I can use to set this up in Excel?

I have an initial deposit of 1k, bank pays 5% annual interest compounded monthly. I want to leave it in there for 30 years. How do I know how much I should deposit to make one million dollars in 30 years?

Hi All,

I would like to be able to check senarios of the problem.
There is a Capital amount invested with an interest rate of 'x' annually, with a fixed monthly withdrawal - how long would this investment last? - that's the easy part.
Now, if there are periodic amounts (not fixed in amount for frequency) paid in as well as the odd monthly fixed amount left in (not withdrawn for that month), what would the new term be?

Hopefully there are a lot more clever people out there than me.
Many thanks in advance for your assistance.

Regards,
Chubby16

I have a spreadsheet of deposits made on a saving account. I need to compute the daily interest at the various interest rates, the same way the banks would compute the interest, which I think is compounded daily. It would be appreciated if someone could assist

Regards

Howard

******** ******************** ************************************************************************> Microsoft Excel - Bank Interest.xls ___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout I8 I9 =
A B C D E F G H I 1                   2                 3                   4                   5                   6             Interest Rate table     7 DATE Advances Interest Cumulative Interest Int & Advances   Date Int Rate Max Period 8 18/08/2005 R 65,000.00         15/04/2004 9.00%           784  9 02/12/2006 R 25,000.00         08/06/2006 9.50%           189  10 21/01/2007 R 18,200.00         14/12/2006 10.00%   Sheet1  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment - which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?