Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating daily interest expense
I have a series of loan transactions that I need to calculated the
daily interest on. Example 12/31/03 (Beg. Bal) 50,000 01/08/04 100 01/10/04 100 01/26/04 100 02/22/04 (Payment) -100 02/26/04 (Payment) -100 03/15/04 1,000 03/28/04 1,000 09/01/04 (Payment) -300 09/01/04 (note - same day - many like this) 100 12/31/04 (Payment) -3,000 12/31/04 (End Bal) 48,900 How do I calculate the interest on a daily basis? If possible I would like to use excel functions, but if not possible I need help with the macro. Thanks for the help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating daily interest expense
I assume the positive numbers are draws. Do you want to know how much
interest is accrued each day, or do you want to update the balance each time a transaction is posted. Here's one way: Assume your data starts in A5. in D5 put =C5+D4 and fill down In E6, put =D5*$A$1*(A6-A5)/365 where A1 is the interest rate and fill down. This will give you the accrued interest at each transaction date. You can sum this column to get the interest for the whole year. You can also add this interest to the balance. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com wrote: I have a series of loan transactions that I need to calculated the daily interest on. Example 12/31/03 (Beg. Bal) 50,000 01/08/04 100 01/10/04 100 01/26/04 100 02/22/04 (Payment) -100 02/26/04 (Payment) -100 03/15/04 1,000 03/28/04 1,000 09/01/04 (Payment) -300 09/01/04 (note - same day - many like this) 100 12/31/04 (Payment) -3,000 12/31/04 (End Bal) 48,900 How do I calculate the interest on a daily basis? If possible I would like to use excel functions, but if not possible I need help with the macro. Thanks for the help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating daily interest expense
Thank you! I will use that formula for the next 20 years - and I am
not kidding! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Calculating Daily interest on a moving balance? | Excel Discussion (Misc queries) | |||
daily personal expense spreadsheet in excel | Excel Discussion (Misc queries) | |||
template for monthly interest expense for invested monies | New Users to Excel | |||
daily interest | Excel Worksheet Functions | |||
How do you calculate interest expense on bonds? | Excel Discussion (Misc queries) |