Insert New Data in Order on an Existing Chart

[CAlloway]

Hello,
I have created a chart for a loan I have. It shows the future payment dates
(bi-weekly), interest paid, principal paid and outstanding balance. It
calculates it out until the loan is repaid. What I would like to do is add 2
columns for additional payments. I want one column so I can input the date of
the extra payment, and the other the amount that is applied to the principal.
The trick is that I want Excel to take the date and insert it in
chronological order with the existing dates, and then take the amount off the
principal and re-calculate the rest of the data until the loan is paid down.
Oh, and not to calculate interest on that payment, since it's principal only.
Any help you can give would be great. Thanks!

 

[Joel]

I think you want to add another series to the chart. If you roght click on
the chart in the white area outside the plt and select source data. Then
click the series tab.

 

[CAlloway]

I am confused. But I said "chart", and I actually have a spreadsheet. All I
want to be able to do is enter a date in a seperate column and then a dollar
amount in another. Then have Excel plug them into the existing data in date
order and re-calculate the balance. I'm relatively new to using Excel, but I
can't find anything on their help pages to cover this. Thanks.

 

[Joel]

I assume your are using IPMT function or equivalent.

Have four columns for each period. The Present value of loan,Principle
payment, Interest, and Extra Payment.

A B C D
E
Value of Loan Interest Princilple Extra Payment Date
$500,000 =ipmt( ) A - B 0
column C-D =ipmt( ) A - B

 

[CAlloway]

No, I had to do everything the hard way (in other words, I didn't know any
better). I probably need to do a little more research on functions so I don't
look so dumb. Column A has the date, with subsequent cells below it having
the formula (date above + 14), which works fine. Cell G2 has the interest
rate (just for somewhere to throw that value). Column B has the interest paid
per period {fomula=D2*($G$2/365)*14}. Column C has the regular payment amount
minus the interest amount in column B. Then column D has the previous balance
minus the principal paid. Now I want (we'll say column H) to put in the date
of the extra and (i.e.) column I, the amount. But then that info I want
plugged into the original info in A and D without calculating interest paid
(since it's just principal). Hope this makes what I'm doing clearer. If you
think the IPMT function will make this easier, I will try researching how to
use that. Thanks.

 

[Joel]

Consider the interest payment made at the end of tthe month, and the exttra
payment made att the beginning of the month. You need to subtract the exttra
payment from column C before tthe calculattion is made

Interest=(D2 - H2)*($G$2/365)*14
Principal = Old Principal - Extra Payment - Monthly Payment + Interest.

You can use either your formula or IPMT