Hi there, I have an amortization schedule that has a formula in cell H13. This formula was given to me by someone on this forum for which I'm very thankful. It gives me the remaining months left on the loan. In general it searches for today and finds the remaining months. If i make additional payments it will also reduce the number of payments if entered up to todays date. As an ex. if i put $1000 additional in D61 the payments will drop to 19, and if another $1000 in D62 the payments to 17. If i put in another $1000 in D63 the payments remain the same because the date hasn't occurred. As i use this amortization table for "what if scenario's", i would like it to reflect todays date but the table should reduce payments beyond that date as the table shrinks when a payment is made. I hope this makes sense. Thank you.
| 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 | =NPER($D$7/12,-$D$21,INDEX($H$60:$H$100,MATCH(TODAY(),$B$60:$B$102))) |
| 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:A103 | A61 | =IF(H60="","",IF(roundOpt,IF(OR(A60>=nper,ROUND(H60,2)<=0),"",A60+1),IF(OR(A60>=nper,H60<=0),"",A60+1))) |
| B61:B103 | 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:C103 | 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:F103 | F61 | =IF(A61="","",IF(AND(A61=1,pmtType=1),0,IF(roundOpt,ROUND(rate*H60,2),rate*H60))) |
| G61:G103 | G61 | =IF(A61="","",C61-F61+D61) |
| H61:H103 | H61 | =IF(A61="","",H60-G61) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Borrowell | =Loan!$B$25:INDEX(Loan!$J:$J,MATCH(99^99,Loan!$B:$B,1)) | H7:H9, H12:H13, A61:C85, F85, F61:H84, H85 |
| compound_period | =Loan!$D$15 | H6, H11 |
| fpdate | =Loan!$D$9 | B61:B103 |
| loan_amount | =Loan!$D$6 | H9, D21, H60 |
| months_per_period | =Loan!$D$17 | B61:B103 |
| nper | =Loan!$D$18 | H9, D21, A61:A103, C61:C103 |
| payment | =Loan!$D$21 | H13, C61:C103 |
| periods_per_year | =Loan!$D$14 | H6, H11, B61:B103 |
| pmtType | =Loan!$D$16 | H9, D21, F61:F103 |
| Print_Area | =OFFSET(Loan!$A$1,0,0,ROW(Loan!$A$59)+1+Loan!$D$18,8) | H6:H9, D11, H11:H13, C21:D21, A61:C103, H60:H85, F61:G84, F85:F103 |
| Print_Titles | =Loan!$59:$59 | H12 |
| rate | =Loan!$H$6 | H9, D21, C61:C103, F61:F103 |
| roundOpt | =Loan!$D$19 | H9, D21, A61:A103, C61:C103, F61:F103 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A61:H840 | Expression | =MOD($A61,periods_per_year)=0 | text | YES |
| D11 | Expression | =compound_period>periods_per_year | text | YES |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D10:D11 | List | =$L$6:$L$13 |
| D12 | List | End of Period, Beginning of Period |
| D13 | List | On,Off |
