Help calculating annual payments

[EssKayKay]

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.

 

[EssKayKay]

I think I thought of another way to do this but again, I will need some assistance. Column C contains dates (mm/dd/yyyy). Is there a way to count how many entries there are in a range for each corresponding year?

That is in range C33:C500 return a value of how many entries there are for the year of each date. There could be 100 years but more likely limited to 30.

Column R (R33:R133) contains the years. In the case noted above it would be 2025 – 2125 (R33:R133), although we would only be interested in the first five years (i.e., rows 33:38).

Therefore, is it possible to “count” how many entries in a range (say C33:C500) have a year = 2025, year=2026, year=2027 . . . Year=2030? Again, in the case above as dates are only entered monthly, it would be:

2025 = 3 (Oct-Dec)
2026 = 12 (Jan-Dec)
2027 = 12 (Jan-Dec)
2028 = 12 (Jan-Dec)
2029 = 12 (Jan-Dec)
2030 = 9 (Jan-Sep)


Again, thanks for viewing,
SKK

 

[EssKayKay]

I thought I found my answer where "R33" contains the year I wish to count -

=SUMPRODUCT(--(YEAR($C$33:$C$2000)=R33))

However, I found another problem where if the value in another cell is greater than some number I receive a #Value!. What does this mean?

 

[Joe4]

So much is dependent on your data structure. It would be very beneficial if you could show us exactly what your data looks like, and your expected results.
You can either use the tool below, or show us some screen prints.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[EssKayKay]

So much is dependent on your data structure. It would be very beneficial if you could show us exactly what your data looks like, and your expected results.
You can either use the tool below, or show us some screen prints.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you so very much Joe. I was not aware of this. I'm working on something else (related to this). If/when I need to so, I will most certainly try the XL2BB Add-In.

I'll keep you posted,
Steve