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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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 Question
Cashflow forecast based on start dates and either monthly, bi-monthly, quarterly, annually
Replies
2
Views
784
felixstraube
F
B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
565
B-radK
B
E
  • Solved
Find a value matching a range upon several criteria
Replies
2
Views
166
ED38
E
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
60
aurelius33
A
L
  • Solved
Help with formula Number serial based on criteria monthly
Replies
4
Views
318
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,880
Messages
6,175,153
Members
452,615
Latest member
bogeys2birdies

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