Help calculating annual payments

EssKayKay

Active Member
Joined
Jan 5, 2003
Messages
457
Office Version
  1. 2007
Platform
  1. Windows
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.
 
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top