lease showing payments for each year for years/month payment is applicable

D

Darlene C

New Member
Joined
Feb 13, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am trying to set up a spreadsheet that will calculate payments for each year (depending on the lease start & end date). I was trying to use the IFS formula to calculate if that item is within that lease date time frame.
So for the Titan utility box: IF (E2) 2019 >= (G2)2018 calculate payments for that year. In this case 2018 wouldn't have a payment because the lease starts in 2019. There would be payments in 2019 (but only from September to December), payments in 2020, payments in 2021, payments in 2022, payments in 2023, and payments only until September for 2024.
Then I will total the payments at the bottom of each year to project for future purchases.

1644797378816.png
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Hi,

You have inconsistencies in your data sample. See my notes below.

Also, please look into posting sample using XL2BB, see my signature, or at least post in table format, Not a picture, so helpers can copy your data for testing.

StartEnd
9/1/20199/1/2024<Is End month inclusive? If so, lease is 5 year 1 month, If not, End should be 8/31/2024
4/1/20215/1/2027<Same as above, lease is 5 year 2 month if End is inclusive
5/1/20214/30/2027<This seems correct
6/24/20207/1/2024<What about day proration?
10/31/201811/1/2024<End month inclusive? Day proration?
 
Upvote 0
Darlene C said:
I am trying to set up a spreadsheet that will calculate payments for each year (depending on the lease start & end date). I was trying to use the IFS formula to calculate if that item is within that lease date time frame.
So for the Titan utility box: IF (E2) 2019 >= (G2)2018 calculate payments for that year. In this case 2018 wouldn't have a payment because the lease starts in 2019. There would be payments in 2019 (but only from September to December), payments in 2020, payments in 2021, payments in 2022, payments in 2023, and payments only until September for 2024.
Then I will total the payments at the bottom of each year to project for future purchases.

View attachment 57714
Click to expand...

Darlene C said:
I am trying to set up a spreadsheet that will calculate payments for each year (depending on the lease start & end date). I was trying to use the IFS formula to calculate if that item is within that lease date time frame.
So for the Titan utility box: IF (E2) 2019 >= (G2)2018 calculate payments for that year. In this case 2018 wouldn't have a payment because the lease starts in 2019. There would be payments in 2019 (but only from September to December), payments in 2020, payments in 2021, payments in 2022, payments in 2023, and payments only until September for 2024.
Then I will total the payments at the bottom of each year to project for future purchases.

View attachment 57714
Click to expand...


COMPDESCRIPTION (YR / MAKE / MODEL)FINANCINGMthly Lease PaymentLease StartLease End2018 pmnt2019 pmnt2020 pmnt2021 pmnt2022 pmnt2023 pmnt2024 pmnt2025 pmnt2026 pmnt2027 pmnt2028 pmnt2029 pmnt2030 pmnt
QH1 Titan5 Utility Box 10' x 12' L
C/W Stacker & Accessories		Add Capital$ 799.68
2019-09-01​
2024-09-01​
QHGravel ScreenerAdd Capital$ 2,145.92
2021-04-01​
2027-05-01​
KELJD - 2012 624K LoaderCWB$ 2,737.23
2019-05-01​
2025-05-01​
AMP2005 JD 8120Brandt Finance$ 1,787.52
2021-05-01​
2027-04-30​
KEL2008 John Deere 750J Crawler DozerBrandt Finance$ 4,934.59
2020-06-24​
2024-07-01​
WLSJD - 2018 250G ExcavatorJohn Deere$ 4,645.61
2018-10-31​
2024-11-01​
WFP332G SkidsteerJohn Deere$ 1,419.84
2019-10-28​
2024-11-01​
WLS2018 John Deere 380GLC Excavator BaseBrandt Finance$ 5,140.84
2020-09-01​
2026-09-01​
jtakw said:
Hi,

You have inconsistencies in your data sample. See my notes below.

Also, please look into posting sample using XL2BB, see my signature, or at least post in table format, Not a picture, so helpers can copy your data for testing.

StartEnd
9/1/20199/1/2024<Is End month inclusive? If so, lease is 5 year 1 month, If not, End should be 8/31/2024
4/1/20215/1/2027<Same as above, lease is 5 year 2 month if End is inclusive
5/1/20214/30/2027<This seems correct
6/24/20207/1/2024<What about day proration?
10/31/201811/1/2024<End month inclusive? Day proration?
Click to expand...

hi jtakw - I am having troubles trying to get xl2bb downloaded and into excel to use.
I copied and pasted above but am unsure if this will be of any use in trying to help me with this.
I appreciate your response
 
Upvote 0
Thanks for trying, it helps a little.
You haven't addressed the inconsistencies I pointed out.
This is a solution Without Day Proration, and Always End Date Month Inclusive:

Please note G1:S1 is entered as 1/1/2018, 1/1/2019, etc. and Custom Formatted as yyyy "pmnt"

Book3.xlsx
DEFGHIJKLMNOPQ
1Mthly Lease PaymentLease StartLease End2018 pmnt2019 pmnt2020 pmnt2021 pmnt2022 pmnt2023 pmnt2024 pmnt2025 pmnt2026 pmnt2027 pmnt2028 pmnt
2$799.689/1/20199/1/2024 3198.729596.169596.169596.169596.167197.12    
3$2,145.924/1/20215/1/2027   19313.2825751.0425751.0425751.0425751.0425751.0410729.6 
4$2,737.235/1/20195/1/2025 21897.8432846.7632846.7632846.7632846.7632846.7613686.15   
5$1,787.525/1/20214/30/2027   14300.1621450.2421450.2421450.2421450.2421450.247150.08 
6$4,934.596/24/20207/1/2024  34542.1359215.0859215.0859215.0834542.13    
7$4,645.6110/31/201811/1/202413936.8355747.3255747.3255747.3255747.3255747.3251101.71    
8$1,419.8410/28/201911/1/2024 4259.5217038.0817038.0817038.0817038.0815618.24    
9$5,140.849/1/20209/1/2026  20563.3661690.0861690.0861690.0861690.0861690.0846267.56  
Sheet1002
Cell Formulas
RangeFormula
G2:Q9G2=IF(OR(YEAR(G$1)<YEAR($E2),YEAR(G$1)>YEAR($F2)),"",IF(YEAR(G$1)=YEAR($E2),(DATEDIF($E2,EDATE(G$1,12)-1,"m")+1)*$D2,IF(YEAR(G$1)=YEAR($F2),(DATEDIF(G$1,$F2,"m")+1)*$D2,$D2*12)))
 
Upvote 0
jtakw said:
Thanks for trying, it helps a little.
You haven't addressed the inconsistencies I pointed out.
This is a solution Without Day Proration, and Always End Date Month Inclusive:

Please note G1:S1 is entered as 1/1/2018, 1/1/2019, etc. and Custom Formatted as yyyy "pmnt"

Book3.xlsx
DEFGHIJKLMNOPQ
1Mthly Lease PaymentLease StartLease End2018 pmnt2019 pmnt2020 pmnt2021 pmnt2022 pmnt2023 pmnt2024 pmnt2025 pmnt2026 pmnt2027 pmnt2028 pmnt
2$799.689/1/20199/1/2024 3198.729596.169596.169596.169596.167197.12    
3$2,145.924/1/20215/1/2027   19313.2825751.0425751.0425751.0425751.0425751.0410729.6 
4$2,737.235/1/20195/1/2025 21897.8432846.7632846.7632846.7632846.7632846.7613686.15   
5$1,787.525/1/20214/30/2027   14300.1621450.2421450.2421450.2421450.2421450.247150.08 
6$4,934.596/24/20207/1/2024  34542.1359215.0859215.0859215.0834542.13    
7$4,645.6110/31/201811/1/202413936.8355747.3255747.3255747.3255747.3255747.3251101.71    
8$1,419.8410/28/201911/1/2024 4259.5217038.0817038.0817038.0817038.0815618.24    
9$5,140.849/1/20209/1/2026  20563.3661690.0861690.0861690.0861690.0861690.0846267.56  
Sheet1002
Cell Formulas
RangeFormula
G2:Q9G2=IF(OR(YEAR(G$1)<YEAR($E2),YEAR(G$1)>YEAR($F2)),"",IF(YEAR(G$1)=YEAR($E2),(DATEDIF($E2,EDATE(G$1,12)-1,"m")+1)*$D2,IF(YEAR(G$1)=YEAR($F2),(DATEDIF(G$1,$F2,"m")+1)*$D2,$D2*12)))
Click to expand...


i got it to work!
mrexcel samp.xlsx
ABCDEFGHIJKLMNOPQRS
1COMPDESCRIPTION (YR / MAKE / MODEL)FINANCING Mthly Lease Payment Lease StartLease End2018 pmnt2019 pmnt2020 pmnt2021 pmnt2022 pmnt2023 pmnt2024 pmnt2025 pmnt2026 pmnt2027 pmnt2028 pmnt2029 pmnt2030 pmnt
2QH1 Titan5 Utility Box 10' x 12' L C/W Stacker & AccessoriesAdd Capital$ 799.682019-09-012024-09-01#VALUE!#VALUE!#VALUE!
3QHGravel Screener Add Capital$ 2,145.922021-04-012027-05-01
4KELJD - 2012 624K LoaderCWB$ 2,737.232019-05-012025-05-01
5AMP2005 JD 8120 Brandt Finance$ 1,787.522021-05-012027-04-30
6KEL2008 John Deere 750J Crawler DozerBrandt Finance$ 4,934.592020-06-242024-07-01
7WLSJD - 2018 250G ExcavatorJohn Deere$ 4,645.612018-10-312024-11-01
8WFP332G SkidsteerJohn Deere$ 1,419.842019-10-282024-11-01
9WLS2018 John Deere 380GLC Excavator BaseBrandt Finance$ 5,140.842020-09-012026-09-01
10
Sheet1
Cell Formulas
RangeFormula
G2:I2G2=IF(OR(YEAR(G$1)<YEAR($E2),YEAR(G$1)>YEAR($F2)),"",IF(YEAR(G$1)=YEAR($E2),(DATEDIF($E2,EDATE(G$1,12)-1,"m")+1)*$D2,IF(YEAR(G$1)=YEAR($F2),(DATEDIF(G$1,$F2,"m")+1)*$D2,$D2*12)))


I'm not sure I'm understanding your note about the date though. I tried pasting your formula to see how this would work and it brought up #VALUE! only
 
Upvote 0
jtakw said:
You haven't addressed the inconsistencies I pointed out.
This is a solution Without Day Proration, and Always End Date Month Inclusive:

Please note G1:S1 is entered as 1/1/2018, 1/1/2019, etc. and Custom Formatted as yyyy "pmnt"
Click to expand...

I'm not sure I'm understanding your note about the date though. I tried pasting your formula to see how this would work and it brought up #VALUE! only
Click to expand...

Did you enter the G1:S1 values as stated above Bold in Red.

Regarding the Days and End Month, read my Post # 2 again.
 
Upvote 0
jtakw said:
Did you enter the G1:S1 values as stated above Bold in Red.

Regarding the Days and End Month, read my Post # 2 again.
Click to expand...

mrexcel samp.xlsx
ABCDEFGHIJKLMNOPQRS
1COMPDESCRIPTION (YR / MAKE / MODEL)FINANCING Mthly Lease Payment Lease StartLease End2018 pmnt2019 pmnt2020 pmnt2021 pmnt2022 pmnt2023 pmnt2024 pmnt2025 pmnt2026 pmnt2027 pmnt2028 pmnt2029 pmnt2030 pmnt
2QH1 Titan5 Utility Box 10' x 12' L C/W Stacker & AccessoriesAdd Capital$ 799.682019-09-012024-08-01#VALUE!#VALUE!#VALUE!
3QHGravel Screener Add Capital$ 2,145.922021-04-012027-03-01
4KELJD - 2012 624K LoaderCWB$ 2,737.232019-05-012026-04-01
5AMP2005 JD 8120 Brandt Finance$ 1,787.522021-05-012027-04-30
6KEL2008 John Deere 750J Crawler DozerBrandt Finance$ 4,934.592020-06-242024-05-24
7WLSJD - 2018 250G ExcavatorJohn Deere$ 4,645.612018-10-312024-09-30
8WFP332G SkidsteerJohn Deere$ 1,419.842019-10-282024-09-28
9WLS2018 John Deere 380GLC Excavator BaseBrandt Finance$ 5,140.842020-09-012026-08-01
10
11
12
Sheet1
Cell Formulas
RangeFormula
G2:I2G2=IF(OR(YEAR(G$1)<YEAR($E2),YEAR(G$1)>YEAR($F2)),"",IF(YEAR(G$1)=YEAR($E2),(DATEDIF($E2,EDATE(G$1,12)-1,"m")+1)*$D2,IF(YEAR(G$1)=YEAR($F2),(DATEDIF(G$1,$F2,"m")+1)*$D2,$D2*12)))


you're telling me that the end date is wrong according to the # of payments (eg. 5 year lease=60 payments and the dates I had would have actually resulted in 61 payments) ?
I have custom formatted G1:S1 as yyyy now
 
Upvote 0
Darlene C said:
1. you're telling me that the end date is wrong according to the # of payments (eg. 5 year lease=60 payments and the dates I had would have actually resulted in 61 payments) ?
2. I have custom formatted G1:S1 as yyyy now
Click to expand...

1. Yes, that's what I'm pointing out, and Also, when Start date is like 6/24/2020, are you prorating That month, you're not paying for the Whole month of June when you started on 6/24
2. Don't Just format as yyyy, you need to also enter 1/1/2018 in G1, 1/1/2019 in G2, 1/1/2020 in G3, and so on....
 
Last edited:
Upvote 0
What do you expect for 2019, if
Start date: 20-Dec-2019
Monthly: 3100
Result:
11 days (1100)
half month (1550)
or zero
???
 
Upvote 0
jtakw said:
1. Yes, that's what I'm pointing out, and Also, when Start date is like 6/24/2020, are you prorating That month, you're not paying for the Whole month of June when you started on 6/24
2. Don't Just format as yyyy, you need to also enter 1/1/2018 in G1, 1/1/2019 in G2, 1/1/2020 in G3, and so on....
Click to expand...
but not all payments come out on the 1st of the month and I want to use G1:S1 as my years only so I can get the big picture of what the payments will be for that whole year. My payments will be less in that year if the lease date ends in May (for example), rather than November.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Fiscal Year ending on the Friday closest to Nov 30th?
Replies
3
Views
334
Dave Patton
D
M
  • Question Question
Year over year counts
Replies
3
Views
395
RobP
R
E
  • Question Question
From two dates, dividing the years into different columns, create hyperlinks organized by quantity and not by dates
Replies
0
Views
329
ExcelenteNovel
E
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
675
Domenic
D
U
  • Question Question
year by year headcount snapshot with only start and end date
2
Replies
11
Views
1K
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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