Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Data in Order on an Existing Chart
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Data in Order on an Existing Chart
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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Data in Order on an Existing Chart
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" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Data in Order on an Existing Chart
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" wrote: 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" wrote: 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" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Data in Order on an Existing Chart
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Data in Order on an Existing Chart
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 "CAlloway" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a chart and data to existing worksheet | Charts and Charting in Excel | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) | |||
Add source data to an existing chart? | Charts and Charting in Excel | |||
How To: Link worksheet data to pre-existing chart tables | Excel Discussion (Misc queries) | |||
How To: Link worksheet data to pre-existing chart tables | Charts and Charting in Excel |