Use Start Date of new Entry as end Date for previous Entry

Hello

I have a spreadsheet with a Loan statement. (Please see attached sample sheet)

It has 12 Columns viz. A) Date, B) Receipts, C) Payments, D) Towards, E) Mode, F) Annual Interest Rate, G) Daily Interest Rate, H) Interest Cutoff Date, I) No of Days, J) Interest Amount, K) Closing Principal, L) Closing Interest at the time of that particular entry.

I have added a Drop down List with options to Column D .....viz. Principal Receipt, Interest, Principal Repaid to define the type of transaction it is. For example Principal Receipt is a Receipt Transaction and the Value will go under Column B) Receipts, Similarly Interest or Principal Repaid will go under Column D) Payments.

I am trying to achieve following:

1) When I am entering a new transaction and select "Principal Receipt" option from the drop down list in Column D, then the date of that transaction should be copied to the column H) Interest Cutoff date of the previous transaction with "Principal receipt" in column D.

2) the "Principal Receipt" amount of that previous entry should be added to the column K) Closing Principal of the new entry.

3) Similarly, when a "Principal Repaid" option is selected in a new entry, it should deduct the amount from "Closing principal"

This way, every time a New "Principal /receipt" entry is made, it will work out the interest on previous receipts till the date of new entry and merge the previous receipts with the new receipt to show current closing principal.

I hope this make sense.

Please help

Thanks
V

Hello JeteMc

Firstly, My sincere apologies for creating confusion with two posts. Its just that, since there were no responses to the first post for a while, I thought may be I wasnt able to explain my problem well. Therefore I tried to break it down to help me re-explain. Could you please guide me on how to copy that post into this one? Thanks

With the code you have given me, the one I pasted in K9 and down work well, but the one in H9 and down is giving me this error - #NAme?

Could you please help

Thanks
V

Thanks JeteMc. It worked brilliantly and did exactly what I wanted. Just one more request, is there a way I can clear the contents of the worksheet without loosing the formulae in various cells? May by incorporating a ActivX Command button with a code or something?

Thanks
V

Hello

I have a worksheet where I have used following code to find rows in column D that contain the word "Principal Receipt" and then paste the Date Value from Datepicker into "column H" of the same rows.

Please Login or Register  to view this content.

1) This finds ALL the rows containing the word "Principal Receipt" in D and pastes the Date in H. I am wondering, if there is a way I could only get it to paste the Date in H column of ONLY the most recent previous entry with "Capital Receipt" in D, instead of all of them. I mean ONLY in the last entry with "Capital Receipt" in D, prior to the currently entered one.

2) Also, I have inserted formulae in columns G, I, J, K & L. Is there way I can clear contents of this sheet without loosing the formula I have inserted? My current clear contents code wipes everything clean .

I have attached the sample workbook for reference.

Thanks

V

I figured it. Created a button to clear first 4 columns with a code and it clears the table ! Thanks

Hi JeteMc

While the solution you gave me is working exactly as I requested it to be, I have realised a flaw in the logic I was trying to follow.

I was using start date of the new "Principal Receipt" entry as the cutoff date for the previous "Principal Receipt" entry and adding the balance Principal amount from previous entry to the new one to calculate interest on the entire amount from the date of the new entry.

However, if a Principal Receipt Entry is made and then after sometime a Principal repaid of equivalent amount is made, then as expected it shows the Balance Principal amount as "0", but it still uses the Interest Cutoff date as either the start date of the next Principal Receipt entry or Today's date if there is no more principal receipt entries. This is affecting the interest calculations dramatically as you would expect.

For example, If there is Principal Receipt entry on 10 Feb 2019 for $2000 and Principal Re-paid entry for $2000 on 15 March 2019, then the Balance principal becomes "0" on 15 March 2019. Effectively, this should also become the Interest Cutoff date as the Balance Principal is now "0". However, it will only take the next Principal Receipt entry date (if any) as the interest cutoff date or it will take Today's date and in both cases the interest is getting over-calculated.

Is there a way we can modify the formula to take the Interest Cutoff date for the principal Receipt entry to be either the NEXT Date when the Balance Principal became "0" or the start date of the new entry with "Principal Receipt" in column D , whichever comes first.

Thanks in advance

Cheers
V

Hi JeteMc

Thanks for your response.

I tried your code / sample sheet you uploaded yesterday. The code seems to work for first two entries, but when I inserted the data for multiple entries, 3rd Principal entry onwards its taking 9 August 2016 (which is the entry date of the very first Principal Receipt entry) as cutoff date to all principal receipt entries. I am attaching the sheet for your reference.

Could you please help

Thanks
V

Thanks a million JeteMc !! It works brilliantly !! Really appreciate your help !!

Cheers
V

Hello JeteMc ........sincere apologies for a much delayed response. Had been travelling extensively. Checked your last code yesterday and it works well !! Thanks again for all your help.

Cheers
V