nevernine99
New Member
- Joined
- Dec 18, 2021
- Messages
- 10
- Office Version
- 365
- Platform
- 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.
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 | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
1 | No. | Payment Date | Interest Rate | Payment Due | Extra Payment | Interest | Principal | Balance | Total Interest | Total Principal | Year | Average % Interest Rate | Extra Payment | Interest | Principal | Balance | |||||||||
2 | |||||||||||||||||||||||||
3 | 1 | January 1, 2020 | 2.00% | 10683.11 | 100 | 993.33 | 9789.78 | 586210.2 | 993.33 | 9789.78 | 2020 | 2.58% | 100.00 | 13890.12 | 114352.08 | 481647.9 | |||||||||
4 | Loan Information | 2 | February 1, 2020 | 2.00% | 10683.11 | 977.02 | 9706.09 | 576504.1 | 1970.35 | 19495.87 | 2021 | 2.08% | 100.00 | 9296.53 | 116799.43 | 364848.5 | |||||||||
5 | 3 | March 1, 2020 | 2.00% | 10683.11 | 960.84 | 9722.27 | 566781.9 | 2931.19 | 29218.14 | 2022 | 3.75% | 0.00 | 11235.55 | 117099.22 | 247749.3 | ||||||||||
6 | Loan amount | 596,000.00 | 4 | April 1, 2020 | 2.00% | 10683.11 | 944.64 | 9738.47 | 557043.4 | 3875.83 | 38956.61 | 2023 | |||||||||||||
7 | Term (years) | 30 | 5 | May 1, 2020 | 2.00% | 10683.11 | 928.41 | 9854.7 | 547188.7 | 4804.24 | 48811.31 | 2024 | |||||||||||||
8 | Starting interest rate | 2.901% | 6 | June 1, 2020 | 3.00% | 10660.95 | 1367.97 | 9292.98 | 537895.7 | 6172.21 | 58104.29 | 2025 | |||||||||||||
9 | First payment date | 1-Jan-20 | 7 | July 1, 2020 | 3.00% | 10660.95 | 1344.74 | 9316.21 | 528579.5 | 7516.95 | 67420.5 | 2026 | |||||||||||||
10 | 8 | August 1, 2020 | 3.00% | 10660.95 | 1321.45 | 9339.5 | 519240 | 8838.4 | 76760 | 2027 | |||||||||||||||
11 | Starting monthly payment | 2,481.02 | 9 | September 1, 2020 | 3.00% | 10660.95 | 1298.1 | 9362.85 | 509877.2 | 10136.5 | 86122.85 | 2028 | |||||||||||||
12 | . | 10 | October 1, 2020 | 3.00% | 10660.95 | 1274.69 | 9386.26 | 500490.9 | 11411.19 | 95509.11 | 2029 | ||||||||||||||
13 | Rate Adjustments | 11 | November 1, 2020 | 3.00% | 10660.95 | 1251.23 | 9409.72 | 491081.2 | 12662.42 | 104918.8 | 2030 | ||||||||||||||
14 | Rate | As Of … | 12 | December 1, 2020 | 3.00% | 10660.95 | 1227.7 | 9433.25 | 481647.9 | 13890.12 | 114352.1 | 2031 | |||||||||||||
15 | Start Rate | 2.901% | Jan-01-2020 | 13 | January 1, 2021 | 4.00% | 10875.15 | 1605.49 | 9269.66 | 472378.3 | 15495.61 | 123621.7 | |||||||||||||
16 | New Rates | 3.000% | Jun-01-2020 | 14 | February 1, 2021 | 4.00% | 10875.15 | 1574.59 | 9300.56 | 463077.7 | 17070.2 | 132922.3 | |||||||||||||
17 | 2.000% | Sep-01-2020 | 15 | March 1, 2021 | 4.00% | 10875.15 | 1543.59 | 9331.56 | 453746.1 | 18613.79 | 142253.9 | ||||||||||||||
18 | 4.000% | Jan-01-2021 | 16 | April 1, 2021 | 2.00% | 10474.49 | 756.24 | 9718.25 | 444027.9 | 19370.03 | 151972.1 | ||||||||||||||
19 | 3.000% | Jun-01-2021 | 17 | May 1, 2021 | 2.00% | 10474.49 | 740.05 | 9734.44 | 434293.5 | 20110.08 | 161706.6 | ||||||||||||||
20 | 3.000% | Apr-01-2022 | 18 | June 1, 2021 | 2.00% | 10474.49 | 723.82 | 9750.67 | 424542.8 | 20833.9 | 171457.2 | ||||||||||||||
21 | 2.000% | Aug-01-2022 | 19 | July 1, 2021 | 2.00% | 10474.49 | 707.57 | 9766.92 | 414775.9 | 21541.47 | 181224.1 | ||||||||||||||
22 | 20 | August 1, 2021 | 1.00% | 10294.51 | 345.65 | 9948.86 | 404827 | 21887.12 | 191173 | ||||||||||||||||
23 | 21 | September 1, 2021 | 1.00% | 10294.51 | 337.36 | 9957.15 | 394869.9 | 22224.48 | 201130.2 | ||||||||||||||||
24 | Summary | 22 | October 1, 2021 | 1.00% | 10294.51 | 329.06 | 9965.45 | 384904.4 | 22553.54 | 211095.6 | |||||||||||||||
25 | Est. Max monthly payment | 10,875.15 | 23 | November 1, 2021 | 1.00% | 10294.51 | 100 | 320.75 | 10073.76 | 374830.6 | 22874.29 | 221169.4 | |||||||||||||
26 | Total payments | 643,531.43 | 24 | December 1, 2021 | 1.00% | 10294.51 | 312.36 | 9982.15 | 364848.5 | 23186.65 | 231151.5 | ||||||||||||||
27 | Total interest | 47,531.43 | 25 | January 1, 2022 | 2.00% | 10450.2 | 608.08 | 9842.12 | 355006.4 | 23794.73 | 240993.6 | ||||||||||||||
28 | 26 | February 1, 2022 | 2.00% | 10450.2 | 591.68 | 9858.52 | 345147.9 | 24386.41 | 250852.2 | ||||||||||||||||
29 | 27 | March 1, 2022 | 2.00% | 10450.2 | 575.25 | 9874.95 | 335272.9 | 24961.66 | 260727.1 | ||||||||||||||||
30 | 28 | April 1, 2022 | 2.00% | 10450.2 | 558.79 | 9891.41 | 325381.5 | 25520.45 | 270618.5 | ||||||||||||||||
31 | 29 | May 1, 2022 | 2.00% | 10450.2 | 542.3 | 9907.9 | 315473.6 | 26062.75 | 280526.4 | ||||||||||||||||
32 | 30 | June 1, 2022 | 5.00% | 10869.11 | 1314.47 | 9554.64 | 305919 | 27377.22 | 290081.1 | ||||||||||||||||
33 | 31 | July 1, 2022 | 5.00% | 10869.11 | 1274.66 | 9594.45 | 296324.5 | 28651.88 | 299675.5 | ||||||||||||||||
34 | 32 | August 1, 2022 | 5.00% | 10869.11 | 1234.69 | 9634.42 | 286690.1 | 29886.57 | 309309.9 | ||||||||||||||||
35 | 33 | September 1, 2022 | 5.00% | 10869.11 | 1194.54 | 9674.57 | 277015.5 | 31081.11 | 318984.5 | ||||||||||||||||
36 | 34 | October 1, 2022 | 5.00% | 10869.11 | 1154.23 | 9714.88 | 267300.6 | 32235.34 | 328699.4 | ||||||||||||||||
37 | 35 | November 1, 2022 | 5.00% | 10869.11 | 1113.75 | 9755.36 | 257545.3 | 33349.09 | 338454.7 | ||||||||||||||||
38 | 36 | December 1, 2022 | 5.00% | 10869.11 | 1073.11 | 9796 | 247749.3 | 34422.2 | 348250.7 | ||||||||||||||||
39 | 37 | January 1, 2023 | 5.00% | 10869.11 | 1032.29 | 9836.82 | 237912.5 | 35454.49 | 358087.6 | ||||||||||||||||
40 | 38 | February 1, 2023 | 5.00% | 10869.11 | 991.3 | 9877.81 | 228034.6 | 36445.79 | 367965.4 | ||||||||||||||||
41 | 39 | March 1, 2023 | 5.00% | 10869.11 | 950.14 | 9918.97 | 218115.7 | 37395.93 | 377884.3 | ||||||||||||||||
42 | 40 | April 1, 2023 | 5.00% | 10869.11 | 908.82 | 9960.29 | 208155.4 | 38304.75 | 387844.6 | ||||||||||||||||
43 | 41 | May 1, 2023 | 5.00% | 10869.11 | 867.31 | 10001.8 | 198153.6 | 39172.06 | 397846.4 | ||||||||||||||||
44 | 42 | June 1, 2023 | 5.00% | 10869.11 | 825.64 | 10043.47 | 188110.1 | 39997.7 | 407889.9 | ||||||||||||||||
45 | 43 | July 1, 2023 | 5.00% | 10869.11 | 783.79 | 10085.32 | 178024.8 | 40781.49 | 417975.2 | ||||||||||||||||
46 | 44 | August 1, 2023 | 5.00% | 10869.11 | 741.77 | 10127.34 | 167897.5 | 41523.26 | 428102.6 | ||||||||||||||||
47 | 45 | September 1, 2023 | 5.00% | 10869.11 | 699.57 | 10169.54 | 157727.9 | 42222.83 | 438272.1 | ||||||||||||||||
48 | 46 | October 1, 2023 | 5.00% | 10869.11 | 657.2 | 10211.91 | 147516 | 42880.03 | 448484 | ||||||||||||||||
49 | 47 | November 1, 2023 | 5.00% | 10869.11 | 614.65 | 10254.46 | 137261.5 | 43494.68 | 458738.5 | ||||||||||||||||
50 | 48 | December 1, 2023 | 5.00% | 10869.11 | 571.92 | 10297.19 | 126964.4 | 44066.6 | 469035.7 | ||||||||||||||||
51 | 49 | January 1, 2024 | 5.00% | 10869.11 | 529.02 | 10340.09 | 116624.3 | 44595.62 | 479375.7 | ||||||||||||||||
52 | 50 | February 1, 2024 | 5.00% | 10869.11 | 485.93 | 10383.18 | 106241.1 | 45081.55 | 489758.9 | ||||||||||||||||
53 | 51 | March 1, 2024 | 5.00% | 10869.11 | 442.67 | 10426.44 | 95814.64 | 45524.22 | 500185.4 | ||||||||||||||||
54 | 52 | April 1, 2024 | 5.00% | 10869.11 | 399.23 | 10469.88 | 85344.76 | 45923.45 | 510655.2 | ||||||||||||||||
55 | 53 | May 1, 2024 | 5.00% | 10869.11 | 355.6 | 10513.51 | 74831.25 | 46279.05 | 521168.8 | ||||||||||||||||
56 | 54 | June 1, 2024 | 5.00% | 10869.11 | 311.8 | 10557.31 | 64273.94 | 46590.85 | 531726.1 | ||||||||||||||||
57 | 55 | July 1, 2024 | 5.00% | 10869.11 | 267.81 | 10601.3 | 53672.64 | 46858.66 | 542327.4 | ||||||||||||||||
58 | 56 | August 1, 2024 | 5.00% | 10869.11 | 223.64 | 10645.47 | 43027.17 | 47082.3 | 552972.8 | ||||||||||||||||
59 | 57 | September 1, 2024 | 5.00% | 10869.11 | 179.28 | 10689.83 | 32337.34 | 47261.58 | 563662.7 | ||||||||||||||||
60 | 58 | October 1, 2024 | 5.00% | 10869.11 | 134.74 | 10734.37 | 21602.97 | 47396.32 | 574397 | ||||||||||||||||
61 | 59 | November 1, 2024 | 5.00% | 10869.11 | 90.01 | 10779.1 | 10823.87 | 47486.33 | 585176.1 | ||||||||||||||||
62 | 60 | December 1, 2024 | 5.00% | 10868.97 | 45.1 | 10823.87 | 0 | 47531.43 | 596000 | ||||||||||||||||
63 | |||||||||||||||||||||||||
64 | |||||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R3:R5 | R3 | =AVERAGEIFS($H$3:$H$482,$G$3:$G$482,">="&DATE(Q3,1,1),$G$3:$G$482,"<="&DATE(Q3,12,31)) |
S3:U5 | S3 | =SUMIFS(J$3:J$512,$G$3:$G$512,">="&DATE($Q3,1,1),$G$3:$G$512,"<="&DATE($Q3,12,31)) |
V3:V5 | V3 | =VLOOKUP(DATEVALUE("December 1, " & $Q3),$G$3:$O$512,7,0) |
D11 | D11 | =ROUND(-PMT(D8/12,($D$7*12),$D$6),2) |
D12 | D12 | =IF(($D$7*12)>480,"ERROR: >480 payments",".") |
C15 | C15 | =D8 |
D15 | D15 | =$D$9 |
D25 | D25 | =MAX(I3:I482) |
D26 | D26 | =SUM(K3:K482)+SUM(L3:L482) |
D27 | D27 | =SUM(K3:K483) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D15 | Any value |