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 |