Sequential numbers based on cell value

N

nevernine99

New Member
Joined
Dec 18, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
How do I enter sequential numbers based on cell value start with D9 and end with D7 (30 years term) and I would like to calculate in range Q3:V14.
If I change D7 value to 10 years, in range Q3:V14 should count only 10 years and stop. See my attached XL2BB file.
Thank you so much for your help.

Value based on year.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1No.Payment DateInterest RatePayment DueExtra PaymentInterestPrincipalBalanceTotal InterestTotal PrincipalYearAverage % Interest RateExtra PaymentInterestPrincipalBalance
2
31January 1, 20202.00%10683.11100993.339789.78586210.2993.339789.7820202.58%100.0013890.12114352.08481647.9
4Loan Information2February 1, 20202.00%10683.11977.029706.09576504.11970.3519495.8720212.08%100.009296.53116799.43364848.5
53March 1, 20202.00%10683.11960.849722.27566781.92931.1929218.1420223.75%0.0011235.55117099.22247749.3
6Loan amount 596,000.004April 1, 20202.00%10683.11944.649738.47557043.43875.8338956.612023
7Term (years) 305May 1, 20202.00%10683.11928.419854.7547188.74804.2448811.312024
8Starting interest rate 2.901%6June 1, 20203.00%10660.951367.979292.98537895.76172.2158104.292025
9First payment date 1-Jan-207July 1, 20203.00%10660.951344.749316.21528579.57516.9567420.52026
108August 1, 20203.00%10660.951321.459339.55192408838.4767602027
11Starting monthly payment 2,481.029September 1, 20203.00%10660.951298.19362.85509877.210136.586122.852028
12.10October 1, 20203.00%10660.951274.699386.26500490.911411.1995509.112029
13Rate Adjustments11November 1, 20203.00%10660.951251.239409.72491081.212662.42104918.82030
14RateAs Of …12December 1, 20203.00%10660.951227.79433.25481647.913890.12114352.12031
15Start Rate 2.901%Jan-01-202013January 1, 20214.00%10875.151605.499269.66472378.315495.61123621.7
16New Rates 3.000%Jun-01-202014February 1, 20214.00%10875.151574.599300.56463077.717070.2132922.3
172.000%Sep-01-202015March 1, 20214.00%10875.151543.599331.56453746.118613.79142253.9
184.000%Jan-01-202116April 1, 20212.00%10474.49756.249718.25444027.919370.03151972.1
193.000%Jun-01-202117May 1, 20212.00%10474.49740.059734.44434293.520110.08161706.6
203.000%Apr-01-202218June 1, 20212.00%10474.49723.829750.67424542.820833.9171457.2
212.000%Aug-01-202219July 1, 20212.00%10474.49707.579766.92414775.921541.47181224.1
2220August 1, 20211.00%10294.51345.659948.8640482721887.12191173
2321September 1, 20211.00%10294.51337.369957.15394869.922224.48201130.2
24Summary22October 1, 20211.00%10294.51329.069965.45384904.422553.54211095.6
25Est. Max monthly payment 10,875.1523November 1, 20211.00%10294.51100320.7510073.76374830.622874.29221169.4
26Total payments 643,531.4324December 1, 20211.00%10294.51312.369982.15364848.523186.65231151.5
27Total interest 47,531.4325January 1, 20222.00%10450.2608.089842.12355006.423794.73240993.6
2826February 1, 20222.00%10450.2591.689858.52345147.924386.41250852.2
2927March 1, 20222.00%10450.2575.259874.95335272.924961.66260727.1
3028April 1, 20222.00%10450.2558.799891.41325381.525520.45270618.5
3129May 1, 20222.00%10450.2542.39907.9315473.626062.75280526.4
3230June 1, 20225.00%10869.111314.479554.6430591927377.22290081.1
3331July 1, 20225.00%10869.111274.669594.45296324.528651.88299675.5
3432August 1, 20225.00%10869.111234.699634.42286690.129886.57309309.9
3533September 1, 20225.00%10869.111194.549674.57277015.531081.11318984.5
3634October 1, 20225.00%10869.111154.239714.88267300.632235.34328699.4
3735November 1, 20225.00%10869.111113.759755.36257545.333349.09338454.7
3836December 1, 20225.00%10869.111073.119796247749.334422.2348250.7
3937January 1, 20235.00%10869.111032.299836.82237912.535454.49358087.6
4038February 1, 20235.00%10869.11991.39877.81228034.636445.79367965.4
4139March 1, 20235.00%10869.11950.149918.97218115.737395.93377884.3
4240April 1, 20235.00%10869.11908.829960.29208155.438304.75387844.6
4341May 1, 20235.00%10869.11867.3110001.8198153.639172.06397846.4
4442June 1, 20235.00%10869.11825.6410043.47188110.139997.7407889.9
4543July 1, 20235.00%10869.11783.7910085.32178024.840781.49417975.2
4644August 1, 20235.00%10869.11741.7710127.34167897.541523.26428102.6
4745September 1, 20235.00%10869.11699.5710169.54157727.942222.83438272.1
4846October 1, 20235.00%10869.11657.210211.9114751642880.03448484
4947November 1, 20235.00%10869.11614.6510254.46137261.543494.68458738.5
5048December 1, 20235.00%10869.11571.9210297.19126964.444066.6469035.7
5149January 1, 20245.00%10869.11529.0210340.09116624.344595.62479375.7
5250February 1, 20245.00%10869.11485.9310383.18106241.145081.55489758.9
5351March 1, 20245.00%10869.11442.6710426.4495814.6445524.22500185.4
5452April 1, 20245.00%10869.11399.2310469.8885344.7645923.45510655.2
5553May 1, 20245.00%10869.11355.610513.5174831.2546279.05521168.8
5654June 1, 20245.00%10869.11311.810557.3164273.9446590.85531726.1
5755July 1, 20245.00%10869.11267.8110601.353672.6446858.66542327.4
5856August 1, 20245.00%10869.11223.6410645.4743027.1747082.3552972.8
5957September 1, 20245.00%10869.11179.2810689.8332337.3447261.58563662.7
6058October 1, 20245.00%10869.11134.7410734.3721602.9747396.32574397
6159November 1, 20245.00%10869.1190.0110779.110823.8747486.33585176.1
6260December 1, 20245.00%10868.9745.110823.87047531.43596000
63
64
Sheet1
Cell Formulas
RangeFormula
R3:R5R3=AVERAGEIFS($H$3:$H$482,$G$3:$G$482,">="&DATE(Q3,1,1),$G$3:$G$482,"<="&DATE(Q3,12,31))
S3:U5S3=SUMIFS(J$3:J$512,$G$3:$G$512,">="&DATE($Q3,1,1),$G$3:$G$512,"<="&DATE($Q3,12,31))
V3:V5V3=VLOOKUP(DATEVALUE("December 1, " & $Q3),$G$3:$O$512,7,0)
D11D11=ROUND(-PMT(D8/12,($D$7*12),$D$6),2)
D12D12=IF(($D$7*12)>480,"ERROR: >480 payments",".")
C15C15=D8
D15D15=$D$9
D25D25=MAX(I3:I482)
D26D26=SUM(K3:K482)+SUM(L3:L482)
D27D27=SUM(K3:K483)
Cells with Data Validation
CellAllowCriteria
D15Any value
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Are your account details up to date?...you're using Excel 2019? I ask because Excel 365 has several features/functions that would make this easier.
 
Upvote 0
Yes, my account details are up to date.
Unfortunately, I am using 2019 Office Professional Pro.
 
Upvote 0
You're welcome, thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Cashflow forecast based on start dates and either monthly, bi-monthly, quarterly, annually
Replies
2
Views
484
felixstraube
F
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
278
B-radK
B
L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
142
Peter_SSs
Peter_SSs
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
282
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,734
Messages
6,126,545
Members
449,317
Latest member
chingiloum

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