I need a real pro to help with this one- Interest Calculation

[Matt]

Hi, does anyone have an idea that could help me automate the process I
describe below?

I have a spreadsheet with a couple worksheets. On the first worksheet, I
have a chart set up where I enter cash movements into or out of the
account(using either a negative or positive number). In the first column I
enter the date of the movement, and in the adjacent column I enter the amount
of the movement. I have a "total" formula at the top that sums all the
movements to give me the balance of the account. Somedays a cash movement
will occur and somedays there won't be any.

On the 2nd worksheet, I set up another chart that calculates the balance of
the account out on a daily basis which I fill out at the end of every month.
In the first column, I enter the dates for the present month end. i.e.
February 1st -28th would occupy the first column on the worksheet. On the
adjacent column I enter what the total balance was on that day. This involves
me manually going back to the 1st worksheet and finding the balance for that
particular day by summing up all of the cash movements up to and including
that particular day and entering it onto the 2nd work sheet...When the 2nd
worksheet is completed I now have a running daily balance for that particular
month.

The third worksheet I use to calculate interest. It is basically another
chart set up in a similar way to the 2nd spreadsheet, with each day for the
particular month running down the first column. In in the adjacent column I
enter what the interest rate was for that particular day. Then in the third
column, I enter a formula that pulls the balance for the particular day from
the 2nd worksheet, and multiplies it by the interest rate for that particular
day and then divides that by 100. I drag this formula down for each day of
the month, giving me the interest earned for each day during that month. I
total it at the bottom, giving me the total interest earned on the account
during the month.

My question is- is there a way to automate this process? Where I could enter
the cash movements on the first worksheet as usual, but the 2nd and third
steps I take to find the interest earned could be an automatic process? Any
help, ideas, or comments would be greatly appreciated. Please let me know if
any further explanation is needed.

 

[dlw]

you can use formulas on the 2nd sheet that reference ranges in the 1st sheet.
a sumif statement for each day would do it

 

[Matt]

Thanks for your response dlw. Could you elaborate a little? I'm exactly clear
how the sumif function would work in this case.

 

[T. Valko]

If you make this like a basic checkbook register this should be pretty easy
to do.

I'd add a balance column on sheet1 for *each* transaction, like a checkbook
register, then to get the daily balance onto sheet2 you'd do a simple lookup
on the balance column for each day of the month.

 

[Fred Smith]

I would do it in the following way.

On your second worksheet, the balance on any given day is yesterday's
balance plus any cash flows for that day. Rather than recalculating the
balance everyday, calculate the balance forward.

Suppose the balance for Jan 31st is in B1. A2 contains the date 2009-02-01.
B2 would be:
=b1+sumif(Sheet1!A:A,A2,Sheet1!B:B)

On Sheet3, I would change the way you enter interest rates. Rather than
dividing by 100, enter a percentage rate (and format it as a percentage).
Then don't divide by 100. You'll find financial calculations much easier to
do when you use percentages.

Regards,
Fred.