MEDIUM BLOGGING

Calculating a fixed-rate mortgage payment in R

How to Compute Interest and Principal Components of a Mortgage in R

Maggie Wanjiru
Analytics Vidhya
Published in
6 min readAug 1, 2020

--

Source: https://unsplash.com

Mortgage - This is one word that we hear every now and then all over the news, social media and newspapers and probably ponder about the rigorous math and calculations it entails.

Taking a mortgage is such a huge step towards future home ownership. To some, it’s such a big priority in their lives that they start saving money for the loan periodically(monthly, quarterly etc) annually for quite a number of years.

Investopedia.com states that the most popular mortgages are a 30-year fixed and a 15-year fixed. Some mortgages can be as short as five years; some can be 40 years or longer.

Furthermore, stretching payments over more years reduces the monthly payment but increases the amount of interest to pay.

Mortgage Definition

According to Wikipedia, a mortgage loan or simply mortgage is a loan used either by purchasers of real property to raise funds to buy real estate, or alternatively by existing property owners to raise funds for any purpose while putting a lien on the property being mortgaged.

In other basic terms, it is a type of loan from a bank or a financial institution you can use to buy or refinance a home.

Like any other loan, mortgages require one to pay principal plus interest. There are 2 major types of interest rate types; fixed-rate mortgages and adjustable-rate mortgages.

For purposes of this post, we will only focus on the fixed-rate mortgages.

To find out more about adjustable-rate mortgages, consider checking out the below link;

What Is a Fixed-Rate Mortgage Payment?

According to investopedia.com, “With a fixed-rate mortgage, the borrower pays the same interest rate for the life of the loan…If market interest rates rise, the borrower’s payment does not change. If interest rates drop significantly, the borrower may be able to secure that lower rate by refinancing the mortgage. A fixed-rate mortgage is also called a “traditional” mortgage”.

“A refinance occurs when an individual or business revises the interest rate, payment schedule, and terms of a previous credit agreement. Debtors will often choose to refinance a loan agreement when the interest rate environment has substantially changed, causing potential savings on debt payments from a new agreement”, investopedia.com states.

Example

Suppose, hypothetically, you’d like to consider borrowing a mortgage for Kshs. 10 million with a 12% fixed interest rate per annum for a period of 20 years. (At the end of 20 years you’d wish to complete your total mortgage payment). Two major questions pop up.

a) How would you calculate your monthly payments?

b) How much of your monthly payments would go to the interest and principal components per month? (This results to something called an amortization schedule)

If your answers to the 2 questions are no, then let’s dive in together and figure out how we can do that in R. If your answers are yes, then I bet you’d like to learn how to do that in R.

If you are a beginner in R, consider checking out this resource and sooooo many more;

Let the coding begin!

Prerequisite packages

FinancialMath — Financial Mathematics for Actuaries whose authors are Kameron Penn [aut, cre] and Jack Schmidt [aut] made the above computation pretty straight forward.

The package description is as follows; Contains financial math functions and introductory derivative functions included in the Society of Actuaries and Casualty Actuarial Society ‘Financial Mathematics’ exam, and some topics in the ‘Models for Financial Economics’ exam.

To find out more about the package, please see below;

R Code;

# Mortgage payment schedule calculatorlibrary(FinancialMath)#loading the required packagetab1 <- amort.table(Loan=10000000,n=240,pmt=NA,i=0.12/12,ic=1,pf=1,plot=FALSE)  #produces an amortization table for paying off a loan while also solving for either the number of payments, loan amount, or the payment amountwrite.csv(tab1, file  = "tab1.csv")#produce a .csv exceloutput

The above code is what produces our amortization table; which consists of payment, interest paid, principal paid and balance per month.

Definitions of some terms in the code;

  • Loan — loan amount, in this case, Kshs 10 Million
  • n — the number of payments/periods, assuming monthly payments, this is therefore 20*12(20 multiplied by 12 months in a year)
  • pmt — value of level payments. It is NA in our code because it is what we are looking for. It is unknown. NOTE: For the code to run, one of n, pmt, or Loan must be NA (unknown).
  • i — nominal interest rate convertible ic times per year(dividing our interest rate; 12% or 0.12 by 12 means that our fixed rate is good to go for monthly computation. Therefore, monthly interest rate = 0.01 or 1%)
  • ic — interest conversion frequency per year(It is equal to one because we are already working with the converted monthly rate)
  • pf — the payment frequency- number of payments per year(payment is once a month hence the 1)

The following are the first 20 rows of the above code output when produced in a .csv template;

This means that in the first month you will make a monthly payment of Kshs 110,108.6. Out of this payment, Kshs 100,000 will go to the interest component while Kshs 10, 108.61 goes to the principal component. Ideally, the principal and interest payments per row(per month) must equal the monthly payment.

Early in the life of the loan, payments are composed of more interest repaid and less principal repaid. With time, as the proportion of payment that goes to the interest reduces, that for the principal increases.

Applying the same code but changing plot = TRUE, the below graph confirms our numbers;

# Data Visualizationamort.table(Loan=10000000,n=240,pmt=NA,i=0.12/12,ic=1,pf=1,plot=TRUE) #producing the graph with plot = TRUE
Payment towards interest

This process continues until the outstanding principal(the balance)reaches zero and the loan is paid in full.

By the end of the 240th month, you will be paying an interest of Kshs 1,090.18 and a principal amount of Kshs 109,018.43.

To confirm the same, see below the last 20 rows of the payment schedule;

Another graph that shows the above mentioned principal and interest payment movement is shown below;

The beauty of such payment schedules is that they enable one to budget effectively and plan accordingly towards achieving their dream house. This as you have seen is also pretty easy to do in R.

It is important to note that this was a hypothetical calculation. There are other factors that might alter this calculation such as prepayment risk. This is the risk involved with the premature return of principal on a fixed-income security. If in any scenario, there are lower interest rates, it is profitable for the borrower to prepay as this enables the borrower save money. Several factors influence this such as level of interest rate, change in borrower’s profile, housing prices etc.

Thanks for reading!

For any queries, comments or feedback, feel free to reach me on Twitter; https://twitter.com/magwanjiru

Disclaimer: This is a personal blog. The views and opinions expressed in this blog are those of the authors and do not necessarily reflect the official policy or any other party.This article is for informational purposes only; please consult your advisor to determine whether this strategy is right for you.

--

--

Maggie Wanjiru
Analytics Vidhya

Data storyteller | Nature Lover | Aspiring photographer