Hi there, i have an amortization table that shows the # of payments remaining in cell H13 but it is stagnate and deosn't change. The formula shows the payments remaining until the balance is zero but i would like the actual months remaining to automatically change. EX. Today date is Jan 27, 2020. 2 payments have taken place as of today so i would like it to look for today's date and change to say 10 payments remaining. Not sure if another formula is needed or just a modification. Thank You
| Book1 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Loan Amortization Schedule | |||||||||
| 2 | ||||||||||
| 3 | ||||||||||
| 4 | ||||||||||
| 5 | Loan | |||||||||
| 6 | Loan Amount | 10,000.00 | Rate (per period) | 0.417% | ||||||
| 7 | Annual Interest Rate | 5.00% | Total Payments | 10,272.89 | ||||||
| 8 | Term of Loan in Years | 1 | Total Interest | 272.89 | ||||||
| 9 | First Payment Date | 12/20/2019 | Est. Interest Savings | - | ||||||
| 10 | Payment Frequency | Monthly | . | |||||||
| 11 | Compound Period | Monthly | . | |||||||
| 12 | Payment Type | End of Period | Balance Today | $8,367.80 | ||||||
| 13 | Rounding | On | # Of Remaining Months | 12 | ||||||
| 20 | [42] | |||||||||
| 21 | Monthly Payment | 856.07 | ||||||||
| 22 | ||||||||||
| 57 | ||||||||||
| 58 | Amortization Schedule | |||||||||
| 59 | No. | Due Date | Payment Due | Additional Payment | Interest | Principal | Balance | |||
| 60 | 12/6/2019 | 10,000.00 | ||||||||
| 61 | 1 | 12/20/2019 | 856.07 | 41.67 | 814.40 | 9,185.60 | ||||
| 62 | 2 | 1/20/2020 | 856.07 | 38.27 | 817.80 | 8,367.80 | ||||
| 63 | 3 | 2/20/2020 | 856.07 | 34.87 | 821.20 | 7,546.60 | ||||
| 64 | 4 | 3/20/2020 | 856.07 | 31.44 | 824.63 | 6,721.97 | ||||
| 65 | 5 | 4/20/2020 | 856.07 | 28.01 | 828.06 | 5,893.91 | ||||
| 66 | 6 | 5/20/2020 | 856.07 | 24.56 | 831.51 | 5,062.40 | ||||
| 67 | 7 | 6/20/2020 | 856.07 | 21.09 | 834.98 | 4,227.42 | ||||
| 68 | 8 | 7/20/2020 | 856.07 | 17.61 | 838.46 | 3,388.96 | ||||
| 69 | 9 | 8/20/2020 | 856.07 | 14.12 | 841.95 | 2,547.01 | ||||
| 70 | 10 | 9/20/2020 | 856.07 | 10.61 | 845.46 | 1,701.55 | ||||
| 71 | 11 | 10/20/2020 | 856.07 | 7.09 | 848.98 | 852.57 | ||||
| 72 | 12 | 11/20/2020 | 856.12 | 3.55 | 852.57 | 0.00 | ||||
| 73 | ||||||||||
| 74 | ||||||||||
Borrowell | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D11 | D11 | =D10 |
| H6 | H6 | =((1+D7/D15)^(D15/D14))-1 |
| H7 | H7 | =SUM(F61:F840)+SUM(G61:G840) |
| H8 | H8 | =SUM(F60:F840) |
| H9 | H9 | =IF(AND(SUM(D61:D840)=0,roundOpt)," - ",(nper*(-PMT(rate,nper,loan_amount,,pmtType))-loan_amount)-H8) |
| H10 | H10 | =IF(AND(NOT(H840=""),H840>0.004),"ERROR: Limit is "&OFFSET(A841,-1,0,1,1)&" payments",".") |
| H11 | H11 | =IF(D15>D14,"Warning: negative amortization",".") |
| H12 | H12 | =VLOOKUP(TODAY(),B22:H149,7) |
| H13 | H13 | =IF(IF(D6*D7*D8*D9>0,1,0),MATCH(0.01,H61:H1001,-1)+1,"") |
| C21 | C21 | =D10&" Payment" |
| D21 | D21 | =IF(roundOpt,ROUND(-PMT(rate,nper,$D$6,,pmtType),2),-PMT(rate,nper,$D$6,,pmtType)) |
| H60 | H60 | =$D$6 |
| A61:A74 | A61 | =IF(H60="","",IF(roundOpt,IF(OR(A60>=nper,ROUND(H60,2)<=0),"",A60+1),IF(OR(A60>=nper,H60<=0),"",A60+1))) |
| B61:B74 | B61 | =IF(A61="","",IF(OR(periods_per_year=26,periods_per_year=52),IF(periods_per_year=26,IF(A61=1,fpdate,B60+14),IF(periods_per_year=52,IF(A61=1,fpdate,B60+7),"n/a")),IF(periods_per_year=24,DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)/2+IF(AND(DAY(fpdate)>=15,MOD(A61,2)=0),1,0),IF(MOD(A61,2)=0,IF(DAY(fpdate)>=15,DAY(fpdate)-14,DAY(fpdate)+14),DAY(fpdate))),IF(DAY(DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)*months_per_period,DAY(fpdate)))<>DAY(fpdate),DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)*months_per_period+1,0),DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)*months_per_period,DAY(fpdate)))))) |
| C61:C74 | C61 | =IF(A61="","",IF(roundOpt,IF(OR(A61=nper,payment>ROUND((1+rate)*H60,2)),ROUND((1+rate)*H60,2),payment),IF(OR(A61=nper,payment>(1+rate)*H60),(1+rate)*H60,payment))) |
| F61:F74 | F61 | =IF(A61="","",IF(AND(A61=1,pmtType=1),0,IF(roundOpt,ROUND(rate*H60,2),rate*H60))) |
| G61:G74 | G61 | =IF(A61="","",C61-F61+D61) |
| H61:H74 | H61 | =IF(A61="","",H60-G61) |
