Interest table question

[DT1962]

Hi all,

I'm an Excel novice and I need some help as I am wondering how I might go about resolving an issue with a loan repayment table I'm working on.

I set up a table for the year with all dates from Jan 1, 2023-Dec 31, 2023. Interest is calculated daily while fixed payments are made on the loan on the 1st day of every month.

I've designated each day of the year with an adjacent column determining if it's a weekend and another which tells me if it's a day where a holiday lands/is observed (i.e. if January 1st is a Saturday, the holiday would be observed Monday, January 3rd).

I was able to apply a formula to the fixed payment column to populate if it's the 1st of the month, a weekday and not a holiday. If it's the 1st, a weekend or a holiday, it leaves it blank.

What I would really like it to do is if the 1st is a Saturday, to jump up 1 cell and populate the payment there, or if the 1st is a Sunday to jump down 1 cell and populate the payment there.

I would like to be able to do this with the interest column eventually, but cross that bridge y'know?

Is there any way to do this? Thanks in advance.

 

[severynm]

It seems to me that each row would need to know the context of the rows immediately before or after it. I don't really know the full extent of your calculation rules, but in addition to checking if the current date matches the rules for a payment, you'd need to check if the day before/after matches criteria that would make the current date a payment. You could accomplish this through a series of IF statements, but it might be easier to add a few helper columns: does today require payment, does yesterday mean a payment is required today, does tomorrow mean a payment is required today. If any of those are true then you have a payment.