26c_mortgage_interest.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mortgage Interest Deductibility"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: numpy_financial in d:\\bbcx-investments-quarto\\quarto\\venv\\lib\\site-packages (1.0.0)\n",
      "Requirement already satisfied: numpy>=1.15 in d:\\bbcx-investments-quarto\\quarto\\venv\\lib\\site-packages (from numpy_financial) (1.23.2)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip available: 22.2.1 -> 23.3.2\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "!pip install numpy_financial\n",
    "import numpy_financial as npf\n",
    "import plotly.graph_objects as go\n",
    "import plotly.express as px\n",
    "pd.options.display.float_format = '{:,.2f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2,684.11\n"
     ]
    }
   ],
   "source": [
    "PRINCIPAL = 500000\n",
    "RATE = 0.05\n",
    "TERM = 360\n",
    "\n",
    "pmt = npf.pmt(RATE/12,TERM,-PRINCIPAL)\n",
    "print(f'{pmt:,.2f}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>beg_bal</th>\n",
       "      <th>interest</th>\n",
       "      <th>principal</th>\n",
       "      <th>end_bal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>360 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     beg_bal  interest  principal  end_bal\n",
       "1        NaN       NaN        NaN      NaN\n",
       "2        NaN       NaN        NaN      NaN\n",
       "3        NaN       NaN        NaN      NaN\n",
       "4        NaN       NaN        NaN      NaN\n",
       "5        NaN       NaN        NaN      NaN\n",
       "..       ...       ...        ...      ...\n",
       "356      NaN       NaN        NaN      NaN\n",
       "357      NaN       NaN        NaN      NaN\n",
       "358      NaN       NaN        NaN      NaN\n",
       "359      NaN       NaN        NaN      NaN\n",
       "360      NaN       NaN        NaN      NaN\n",
       "\n",
       "[360 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "cols = ['beg_bal','interest','principal','end_bal']\n",
    "df = pd.DataFrame(dtype=float, columns=cols, index=1+np.arange(TERM))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>beg_bal</th>\n",
       "      <th>interest</th>\n",
       "      <th>principal</th>\n",
       "      <th>end_bal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>500,000.00</td>\n",
       "      <td>2,083.33</td>\n",
       "      <td>600.77</td>\n",
       "      <td>499,399.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>499,399.23</td>\n",
       "      <td>2,080.83</td>\n",
       "      <td>603.28</td>\n",
       "      <td>498,795.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>498,795.95</td>\n",
       "      <td>2,078.32</td>\n",
       "      <td>605.79</td>\n",
       "      <td>498,190.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>498,190.16</td>\n",
       "      <td>2,075.79</td>\n",
       "      <td>608.32</td>\n",
       "      <td>497,581.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>497,581.84</td>\n",
       "      <td>2,073.26</td>\n",
       "      <td>610.85</td>\n",
       "      <td>496,970.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>13,254.40</td>\n",
       "      <td>55.23</td>\n",
       "      <td>2,628.88</td>\n",
       "      <td>10,625.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>10,625.52</td>\n",
       "      <td>44.27</td>\n",
       "      <td>2,639.84</td>\n",
       "      <td>7,985.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>7,985.68</td>\n",
       "      <td>33.27</td>\n",
       "      <td>2,650.83</td>\n",
       "      <td>5,334.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>5,334.85</td>\n",
       "      <td>22.23</td>\n",
       "      <td>2,661.88</td>\n",
       "      <td>2,672.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>2,672.97</td>\n",
       "      <td>11.14</td>\n",
       "      <td>2,672.97</td>\n",
       "      <td>-0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>360 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       beg_bal  interest  principal    end_bal\n",
       "1   500,000.00  2,083.33     600.77 499,399.23\n",
       "2   499,399.23  2,080.83     603.28 498,795.95\n",
       "3   498,795.95  2,078.32     605.79 498,190.16\n",
       "4   498,190.16  2,075.79     608.32 497,581.84\n",
       "5   497,581.84  2,073.26     610.85 496,970.99\n",
       "..         ...       ...        ...        ...\n",
       "356  13,254.40     55.23   2,628.88  10,625.52\n",
       "357  10,625.52     44.27   2,639.84   7,985.68\n",
       "358   7,985.68     33.27   2,650.83   5,334.85\n",
       "359   5,334.85     22.23   2,661.88   2,672.97\n",
       "360   2,672.97     11.14   2,672.97      -0.00\n",
       "\n",
       "[360 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for t in df.index:\n",
    "    if t==1:\n",
    "        df.loc[t,'beg_bal'] = PRINCIPAL\n",
    "    else:\n",
    "        df.loc[t,'beg_bal'] = df.loc[t-1,'end_bal']\n",
    "    df.loc[t,'interest']  = RATE/12*df.loc[t,'beg_bal']\n",
    "    df.loc[t,'principal'] = pmt - df.loc[t,'interest']\n",
    "    df.loc[t,'end_bal']   = df.loc[t,'beg_bal']-df.loc[t,'principal']\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>beg_bal</th>\n",
       "      <th>interest</th>\n",
       "      <th>principal</th>\n",
       "      <th>end_bal</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>500,000.00</td>\n",
       "      <td>2,083.33</td>\n",
       "      <td>600.77</td>\n",
       "      <td>499,399.23</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>499,399.23</td>\n",
       "      <td>2,080.83</td>\n",
       "      <td>603.28</td>\n",
       "      <td>498,795.95</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>498,795.95</td>\n",
       "      <td>2,078.32</td>\n",
       "      <td>605.79</td>\n",
       "      <td>498,190.16</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>498,190.16</td>\n",
       "      <td>2,075.79</td>\n",
       "      <td>608.32</td>\n",
       "      <td>497,581.84</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>497,581.84</td>\n",
       "      <td>2,073.26</td>\n",
       "      <td>610.85</td>\n",
       "      <td>496,970.99</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>496,970.99</td>\n",
       "      <td>2,070.71</td>\n",
       "      <td>613.40</td>\n",
       "      <td>496,357.59</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>496,357.59</td>\n",
       "      <td>2,068.16</td>\n",
       "      <td>615.95</td>\n",
       "      <td>495,741.64</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>495,741.64</td>\n",
       "      <td>2,065.59</td>\n",
       "      <td>618.52</td>\n",
       "      <td>495,123.12</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>495,123.12</td>\n",
       "      <td>2,063.01</td>\n",
       "      <td>621.10</td>\n",
       "      <td>494,502.03</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>494,502.03</td>\n",
       "      <td>2,060.43</td>\n",
       "      <td>623.68</td>\n",
       "      <td>493,878.35</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>493,878.35</td>\n",
       "      <td>2,057.83</td>\n",
       "      <td>626.28</td>\n",
       "      <td>493,252.06</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>493,252.06</td>\n",
       "      <td>2,055.22</td>\n",
       "      <td>628.89</td>\n",
       "      <td>492,623.17</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>492,623.17</td>\n",
       "      <td>2,052.60</td>\n",
       "      <td>631.51</td>\n",
       "      <td>491,991.66</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      beg_bal  interest  principal    end_bal  year\n",
       "1  500,000.00  2,083.33     600.77 499,399.23  0.00\n",
       "2  499,399.23  2,080.83     603.28 498,795.95  0.00\n",
       "3  498,795.95  2,078.32     605.79 498,190.16  0.00\n",
       "4  498,190.16  2,075.79     608.32 497,581.84  0.00\n",
       "5  497,581.84  2,073.26     610.85 496,970.99  0.00\n",
       "6  496,970.99  2,070.71     613.40 496,357.59  0.00\n",
       "7  496,357.59  2,068.16     615.95 495,741.64  0.00\n",
       "8  495,741.64  2,065.59     618.52 495,123.12  0.00\n",
       "9  495,123.12  2,063.01     621.10 494,502.03  0.00\n",
       "10 494,502.03  2,060.43     623.68 493,878.35  0.00\n",
       "11 493,878.35  2,057.83     626.28 493,252.06  0.00\n",
       "12 493,252.06  2,055.22     628.89 492,623.17  0.00\n",
       "13 492,623.17  2,052.60     631.51 491,991.66  1.00"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check interest per year\n",
    "df['year'] = np.floor((df.index-1)/12)\n",
    "df.head(13)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year\n",
       "0.00    24,832.47\n",
       "1.00    24,455.06\n",
       "2.00    24,058.34\n",
       "3.00    23,641.32\n",
       "4.00    23,202.96\n",
       "5.00    22,742.18\n",
       "6.00    22,257.83\n",
       "7.00    21,748.69\n",
       "8.00    21,213.51\n",
       "9.00    20,650.94\n",
       "10.00   20,059.59\n",
       "11.00   19,437.99\n",
       "12.00   18,784.59\n",
       "13.00   18,097.75\n",
       "14.00   17,375.78\n",
       "15.00   16,616.87\n",
       "16.00   15,819.13\n",
       "17.00   14,980.58\n",
       "18.00   14,099.13\n",
       "19.00   13,172.57\n",
       "20.00   12,198.62\n",
       "21.00   11,174.84\n",
       "22.00   10,098.67\n",
       "23.00    8,967.45\n",
       "24.00    7,778.35\n",
       "25.00    6,528.42\n",
       "26.00    5,214.54\n",
       "27.00    3,833.44\n",
       "28.00    2,381.67\n",
       "29.00      855.63\n",
       "Name: interest, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('year').interest.sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Incremental tax savings of itemizing deductions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "ANNUAL_MTG_INTEREST = 24832.47\n",
    "STANDARD_DEDUCTION  = 12950\n",
    "MARGINAL_TAX_RATE   = 0.24"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tax Savings With Standard Deduction:\t\t 3,108.00\n",
      "Tax Savings With Itemized Deduction:\t\t 5,959.79\n",
      "Incremental Savings w/ Itemized Deduction:\t 2,851.79\n"
     ]
    }
   ],
   "source": [
    "tax_savings_std = MARGINAL_TAX_RATE * STANDARD_DEDUCTION\n",
    "print(f'Tax Savings With Standard Deduction:\\t\\t {tax_savings_std:,.2f}')\n",
    "\n",
    "tax_savings_mtg = MARGINAL_TAX_RATE * ANNUAL_MTG_INTEREST\n",
    "print(f'Tax Savings With Itemized Deduction:\\t\\t {tax_savings_mtg:,.2f}')\n",
    "\n",
    "print(f'Incremental Savings w/ Itemized Deduction:\\t {tax_savings_mtg-tax_savings_std:,.2f}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.6 ('venv': venv)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "cc788b38d8cb434c82f45ef9d481d0310e17b79321e4c8cbfbfe90f656a3f023"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}