"Line of Credit" template - how to?

Hi everyone,

I have a small business and am setting up a line of credit with a family member. The line of credit has the following requirements:

Interest only is due on the outstanding principal the first of each month.
The rate is adjusted to Prime+1% on the first of each month.
I can make payments on principal at any time, thereby reducing the interest owed.
I can borrow more at any time.

So, I *think* what I need is a worksheet that would allow me to make deposits and withdrawals by entering a date and a debit/credit amount - and then also to calculate the interest due on a given date (the first of each month), and to be able to adjust the interest rate (and have it affect only the future, not the past, calculations).

Does such a template exist? I sure can't find it for all the googling I've done without buying a whole template package...

Any help is appreciated...

Glenn

Hi GlennJ. The following should work
A B C D E
1 Date Debit/Credit Balance Interest Rate Interest Amount
2 2/1/06 $1,000 $1,000 4.1% $ 41.00
3 3/1/06 -500 500 4.2 21.00
4 4/1/06 2,000 2,500 4.0 100.00

Assuming that you borrow or pay principal on the first of the month, to be
completely accurate, you would not start paying interest until the first of
the next month, in this example that would be March 1st; D2 and E2 would be
blank. The calculation in E3 would be based on the Interest Rate shown in
D2, or 4.1%. I think that Interest Amounts should lag one month from the
Debit/Credit. Hope this makes sense.

Sincerely, Michael Colvin


"GlennJ" wrote:

>
> Hi everyone,
>
> I have a small business and am setting up a line of credit with a
> family member. The line of credit has the following requirements:
>
> Interest only is due on the outstanding principal the first of each
> month.
> The rate is adjusted to Prime+1% on the first of each month.
> I can make payments on principal at any time, thereby reducing the
> interest owed.
> I can borrow more at any time.
>
> So, I *think* what I need is a worksheet that would allow me to make
> deposits and withdrawals by entering a date and a debit/credit amount -
> and then also to calculate the interest due on a given date (the first
> of each month), and to be able to adjust the interest rate (and have it
> affect only the future, not the past, calculations).
>
> Does such a template exist? I sure can't find it for all the googling
> I've done without buying a whole template package...
>
> Any help is appreciated...
>
> Glenn
>
>
> --
> GlennJ
> ------------------------------------------------------------------------
> GlennJ's Profile: http://www.excelforum.com/member.php...o&userid=32008
> View this thread: http://www.excelforum.com/showthread...hreadid=517421
>
>