Aligning Dates in order with payments

MarkDShark

Board Regular
Joined
Jun 10, 2008
Messages
228
I need a formula that can list dates in order on another sheet as I enter them on the entry sheet. Im trying to create a spreadsheet that tracks payment dates plus the amount as it subtracts out the balance. Sort of like the example below. I have the first part deducting as I input, but what I want to do, is to be able to enter all the dates no matter what order they are in, but as I do, I need the 2nd spreadsheet to put them in order in a certain format, example below of the entry form, of coarse it should have the lines etc.

Date of loan: 04-15-2010 End Date: 04-15-2015
Balance $4,100.00 ($150.60)

No DATE AMOUNT
1 7/17/2013 $376.65
2 8/21/2013 $376.65
3 9/18/2013 $376.65
4 4/15/2013 $1,949.00
5 11/20/2013 $376.65
6 12/18/2013 $409.65
7 2/19/2014 $385.35

I would need the next sheet to put them in order from earliest to the latest as well as the corresponding payment starting with the "Date of loan" date and ending with the "End Date". So in this example, No4 should show first on the next sheet as I type in the dates at random on the input sheet. Outcome should be in following format below,

04152010 04152013 4,100.00 <-- (Starting with the Date of loan, then the April payment date then the balance)
04152013 07172013 2,151.00 <-- (Carrying down April date, subtracting April payment $ entered on the input sheet, reflecting new balance)
07172013 08212013 1,774.35 <-- (Carrying down next date completing process till all payments entered
08212013 09182013 1,397.70
09182013 11202013 1,021.05
11202013 12182013 644.40
12182013 02192014 234.75
02192014 04152015 -150.60 <-- (Last payment date as well as the "End date". Balance was paid 02192014.

The format is sort of in a zig zag order but needs to be for the sheet im trying to create. Hope you understand it.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
loans issuedloan status
nameissue dateamounttime yearsmonth repaynamepayments maderem payments
fred01/04/201250005166.67fred3822
bill01/05/2013100005333.33bill2535
tom12/05/2013120005400.00tom2535
ann01/04/201570005233.33ann258
do you need more information than the above 2 tables ?

<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

MarkDShark

Board Regular
Joined
Jun 10, 2008
Messages
228
Well the format I need the dates to resequence in on the 2nd sheet as I type them in on the first sheet would need to be in the below format as I posted in my original post.

04152010 04152013 4,100.00 <-- (Starting with the Date of loan, then the April payment date then the balance)
04152013 07172013 2,151.00 <-- (Carrying down April date, subtracting April payment $ entered on the input sheet, reflecting new balance)
07172013 08212013 1,774.35 <-- (Carrying down next date completing process till all payments entered
08212013 09182013 1,397.70
09182013 11202013 1,021.05
11202013 12182013 644.40
12182013 02192014 234.75
02192014 04152015 -150.60 <-- (Last payment date as well as the "End date". Balance was paid 02192014.


I would need the sheet to list them in date order just like the above format. The 4100 is the starting balance which should always be first just as it shows above, the -150.60 is whats left of the last payment that was made on 02-29-2014 that was subtracted out of the bal of 234.75. 04-15-2015 is the end date of the loan. This give me an idea of the time frames between the 2 dates as it pays down the balance etc. I also need the dates to reflect the above format for dates entered, the MMDDYYYY. No dashes or backslashes, just the numbers. I can send you the input sheet if you need to see it. Its a work in progress and is still just a frame of what it will be when done.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
I borrow 5000 at 10 % over 5 years so total repayable is 7500 = 1500 per year = 125 a month

I cannot see what you want with your post 3

or can clients under pay and over pay thus changing the timescales
 

MarkDShark

Board Regular
Joined
Jun 10, 2008
Messages
228

ADVERTISEMENT

Yes they can make any payment they want at anytime, to pay the balance down. For this example, there is no percentage added on. Its simply the balance paid down as the person makes the payment at an amount of there choosing. The format above will be used for something else by the way it populates. In full disclosure, the person decides when the balance is to be fully paid by the amounts of payments made, and when. The snap shot that I'm trying to capture in the format I require, will be cut and pasted into a program I have that determines the interest to be charged. It will take into consideration the amount of payments, the dates they were made, calculate the time frames between each payment, the date the loan was open and paid off to give a true time frame as to how long it took the person to pay it off plus other time frames. There is no set payment or date since the person is in control of how the account is paid. What I will be calculating after its paid, is the interest to actually charge from one payment to the next according to the amount of the payment made, the date it was made and how often. So if 2 payments came in 2 weeks apart, then the 14 days will be added to a running total of days to be calculated as each payment posts. So if another payment came in, 30 days later from the 2nd payment as indicated above, then 30 days would be added to the 14. The tally ends at the end date of the loan. I would calculate interest not on a full 5yrs, but on the days between the payments and the balance after they were made. The format I need is only for cut and paste purposes after everything is sequence like the example above. Sounds confusing I'm sure, but all I need is a formula that will sequence these payments as I enter them on another sheet no matter what order I enter them since there could be 100 payments coming in and it would make it easier so I don't have to manually put them in order and tally the balance each time I enter a payment. I just want to enter the date and the amount and the 2nd sheets puts them in order and Talley the new balance in the posted format for cut and pasting purposes.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
so imho far simpler to charge interest PER DAY on amount of loan outstanding - so if I paid off 100 on first of month and then 75 on the 8th of month interest from the 9th would be on amount owing minus 100 minus 75
 

MarkDShark

Board Regular
Joined
Jun 10, 2008
Messages
228

ADVERTISEMENT

Yup I agree it COULD be far simpler, but unfortunately, the program i use to enter this information manually is work related, but at least id like to create a spreadsheet to do all this so all I have to do is cut and paste the results into the program to get the end results.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
rather than cut and paste use a macro to do it - once it is fully tested there can be no human errors - good luck!
 

MarkDShark

Board Regular
Joined
Jun 10, 2008
Messages
228
Well the excel sheet would be for my personal use only. It would just be a tool to make it easier to populate the program. Reason I asked if there is a formula to do it, is because I am not very good at excel and would need to know what formula, VBA etc could do this.
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
ok - first of all if my bank transfers a payment to your bank how do you deal with it - if it is totally manual you need to find my payment record and add payment and date - at the top of my sheet there can be an amount owing "box" that will automatically update. Lets restart from there and I can help you with formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,164
Messages
5,623,109
Members
415,955
Latest member
ssheldon2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top