I ran into a problem I cannot figure out. I have a formula in Range(S33:S133) that returns the value of a cell ($F$18) times the number of payments remaining to be paid out in any remaining year under contract; each row corresponding to the next consecutive year (total 100 years). However, it’s not working as expected.
Let’s start with the assumption payments will be monthly although that could change to semi-monthly or bi-weekly. The due dates of the payments are noted in column C starting in cell C33. Each row from there on will include the next payment due date. There could be over 1000 payments/rows.
As example, the value in F18=500, the contract is for five years (60 months), and begins in October of 2025. The formula returning the annual payment will be in column S (S33:S133). Column R contains the corresponding year.
2025: 3 x 500 = 1500
2026: 12 x 500 = 6000
2027: 12 x 500 = 6000
2028: 12 x 500 = 6000
2029: 12 x 500 = 6000
2030: 9 x 500 = 4500
Any suggestions would be appreciated.
Thanks for viewing,
Steve K.
Let’s start with the assumption payments will be monthly although that could change to semi-monthly or bi-weekly. The due dates of the payments are noted in column C starting in cell C33. Each row from there on will include the next payment due date. There could be over 1000 payments/rows.
As example, the value in F18=500, the contract is for five years (60 months), and begins in October of 2025. The formula returning the annual payment will be in column S (S33:S133). Column R contains the corresponding year.
2025: 3 x 500 = 1500
2026: 12 x 500 = 6000
2027: 12 x 500 = 6000
2028: 12 x 500 = 6000
2029: 12 x 500 = 6000
2030: 9 x 500 = 4500
Any suggestions would be appreciated.
Thanks for viewing,
Steve K.