Document to show payments and interest

[npereira]

Hi,

i have been racking my brains for several days trying to figure this out to no avail.

Here is what i want to do:

have a line of credit with 8000$ on it that I owe. On the same line of credit, there is a 10000$ portion that I pay 50/50 with the wife.
So we need to have an excel sheet that lays out how much each portion is devided and what is left to pay, on the same sheet.
Both amount are on the same line of credit at 4.15%.
Each portion will be deducted each time we make a payment. for example:
When I make a payment on my 8000$, then what is the balance left with interest.
Same, for the 10000$ portion, when we make a payment, what is the amount left to pay with interest.

This way we each pay our how portion with interest.

Can anyone help us figure this out? This could be on the same excel sheet as seperate columns since they have to be calculated seperatly and each row would be the date we paid, the amount and then show the balance with interest left to pay.

[Pauleyb]

Not enough information (which may be why you are pulling your hair out).

Is this calculated monthly? So, 0.3458333% each month in interest to the outstanding balance?
When do you exit your draw period and into your repayment period? What are those terms?
What is the minimum payment?
Do you plan to contribute a set amount each month or will it vary? I'm assuming this is variable, which is why you are running into problems. If this is the case, there is no specific date since you could pay it all off tomorrow or stretch it out for years just paying the minimum payment. Thus, the amount left to pay with interest will always be dependent upon how much you contribute, when, and how long you expect to make payments.

The easy solution is to determine how much you will consistently pay each month. Say $400. So, each month you pay 72.222% (or $288.89) and your wife pays 27.777% (or $111.11). Then you would use NPER to determine how many periods it will take to pay it off. For example:
=NPER(0.0415/12,400,-18000,0,0)
This is assuming monthly payments. It shows it will take 49 periods (months, so 4 years and 1 month) to pay it off with that payment schedule. That last variable (the 0) your interest is accrued at the end of the period. Change it to a 1 if it accrues at the beginning of a period. It should not make of a difference.