spb/traite-evaluation-qc
Public
1#!/usr/bin/env python32"""Classeurs Excel du traité (openpyxl, formules vivantes) : spreadsheets/3- cas1_grille_comparaison.xlsx : grille d'ajustements des 5 comparables (ch. 16-17) avec formules (prix ajusté, brut/net, pondération)4- cas3_dcf_12_logements.xlsx : état locatif, dépenses normalisées, RNE, capitalisation, DCF 10 ans, financement (ch. 28-31, 47)5- amortissement_hypothecaire.xlsx : calculateur d'hypothèque canadienne (composition semestrielle) avec tableau d'amortissement6Les valeurs sont celles des modules traite.cas1 / traite.cas3 ; les formules Excel reproduisent les identités du livre."""7import sys, pathlib8sys.path.insert(0, str(pathlib.Path(__file__).resolve().parents[1]))9from openpyxl import Workbook10from openpyxl.styles import Font, PatternFill, Alignment11from openpyxl.utils import get_column_letter12from traite import cas1, cas3, finance as fin1314ROOT = pathlib.Path(__file__).resolve().parents[3]; OUT = ROOT / "spreadsheets"; OUT.mkdir(exist_ok=True)15H = Font(bold=True, color="FFFFFF"); HF = PatternFill("solid", fgColor="003E7E"); NOTE = Font(italic=True, color="606670")16MONEY = '#,##0 "$"'; PCT = "0.00%"1718def entete(ws, row, labels):19 for j, l in enumerate(labels, 1):20 c = ws.cell(row=row, column=j, value=l); c.font = H; c.fill = HF; c.alignment = Alignment(wrap_text=True, vertical="center")2122def avert(ws, row, txt="DONNÉES SYNTHÉTIQUES — aucun bien réel. Généré par analysis/python/cases/make_spreadsheets.py"):23 ws.cell(row=row, column=1, value=txt).font = NOTE2425def grille_cas1():26 r = cas1.tout(); comps = r["selection"]["comparables"].reset_index(drop=True); adj = r["ajustements"]; s = r["sujet"]27 wb = Workbook(); ws = wb.active; ws.title = "Grille"28 avert(ws, 1); ws.cell(row=2, column=1, value="Grille d'ajustements — cas 1, maison de Gatineau (chapitres 16-17)").font = Font(bold=True, size=13)29 cols = ["Élément", "Sujet"] + list(comps["id_vente"])30 entete(ws, 4, cols)31 lignes = [("Date de vente", s["date_evaluation"], [str(d.date()) for d in comps["date_vente"]]), ("Superficie habitable (m²)", s["superficie_habitable_m2"], list(comps["superficie_habitable_m2"])),32 ("Terrain (m²)", s["superficie_terrain_m2"], list(comps["superficie_terrain_m2"])), ("Année de construction", s["annee_construction"], list(comps["annee_construction"])),33 ("Qualité (1-5)", s["qualite_1a5"], list(comps["qualite_1a5"])), ("Garage", s["garage_places"], list(comps["garage_places"])), ("Sous-sol fini", s["sous_sol_fini"], list(comps["sous_sol_fini"]))]34 row = 535 for lab, sv, vals in lignes:36 ws.cell(row=row, column=1, value=lab); ws.cell(row=row, column=2, value=sv)37 for j, v in enumerate(vals, 3): ws.cell(row=row, column=j, value=float(v) if isinstance(v, (int, float)) and not isinstance(v, bool) else v)38 row += 139 row += 1; ws.cell(row=row, column=1, value="Prix de vente").font = Font(bold=True); prix_row = row40 for j, v in enumerate(comps["prix_vente"], 3): ws.cell(row=row, column=j, value=float(v)).number_format = MONEY41 row += 1; ws.cell(row=row, column=1, value="Ajustements (du point de vue du sujet, $)").font = Font(italic=True)42 groupes = ["conditions_marche", "localisation", "superficie", "terrain", "age", "qualite", "garage", "sous_sol", "piscine", "type"]43 noms = ["Conditions du marché", "Localisation", "Superficie habitable", "Terrain", "Âge", "Qualité", "Garage", "Sous-sol", "Piscine", "Type"]44 first_adj = row + 145 for g, nm in zip(groupes, noms):46 row += 1; ws.cell(row=row, column=1, value=nm)47 for j in range(len(comps)): ws.cell(row=row, column=3 + j, value=round(float(adj.loc[j, g]), 2)).number_format = MONEY48 last_adj = row49 row += 1; ws.cell(row=row, column=1, value="Prix ajusté").font = Font(bold=True); paj_row = row50 for j in range(len(comps)):51 L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"={L}{prix_row}+SUM({L}{first_adj}:{L}{last_adj})").number_format = MONEY52 row += 1; ws.cell(row=row, column=1, value="Ajustement brut (%)"); brut_row = row53 for j in range(len(comps)):54 L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"=SUMPRODUCT(ABS({L}{first_adj}:{L}{last_adj}))/{L}{prix_row}").number_format = PCT55 row += 1; ws.cell(row=row, column=1, value="Ajustement net (%)")56 for j in range(len(comps)):57 L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"=({L}{paj_row}-{L}{prix_row})/{L}{prix_row}").number_format = PCT58 row += 1; ws.cell(row=row, column=1, value="Poids ∝ 1/brut"); w_row = row59 rng = f"{get_column_letter(3)}{brut_row}:{get_column_letter(2 + len(comps))}{brut_row}"60 for j in range(len(comps)):61 L = get_column_letter(3 + j); ws.cell(row=row, column=3 + j, value=f"=(1/MAX({L}{brut_row},0.01))/SUMPRODUCT(1/IF({rng}<0.01,0.01,{rng}))").number_format = "0.000"62 row += 2; ws.cell(row=row, column=1, value="Valeur indiquée (pondérée)").font = Font(bold=True)63 ws.cell(row=row, column=2, value=f"=SUMPRODUCT({get_column_letter(3)}{paj_row}:{get_column_letter(2+len(comps))}{paj_row},{get_column_letter(3)}{w_row}:{get_column_letter(2+len(comps))}{w_row})").number_format = MONEY64 row += 1; ws.cell(row=row, column=1, value="Moyenne / médiane des prix ajustés"); ws.cell(row=row, column=2, value=f"=AVERAGE({get_column_letter(3)}{paj_row}:{get_column_letter(2+len(comps))}{paj_row})").number_format = MONEY65 ws.cell(row=row, column=3, value=f"=MEDIAN({get_column_letter(3)}{paj_row}:{get_column_letter(2+len(comps))}{paj_row})").number_format = MONEY66 for c in range(1, 8): ws.column_dimensions[get_column_letter(c)].width = 26 if c == 1 else 1667 wb.save(OUT / "cas1_grille_comparaison.xlsx")6869def dcf_cas3():70 r = cas3.tout(); e = r["etat"]; b = r["base"]; h = r["hyp"]; proj = r["projection"]; f = r["financement"]71 wb = Workbook(); ws = wb.active; ws.title = "Hypothèses"; avert(ws, 1)72 ws.cell(row=2, column=1, value="Cas 3 — 12 logements à Montréal : capitalisation, DCF et financement (chapitres 28-31, 47)").font = Font(bold=True, size=13)73 hyp = [("Taux d'inoccupation et pertes", h["taux_inoccupation_pertes"], PCT), ("Croissance loyers contractuels (TAL)", h["croissance_loyers_contrat"], PCT), ("Croissance loyers de marché", h["croissance_loyers_marche"], PCT),74 ("Rotation annuelle", h["rotation_annuelle"], PCT), ("Croissance des dépenses", h["croissance_depenses"], PCT), ("Taux d'actualisation", h["taux_actualisation"], PCT), ("Taux terminal", h["taux_terminal"], PCT),75 ("Coûts de vente", h["couts_vente"], PCT), ("Taux de capitalisation retenu (médiane extraite)", r["taux"]["mediane"], PCT), ("RPV max", h["financement"]["rpv"], PCT), ("Taux hypothécaire (nominal, semestriel)", h["financement"]["taux"], PCT),76 ("Amortissement (mois)", h["financement"]["amortissement_mois"], "0"), ("RCD minimal", h["financement"]["rcd_min"], "0.00")]77 names = {}78 for i, (lab, v, fmt) in enumerate(hyp, 4):79 ws.cell(row=i, column=1, value=lab); c = ws.cell(row=i, column=2, value=float(v)); c.number_format = fmt; names[lab] = f"Hypothèses!$B${i}"80 ws.column_dimensions["A"].width = 46; ws.column_dimensions["B"].width = 1481 # état locatif82 w2 = wb.create_sheet("État locatif"); avert(w2, 1); entete(w2, 3, ["Logement", "Pièces", "Chambres", "Superficie (m²)", "Loyer contrat ($/mois)", "Loyer marché ($/mois)", "Écart ($/mois)", "Locataire depuis"])83 for i, row in e.iterrows():84 rr = 4 + i85 for j, k in enumerate(["logement", "pieces", "chambres", "superficie_m2", "loyer_mensuel_contrat", "loyer_mensuel_marche"], 1):86 v = row[k]; w2.cell(row=rr, column=j, value=float(v) if isinstance(v, (int, float)) and k not in ("logement",) else str(v))87 w2.cell(row=rr, column=7, value=f"=F{rr}-E{rr}"); w2.cell(row=rr, column=8, value=int(row["locataire_depuis"]))88 last = 3 + len(e); w2.cell(row=last + 1, column=1, value="Total mensuel").font = Font(bold=True)89 for col in "EFG": w2.cell(row=last + 1, column=ord(col) - 64, value=f"=SUM({col}4:{col}{last})").number_format = MONEY90 w2.cell(row=last + 2, column=1, value="RBP annuel (contrat)").font = Font(bold=True); w2.cell(row=last + 2, column=5, value=f"=E{last+1}*12").number_format = MONEY91 w2.cell(row=last + 2, column=6, value=f"=F{last+1}*12").number_format = MONEY92 rbp_ref = f"'État locatif'!$E${last+2}"93 # dépenses94 w3 = wb.create_sheet("Dépenses et RNE"); avert(w3, 1); entete(w3, 3, ["Poste", "Historique 2025 ($)", "Normalisé ($)", "Type"])95 det = b["detail"]96 for i, row in det.reset_index(drop=True).iterrows():97 rr = 4 + i; w3.cell(row=rr, column=1, value=row["poste"]); w3.cell(row=rr, column=2, value=None if row["historique_2025"] != row["historique_2025"] else float(row["historique_2025"])).number_format = MONEY98 w3.cell(row=rr, column=3, value=round(float(row["normalise"]), 2)).number_format = MONEY; w3.cell(row=rr, column=4, value=row["type"])99 ld = 3 + len(det)100 w3.cell(row=ld + 2, column=1, value="RBP (contrat)"); w3.cell(row=ld + 2, column=3, value=f"={rbp_ref}").number_format = MONEY101 w3.cell(row=ld + 3, column=1, value="Autres revenus"); w3.cell(row=ld + 3, column=3, value=float(b["autres_revenus"])).number_format = MONEY102 w3.cell(row=ld + 4, column=1, value="Inoccupation et pertes"); w3.cell(row=ld + 4, column=3, value=f"=-C{ld+2}*{names['Taux d\'inoccupation et pertes']}").number_format = MONEY103 w3.cell(row=ld + 5, column=1, value="RBE").font = Font(bold=True); w3.cell(row=ld + 5, column=3, value=f"=C{ld+2}+C{ld+3}+C{ld+4}").number_format = MONEY104 w3.cell(row=ld + 6, column=1, value="Dépenses totales (réserve incluse)"); w3.cell(row=ld + 6, column=3, value=f"=SUM(C4:C{ld})").number_format = MONEY105 w3.cell(row=ld + 7, column=1, value="RNE").font = Font(bold=True); w3.cell(row=ld + 7, column=3, value=f"=C{ld+5}-C{ld+6}").number_format = MONEY106 w3.cell(row=ld + 8, column=1, value="Ratio de dépenses"); w3.cell(row=ld + 8, column=3, value=f"=C{ld+6}/C{ld+5}").number_format = PCT107 w3.cell(row=ld + 10, column=1, value="Valeur par capitalisation directe (RNE année 1 / R)").font = Font(bold=True)108 w3.cell(row=ld + 10, column=3, value=f"='DCF'!C5/{names['Taux de capitalisation retenu (médiane extraite)']}").number_format = MONEY109 w3.column_dimensions["A"].width = 44; w3.column_dimensions["B"].width = 18; w3.column_dimensions["C"].width = 18110 # DCF111 w4 = wb.create_sheet("DCF"); avert(w4, 1); entete(w4, 3, ["Année", "RBP ($)", "RNE ($)", "CAPEX ($)", "Flux avant dette ($)", "Facteur d'actualisation", "VA ($)"])112 T = h["horizon_ans"]113 for i, row in proj.iterrows():114 rr = 4 + i; w4.cell(row=rr, column=1, value=int(row["annee"]))115 w4.cell(row=rr, column=2, value=round(float(row["rbp"]), 2)).number_format = MONEY; w4.cell(row=rr, column=3, value=round(float(row["rne"]), 2)).number_format = MONEY116 w4.cell(row=rr, column=4, value=round(float(row["capex"]), 2)).number_format = MONEY; w4.cell(row=rr, column=5, value=f"=C{rr}-D{rr}").number_format = MONEY117 if row["annee"] <= T:118 w4.cell(row=rr, column=6, value=f"=1/(1+{names['Taux d\'actualisation']})^A{rr}").number_format = "0.0000"; w4.cell(row=rr, column=7, value=f"=E{rr}*F{rr}").number_format = MONEY119 rT1 = 4 + T; rT = 3 + T120 w4.cell(row=rT1 + 2, column=1, value="Valeur terminale brute = RNE(T+1)/R_T"); w4.cell(row=rT1 + 2, column=7, value=f"=C{rT1}/{names['Taux terminal']}").number_format = MONEY121 w4.cell(row=rT1 + 3, column=1, value="Valeur terminale nette (après coûts de vente)"); w4.cell(row=rT1 + 3, column=7, value=f"=G{rT1+2}*(1-{names['Coûts de vente']})").number_format = MONEY122 w4.cell(row=rT1 + 4, column=1, value="VA de la valeur terminale"); w4.cell(row=rT1 + 4, column=7, value=f"=G{rT1+3}*F{rT}").number_format = MONEY123 w4.cell(row=rT1 + 5, column=1, value="VA des flux (années 1 à T)"); w4.cell(row=rT1 + 5, column=7, value=f"=SUM(G4:G{rT})").number_format = MONEY124 w4.cell(row=rT1 + 6, column=1, value="VALEUR DCF").font = Font(bold=True); w4.cell(row=rT1 + 6, column=7, value=f"=G{rT1+4}+G{rT1+5}").number_format = MONEY125 w4.cell(row=rT1 + 7, column=1, value="Part de la valeur terminale"); w4.cell(row=rT1 + 7, column=7, value=f"=G{rT1+4}/G{rT1+6}").number_format = PCT126 w4.cell(row=rT1 + 8, column=1, value=f"Contrôle : valeur calculée par traite.cas3 = {round(r['dcf']['valeur'])} $").font = NOTE127 w4.column_dimensions["A"].width = 44128 for c in "BCDEFG": w4.column_dimensions[c].width = 16129 # financement130 w5 = wb.create_sheet("Financement"); avert(w5, 1)131 w5.cell(row=3, column=1, value="Valeur retenue (DCF)"); w5.cell(row=3, column=2, value=f"='DCF'!G{rT1+6}").number_format = MONEY132 w5.cell(row=4, column=1, value="RNE année 1"); w5.cell(row=4, column=2, value="='DCF'!C4").number_format = MONEY133 w5.cell(row=5, column=1, value="Taux mensuel équivalent (composition semestrielle)"); w5.cell(row=5, column=2, value=f"=(1+{names['Taux hypothécaire (nominal, semestriel)']}/2)^(1/6)-1").number_format = "0.000000"134 w5.cell(row=6, column=1, value="Constante hypothécaire annuelle f"); w5.cell(row=6, column=2, value=f"=12*B5/(1-(1+B5)^(-{names['Amortissement (mois)']}))").number_format = "0.000000"135 w5.cell(row=7, column=1, value="Prêt max par RPV"); w5.cell(row=7, column=2, value=f"=B3*{names['RPV max']}").number_format = MONEY136 w5.cell(row=8, column=1, value="Prêt max par RCD"); w5.cell(row=8, column=2, value=f"=B4/{names['RCD minimal']}/B6").number_format = MONEY137 w5.cell(row=9, column=1, value="Prêt retenu (le plus faible)").font = Font(bold=True); w5.cell(row=9, column=2, value="=MIN(B7,B8)").number_format = MONEY138 w5.cell(row=10, column=1, value="Service de la dette annuel"); w5.cell(row=10, column=2, value="=B9*B6").number_format = MONEY139 w5.cell(row=11, column=1, value="RCD"); w5.cell(row=11, column=2, value="=B4/B10").number_format = "0.000"140 w5.cell(row=12, column=1, value="RPV"); w5.cell(row=12, column=2, value="=B9/B3").number_format = PCT141 w5.cell(row=13, column=1, value="Mise de fonds"); w5.cell(row=13, column=2, value="=B3-B9").number_format = MONEY142 w5.cell(row=14, column=1, value=f"Contrôle : prêt calculé par traite.cas3 = {round(f['pret'])} $ ({f['contrainte']})").font = NOTE143 w5.column_dimensions["A"].width = 50; w5.column_dimensions["B"].width = 18144 wb.save(OUT / "cas3_dcf_12_logements.xlsx")145146def amortissement():147 wb = Workbook(); ws = wb.active; ws.title = "Calculateur"148 ws.cell(row=1, column=1, value="Calculateur d'hypothèque canadienne (composition semestrielle, Loi sur l'intérêt art. 6) — chapitre 31").font = Font(bold=True, size=12)149 ws.cell(row=3, column=1, value="Capital emprunté ($)"); ws.cell(row=3, column=2, value=300000).number_format = MONEY150 ws.cell(row=4, column=1, value="Taux nominal annuel"); ws.cell(row=4, column=2, value=0.05).number_format = PCT151 ws.cell(row=5, column=1, value="Amortissement (mois)"); ws.cell(row=5, column=2, value=300)152 ws.cell(row=6, column=1, value="Taux mensuel équivalent"); ws.cell(row=6, column=2, value="=(1+B4/2)^(1/6)-1").number_format = "0.0000000"153 ws.cell(row=7, column=1, value="Paiement mensuel ($)").font = Font(bold=True); ws.cell(row=7, column=2, value="=B3*B6/(1-(1+B6)^(-B5))").number_format = '#,##0.00 "$"'154 ws.cell(row=8, column=1, value="Contrôle (traite.finance) : 300 000 $ à 5 %% sur 25 ans = %.2f $" % fin.Hypotheque(300000, 0.05, 300).paiement).font = NOTE155 entete(ws, 10, ["Mois", "Paiement", "Intérêt", "Capital", "Solde"])156 ws.cell(row=11, column=1, value=0); ws.cell(row=11, column=5, value="=B3").number_format = MONEY157 for k in range(1, 361):158 r = 11 + k159 ws.cell(row=r, column=1, value=f'=IF(A{r-1}<$B$5,A{r-1}+1,"")')160 ws.cell(row=r, column=2, value=f'=IF(A{r}="","",$B$7)').number_format = MONEY161 ws.cell(row=r, column=3, value=f'=IF(A{r}="","",E{r-1}*$B$6)').number_format = MONEY162 ws.cell(row=r, column=4, value=f'=IF(A{r}="","",B{r}-C{r})').number_format = MONEY163 ws.cell(row=r, column=5, value=f'=IF(A{r}="","",MAX(E{r-1}-D{r},0))').number_format = MONEY164 ws.column_dimensions["A"].width = 30165 for c in "BCDE": ws.column_dimensions[c].width = 16166 wb.save(OUT / "amortissement_hypothecaire.xlsx")167168if __name__ == "__main__":169 grille_cas1(); dcf_cas3(); amortissement()170 print("classeurs écrits dans spreadsheets/ :", sorted(p.name for p in OUT.glob("*.xlsx")))171