Interest Due Log

[ExcelNew7]

Hi,

I have a financial management workbook which has formulas to determine the date that interest is next due. This means they change. For example, for an account with interest due on 31st Dec annually. this is the formula:

DATE(YEAR(TODAY())+IF((TODAY()>DATE(YEAR(TODAY()),12,31)),1,0),12,31)

The sheet is called "Balance Sheet". I would like to make it so when the cell in the F column displays the 'today' date, the cells A through B and P through R in the respective row as well as the date interest was added (the day it moves across) (Cell F, but the value figure not the formula as I want the date to remain fixed) is copied over to a new sheet ("Interest Due Log"). These will spawn into rows A through F on "Interest Due Log", and each new input will be put a row below.

The next day, the date interest is next due would update by a month, year, quarter etc. on the "Balance Sheet" depending which kind of account it is, but I want the row on "Interest Due Log" to remain fixed still, with the same data and inputs. This is to create a list of interest payments to check I've received them.

Column G of the sheet "Interest Due Log" will be titled 'logged?' and I want this to default to N if the cell A of the respective row is not blank.

I then want to be able to type Y in column G next to the respective row once I've logged the interest and have a date time stamp in UK time 24h appear in column H next to the respective row.

I will then manually hide them using a slicer.

The next time interest is due, the same would occur. So eventually you may have like (where , indicates new cell):
Regular Saver, Barclays, 00-00-00, 00000000, 0000, 31.12.2024, 02.01.2025 11:36
[OTHER ENTRIES]
Regular Saver, Barclays, 00-00-00, 00000000, 0000, 31.12.2025, 05.01.2026 22:43

Hopefully this makes sense, really would appreciate any help with this please!

Thank you

 

[ExcelNew7]

Does anyone know how to do this please? Thank you

 

[Asbestos_Jen]

Can you show your data layout by uploading a mini-sheet with XL2BB? Include some of your manually calculated output requirements to provide a target to compare formulas to.

 

[ExcelNew7]

EXAMPLE WORKBOOK.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Account	Provider	Interest AER	F/V	Interest term	Date Interest Due	Balance	Growth Space	Shrinking Space	Min to hold	Max to hold	Credit Allowance	Min Monthly	Max Monthly	Missed payments allowed?	Sort Code	Account No.	Reference	Customer No.	Notes	Withdrawals	Limitations	Nominated Account	Date Opened	Date closes/d	Product Type	Status
2	Apple	Grapes	45.00%	V	Monthly 17th WD	4/23/24	-$200.00	$200.00	-$400.00	-$600.00	$0.00	-$600.00	Just pay statement balance	Statement balance	Yes, but interest and bad for credit record	01-23-45	12345678	N/A	123	Cashback	Yes	Fee	Potato	2/1/24	2/1/25	Credit Card	Open
3	Banana	Hummus	6.00%	F	Annually 28th Sep	9/28/24	$1,000.00	$9,000.00	-$999.00	$1.00	$10,000.00	$0.00	$0.01	$500.00	Yes, unlimited	67-89-10	23456789	123456789	N/A	Calendar month	Yes	N/A	Cauliflower	2/4/24	N/A	Regular Saver	Open
4	Z Chocolate	Ice cream	4.00%	V	Monthly 5th	5/5/24	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$50.00	$600.00	No - account conversion	11-12-13	34567891	N/A	456	N/A	2 per month	N/A	Samosa	3/14/24	4/16/24	Easy Access	Closed
5	Dragon Fruit	Jambon	0.00%	F	N/A	N/A	$25.63	$4.37	-$5.63	$20.00	$30.00	$0.00	N/A	N/A	N/A	14-15-16	45678912	N/A	789	N/A	Yes	Penalty free closure	Cake	2/6/24	N/A	Current Account	Open
6	Egg	Ketchup	5.00%	F	Monthly 1st	5/1/24	$150.00	$50.00	-$50.00	$100.00	$200.00	$0.00	N/A	N/A	N/A	17-18-19	56789123	789123456	N/A	Interest drops in 12 months	5 per 4 years	Reduces rate	N/A	4/4/24	4/4/32	Easy Access	Opening
7	Z Figs	Lemon	10.00%	V	Quarterly LWD of Mar, Jun, Sep, Dec	6/28/24	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$100.00	$400.00	Yes - 2 per year	20-21-22	67891234	ABC456789DEF	N/A	N/A	No	Fixed account	Cheque in post	2/27/24	3/27/24	Fixed Rate Bond	Matured
8	Totals	N/A	N/A	N/A	N/A	N/A	$975.63	$9,254.37	-$1,454.63	-$479.00	$10,230.00	-$600.00	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=K2-G2
I2:I7	I2	=J2-G2
J2	J2	=L2
F2	F2	=WORKDAY(EOMONTH(TODAY(),-(WORKDAY(EOMONTH(TODAY(),-1),17)>=TODAY())),17)
F3	F3	=DATE(YEAR(TODAY())+IF((TODAY()>DATE(YEAR(TODAY()),9,28)),1,0),9,28)
F4	F4	=EDATE("2022-11-5",DATEDIF("2022-11-6",TODAY(),"M")+1)
F6	F6	=EDATE("2022-11-1",DATEDIF("2022-11-2",TODAY(),"M")+1)
F7	F7	=WORKDAY(EOMONTH(TODAY(),MOD(3-MONTH(TODAY()),3))+1,-1)
G8:L8	G8	=SUM(G2:G7)

EXAMPLE WORKBOOK.xlsx
	A	B	C	D	E	F	G
1	Account	Provider	Interest AER	Date Interest Due	Notes	Interest Logged	Date and time logged
2	Apple	Grapes	45.00%	2/23/24	Cashback	N	TIMESTAMP CALCULATED BY FORMULA BASED ON WHEN Y INPUTTED INTO INTEREST LOGGED COLUMN. IF INTEREST LOGGED COLUMN READS N, THE RELEVANT CELL IN THIS COLUMN SHOULD READ N/A
3	Apple	Grapes	45.00%	3/25/24	Cashback	N	TIMESTAMP CALCULATED BY FORMULA BASED ON WHEN Y INPUTTED INTO INTEREST LOGGED COLUMN. IF INTEREST LOGGED COLUMN READS N, THE RELEVANT CELL IN THIS COLUMN SHOULD READ N/A
4	Chocolate	Ice cream	4.00%	4/5/24	N/A	N	TIMESTAMP CALCULATED BY FORMULA BASED ON WHEN Y INPUTTED INTO INTEREST LOGGED COLUMN. IF INTEREST LOGGED COLUMN READS N, THE RELEVANT CELL IN THIS COLUMN SHOULD READ N/A
5	Figs	Lemon	10.00%	3/28/24	N/A	N	TIMESTAMP CALCULATED BY FORMULA BASED ON WHEN Y INPUTTED INTO INTEREST LOGGED COLUMN. IF INTEREST LOGGED COLUMN READS N, THE RELEVANT CELL IN THIS COLUMN SHOULD READ N/A
Sheet2

 

[ExcelNew7]

Can you show your data layout by uploading a mini-sheet with XL2BB? Include some of your manually calculated output requirements to provide a target to compare formulas to.

Hi @Asbestos_Jen ,

Thanks for your message.

I've tried to upload above - hope this is right.

The second range shows the kind of thing I'd want sheet 2 to show (by copying rows from sheet 1).

Thank you

 

[ExcelNew7]

Hi @Asbestos_Jen ,

Thanks for your message.

I've tried to upload above - hope this is right.

The second range shows the kind of thing I'd want sheet 2 to show (by copying rows from sheet 1).

Thank you

Don't necessarily use the cell references mentioned in my first post please - not sure if they are the same as the sample data I've provided so the cells may differ. Thanks

 

[ExcelNew7]

Forgot to mention:
- Please note this is sample data, and cell values may differ
- Lots of formulas are in use - for example the date interest due field is a formula and changes, but I would want sheet 2 to preserve the date
- Ideally the document would continue to carry out functions even when closed (i.e. if interest due on 19th, but document closed 15th to 23rd, ideally it'd still copy across 19th.
- My sheet 2 example is for what it'd look like if today was the date
- If the account name begins with Z then a space, I would want it to no longer copy across the rows. However if it was copied across, and then Z was subsequently added, just leave it as it is. For example on the 4th row of sheet 1, I only added Z on the 16th of April so it still copied over to sheet 2 for the 5th of April.

For some reason XL2BB has converted things into Dollars and US Date format. My spreadsheet uses £ and DD/MM/YYYY

 

[ExcelNew7]

Does anyone know how to do this please?