Mortgages and Amortization

Mortgage and Amortization

Most people acquire their homes using the Mortgages. This form of Financing has been successful in permitting home-ownership to be within the reach of most households. This generally is viewed as being positive and for the most part works in that wealth can be built up through the equity of one's home. Goodman and Mayer (2018) point out that home ownership while presenting risks also offers a means to build wealth. Those groups excluded from Mortgage tend to be marginalized relative to other segments of society and in the United States owning assets also can provide an important cushion if earnings become disrupted. Mortgage finance is generally viewed as a dull area of scholarship but in Vinegar we find this area to be interesting and we will explore some of the basic math behind amortization, wholesale financing and societal effects using Data Analytics and Machine Learning.

After WWII there was an enormous growth of American mortgages debt to GDP. The growth in mortgage debt outstanding in the United States was largerly fueled by securitization and bank deregulation. Maybe also initially by a frustration from the Clinton administration to extend Health insurance universally and being thwarted. As a sop to those excluded from the American dream the Clinton and then Bush administrations saw virtue in promoting home ownership. The U.S. mortgage prior to the 1930s typically featured variable interest rates, high down payments and short maturities. Green and Wachter (2003) pointed out that before the Great Depression, homeowners typically renegotiated their loans every year. In the years that followed WWII, mortgage debt grew inexorably up until the subprime crisis and thereafter mortgage deb declined but seemed to have plateaued. Nevertheless, the cost of mortgage to households can be substantial and Zillow in a recent report points out significant variation in the costs that tend to be elevated in coastal regions of the USA. Also, they point out that relative costs on low income groups can be quite substantial relative to peers. When considering the Fixed Income asset market, the mortgage market is significant not only in size but also in terms of it's impact on the ordinary public.

Mortgage Math and constructing Amortization Tables (VBA code)

A key tool to convey the longevity/intensity of mortgage expense is visible when mapping out the amortization schedule. This table details each periodic payment on an amortizing loan typically for household finance but also common to firms. Amortization outlines process of paying down a debt over maybe a 20 or 30 year term. Uniform regular payments are typically made although many banks lure in customers with teaser rates and there is a cliff perhaps 2 or 3 years in. Payments are composed of interest and principal. The relative proportion of interest relative to principal alters along the amortization schedule. We will examine this in detail below. We start with some quite simple VBA code:

Function MortPayment(Principal, T, m, r)

SQRB = (1 - (1 + r / m) ^ (-T * m)) / (r / m)

MortPayment = Principal / SQRB

End Function

Mortgage Math and automating Amortization Tables (using C++ code)

Andy James Duncan provides an elegant course explaining key concepts in C++. Here we adapt some C++ Andy released on his portal for outputting amortization tables. We follow the numerical example developed above in Excel and VBA. Below we have three videos related to implementing C++ code in Microsoft Visual Studi, Apple Xcode and in Onlinegbd.

// https://andyjamesduncan.wordpress.com/2013/01/15/learn-finance-c-lesson-8-amortizing-engine-part-ii-finished-rough-cut-command-line-execution/

#include <iostream>

#include <cmath>

#include <iomanip>

using namespace std;

// m = periodicity of mortgage payment (periods per year)

// Prin = principal

// r = interest as decimal

// T = maturity of mortgage in years

int main()

{

const int m = 12;

double Prin = 120000;

double r = 0.05;

int T = 30;

double payment = 0.0;

double sqrb = (1 - (pow(1 + r/m,-m*T)))/(r/m);

payment = Prin /sqrb ;

cout << "Mortgage Payment : " << setiosflags(ios::fixed) <<

setprecision(3) << payment << endl;

double CI = 0.0;

double CP = 0.0;

double Bal = 0.0;

Bal = Prin;

long CM = 1;

while(CM <= m*T){

CI = Bal * r/m;

CP = payment - CI;

Bal = Bal - CP;

cout << "CM: " << CM <<

" CI: " << CI <<

" CP: " << CP <<

" Bal: " << Bal <<

endl;

CM++;

}

cout << "Maturity reached." << endl;

return 0;

}

Developing a simple Mortgage estimation App with VBA Userforms

Visual Basic for Applications (VBA) is sufficiently flexible to program apps right inside an Excel worksheet. With this flexibility a neat robust button can be placed inside Excel that has the key attribute of being fat thumb proof. Please see below code and video clip with explanation:

Private Sub CommandButton1_Click()

Unload UserForm1

End Sub

Private Sub CommandButton2_Click()

SQRB = (1 - (1 + r / m) ^ (-T * m)) / (r / m)

MP = Principal / SQRB

End Sub

Javascript Code for Mortgage estimation

Google Sheets offers hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these are not sufficiently crafted to cater for local detail then it is possible to write customized functions and then invoke them in Google Sheets which is an excel analogue. The customized functions are created using standard JavaScript. See below

function MortgagePay(PR, r, n,m) {

var SQR = (1 - Math.pow(1 + r/m, (-n*m)))/(r/m);

var PAY = PR/SQR;

return PAY;

}