What is a mortgage?

Case Study: Mortgage Trading Analysis in Power BI

Nick Edwards

Capital Markets Analyst at Mynd

What is a loan agreement?

A loan agreement is the borrower's promise to repay the money to the lender.

  • Essentially a repayment plan

loan agreement icon with bar chart that trends downward

Case Study: Mortgage Trading Analysis in Power BI

What is a loan agreement?

Parts of a loan agreement:

  • Loan amount
  • Payment
    • Principal
    • Interest

icon of a document

1 https://www.investopedia.com/terms/m/mortgage.asp
Case Study: Mortgage Trading Analysis in Power BI

Principal

  • Principal is the current balance on the loan A graph of payments reducing the principal balance
Case Study: Mortgage Trading Analysis in Power BI

Interest

  • The interest rate is the proportion of interest owed on the principal balance each period.
    • Quoted as an annual rate
Case Study: Mortgage Trading Analysis in Power BI

Interest

  • The interest rate is the proportion of interest owed on the principal balance each period.
    • Quoted as an annual rate
Example

What is the interest due this month on a principal balance of $100,000 at a 5% annual rate?

Interest rate Monthly interest rate Principal Balance Interest owed
5% $100,000
Case Study: Mortgage Trading Analysis in Power BI

Interest

  • The interest rate is the proportion of interest owed on the principal balance each period.
    • Quoted as an annual rate
Example

What is the interest due this month on a principal balance of $100,000 at a 5% annual rate?

Interest rate Monthly interest rate Principal Balance Interest owed
5% 5% ÷ 12 = 0.417% $100,000
Case Study: Mortgage Trading Analysis in Power BI

Interest

  • The interest rate is the proportion of interest owed on the principal balance each period.
    • Quoted as an annual rate
Example

What is the interest due this month on a principal balance of $100,000 at a 5% annual rate?

Interest rate Monthly interest rate Principal Balance Interest owed
5% 5% ÷ 12 = 0.417% $100,000 0.417% x 100,000 = $417
Case Study: Mortgage Trading Analysis in Power BI

Amortizing

graph of principal and interest payments over time

Case Study: Mortgage Trading Analysis in Power BI

Amortizing in Power BI

Syntax

PPMT(<rate>, <per>, <nper>, <pv>[,<fv>,<type>])
  • This produces a negative value!
  • The rate needs to be adjusted!

Example: Find the principal amount on the first payment of a loan with a balance of $100,000 with a 5% annual interest rate and a 360 month term.

PPMT(.05/12, 1, 360, 100000) = ($-120.15)

Click here to review the syntax in further detail

1 https://learn.microsoft.com/en-us/dax/ppmt-function-dax
Case Study: Mortgage Trading Analysis in Power BI

Amortizing in Power BI

2 Steps to Amortize:

  1. Use PPMT() to find the principal portion of a payment
  2. Subtract the principal amount from the current loan balance

Example continue...

[Principal Balance] + [PPMT]

[$100,000] + [-$120.15]

$99,879.85

Case Study: Mortgage Trading Analysis in Power BI

Mortgage qualification

  • Credit score
    • Missed payments reduce score as they show risk
    • Ranges from 300 (lowest) to 850 (perfect)
  • Debt-to-income (DTI) ratio
    • [Monthly debt payments] ÷ [Monthly income]
    • Generally needs to be under 50%
  • Loan-to-value (LTV) ratio
    • [Loan amount] ÷ [Propert value]
    • The lender needs to know they can get their money back in case of foreclosure
    • The lower, the better
1 This is for educational purposes only. Consult your lender for actual terms and qualification guidelines.
Case Study: Mortgage Trading Analysis in Power BI

Let's continue!

Case Study: Mortgage Trading Analysis in Power BI

Preparing Video For Download...