Mortgage Calculation and Adjustable Rate Mortgages(ARMs)

Mortgage Calculations and Financial Calculators

Important Terminology

  • Amortization Period: The time span over which the payment amount is spread to calculate loan payments.
  • Loan Term (or Loan Maturity): The duration for which the loan is active and must be paid off. For residential mortgages, the amortization period and loan term are usually the same length. However, these can have different time spans for commercial mortgages.
  • Fully Amortizing Loan: A loan where the amortization period and the loan term are equal, common for residential loans.
  • Partially Amortizing Loan: A loan where the loan term is shorter than the amortization period. This results in smaller payments, but requires a balloon payment at the end of the term. Banks generally prefer shorter terms for commercial loans.

Financial Calculator Basics

  • Shift Buttons:
    • Blue Shift: Accesses functions written in blue above the calculator keys.
    • Orange Shift: Accesses functions written in orange above the calculator keys (most commonly used for financial calculations).

Note: When the transcript mentions 'shift', it refers to the orange shift button..

  • Adjusting Decimal Places:

    • Enter the desired number of decimal places, then press Shift + Equals.
    • Example: 6 Shift + Equals displays six decimal places.
    • Example: 2 Shift + Equals displays two decimal places.

  • Negative Numbers:

    • Use the +/- key to change a number between positive and negative.

  • Clearing Memory:

    • C key: Clears the current display.
    • Shift + C (Clear All): Clears all stored memory.

  • Payments per Year:

    • Set the number of payments per year using Shift + PMT.
    • 1 Shift + PMT: Sets to one payment per year.
    • 12 Shift + PMT: Sets to twelve payments per year (typically for mortgages).

  • End Mode vs. Begin Mode:

    • Default mode is End Mode.
    • Switch to Begin Mode using Shift + MAR (shows "BEG" at the bottom of the display).
    • Switch back to End Mode using Shift + MAR.

  • Arrow Key:

    • Use the back arrow key to delete the last entered digit.
Financial Functions Keys (Top Row)
  • N: Number of payments.
  • I/YR (referred to as I): Interest rate per year.
  • PV: Present Value.
  • PMT: Payment amount.
  • FV: Future Value.
  • CFJ: Uneven cash flows.
  • Shift + PRC (NPV): Net Present Value (for uneven cash flows).
  • Shift + CST (IRR): Internal Rate of Return (for uneven cash flows).
  • Shift + FV (amort): Amortization function for calculating remaining balances, in conjunction with the Input Key.

Basic Mortgage Calculations on a Financial Calculator

Calculating Affordable Loan Amount Based on Desired Payment
  • Determine monthly payment affordability.
  • Input loan term, interest rate, and desired payment into the calculator.
  • Compute the present value (PV) to find the affordable loan amount.

Example: 30-year loan at 6% interest with a desired payment of $1,000 per month:

  1. Calculate the total number of payments: 30×12=36030 \times 12 = 360. Store this value into N.
  2. Enter the annual interest rate: 66. Store this value into I.
  3. Enter the monthly payment: 10001000. Store this value into PMT.
  4. Compute the present value by pressing PV. Result: $166,000\$166,000 (the maximum affordable loan).
Calculating Payment Amount
  • Input the loan amount (as a negative number), loan term, and interest rate.
  • Compute the payment amount (PMT).

Example: Calculating payment for a \$100,000 loan:

  1. Enter the loan amount as a negative number: −100,000-100,000. Store this value into PV.
  2. Enter the interest rate: 7%7 \%. Store this value into I.
  3. Enter the number of payments (e.g., 30 years ×\times 12 months = 360): Store this value into N.
  4. Compute the payment amount by pressing PMT. Result: $843.86\$843.86.
Calculating Remaining Balance
  • Use the amortization function (Shift + FV).
  • Input the period for which you want to find the remaining balance, and press the Input key, then Shift + FV.
  • The calculator will display the breakdown of principal, interest, and remaining balance.

Example: Finding the balance after one month on a \$100,000 loan:

  1. After storing the loan details in the calculator (N, I, PV, PMT as above), enter 1, then press Input, and then Shift + FV.
  2. Press Equals to cycle through: Principal paid during the first month, Interest paid during the first month, and the Remaining balance after the first month.

Example: Finding the balance after five years (60 months):

  1. Enter 60, the press Input, and then Shift + FV.
  2. Press Equals multiple times to view the principal paid during the 60th month, interest paid during the 60th month, and the remaining balance after 60 months.

Important Note: A common mistake is to simply subtract the sum of payments from the original loan balance which does not account for interest..

Partially Amortizing Loans and Balloon Payments

Example: Calculating balloon payment after four years on a 30-year amortization schedule:

  1. Input loan amount: $100,000\$100,000 (PV).
  2. Input loan term: 30×1230 \times 12 (N).
  3. Input interest rate: 7 \%$ (I).
  4. Calculate payment (PMT).
  5. To find the balance after four years, enter 4 \times 12(<strong>INPUT</strong><strong>Shift</strong>+<strong>FV</strong>(<strong>AMORT</strong>)).</li><li>Pressequalsuntiltheremainingbalanceisdisplay(e.g.,(<strong>INPUT</strong> <strong>Shift</strong> + <strong>FV</strong> (<strong>AMORT</strong>)).</li> <li>Press equals until the remaining balance is display (e.g.,\$95,000).</li></ol><h4id="interestonlyloansandadjustableratemortgagesarms">Interest−OnlyLoansandAdjustableRateMortgages(ARMs)</h4><h5id="adjustableratemortgagesarms">AdjustableRateMortgages(ARMs)</h5><ul><li><p><strong>ARMTerminology:</strong></p><ul><li><strong>3/1ARM:</strong>Fixedinterestrateforthefirstthreeyears,thenadjustsannually.</li><li><strong>5/1ARM:</strong>Fixedinterestrateforthefirstfiveyears,thenadjustsannually.</li></ul></li><li><p><strong>KeyConsiderationsforARMs:</strong></p><ul><li><strong>PrepaymentPenalties:</strong>OftenfoundinARMs,butlesscommoninfixed−ratemortgages.Thesepenaltiesareputinplacesothatthebankcancontinuetogenerateinterestfromthem.</li><li><strong>TeaserRates:</strong>Lowinitialinterestratesofferedforashortperiod,enticingborrowers.Goodaslongastheborrowerunderstandswhattherealrateis,andwhattheotherimplicationsare.</li><li><strong>IndexRate:</strong>ThebenchmarkratetowhichtheARMinterestrateistied.Itfluctuatesbasedonmarketconditions(PrimeRate,USTreasuryRate,ConsumerPriceIndex,etc.).</li><li><strong>Margin:</strong>Afixedpercentageaddedtotheindexratetodeterminetheloan′sinterestrate.Themarginremainsconstantthroughoutthelifeoftheloan.</li><li><strong>Caps:</strong><ul><li><strong>AnnualCap:</strong>Limitstheamounttheinterestratecanchangefromyeartoyear.</li><li><strong>LifetimeCap:</strong>Limitsthetotalamounttheinterestratecanchangeoverthelifeoftheloan.</li><li>Itisimportanttounderstandifthesecapsarealsoappliedduringtheteaserrate,becausewithoutacaporanyrestrictionsontheannualcap,itcouldgreatlyaffectthelongtermfinancialsoftheloan.</li></ul></li></ul></li></ul><h5id="exampleofanarmwithoutcaps">ExampleofanARMwithoutCaps</h5><ul><li><p>Loanamount:).</li> </ol> <h4 id="interestonlyloansandadjustableratemortgagesarms">Interest-Only Loans and Adjustable Rate Mortgages (ARMs)</h4> <h5 id="adjustableratemortgagesarms">Adjustable Rate Mortgages (ARMs)</h5> <ul> <li><p><strong>ARM Terminology:</strong></p> <ul> <li><strong>3/1 ARM:</strong> Fixed interest rate for the first three years, then adjusts annually.</li> <li><strong>5/1 ARM:</strong> Fixed interest rate for the first five years, then adjusts annually.</li></ul></li> <li><p><strong>Key Considerations for ARMs:</strong></p> <ul> <li><strong>Prepayment Penalties:</strong> Often found in ARMs, but less common in fixed-rate mortgages. These penalties are put in place so that the bank can continue to generate interest from them.</li> <li><strong>Teaser Rates:</strong> Low initial interest rates offered for a short period, enticing borrowers. Good as long as the borrower understands what the real rate is, and what the other implications are.</li> <li><strong>Index Rate:</strong> The benchmark rate to which the ARM interest rate is tied. It fluctuates based on market conditions (Prime Rate, US Treasury Rate, Consumer Price Index, etc.).</li> <li><strong>Margin:</strong> A fixed percentage added to the index rate to determine the loan's interest rate. The margin remains constant throughout the life of the loan.</li> <li><strong>Caps:</strong><ul> <li><strong>Annual Cap:</strong> Limits the amount the interest rate can change from year to year.</li> <li><strong>Lifetime Cap:</strong> Limits the total amount the interest rate can change over the life of the loan.</li> <li>It is important to understand if these caps are also applied during the teaser rate, because without a cap or any restrictions on the annual cap, it could greatly affect the long term financials of the loan.</li></ul></li></ul></li> </ul> <h5 id="exampleofanarmwithoutcaps">Example of an ARM without Caps</h5> <ul> <li><p>Loan amount:\$100,000,30−yearterm,margin:2.75<ul><li><strong>Year1:</strong>Indexrate3.25, 30-year term, margin: 2.75% (275 basis points), no caps.</p> <ul> <li><strong>Year 1:</strong> Index rate 3.25%, teaser rate 4.5%. Monthly payment:\$507.</li><li><strong>Year2:</strong>Indexrate3.25.</li> <li><strong>Year 2:</strong> Index rate 3.25%, margin 2.75%, interest rate 6%. Monthly payment:\$600.</li><li><strong>Year3:</strong>Indexchangesto3.5.</li> <li><strong>Year 3:</strong> Index changes to 3.5%, interest rate 6.25%. Monthly payment:\$613.</li></ul></li></ul><h5id="exampleofanarmwithcaps">ExampleofanARMwithCaps</h5><ul><li><p>Termsarethesameasabovebutincludesa1<ul><li><strong>Year1:</strong>Index3.25.</li></ul></li> </ul> <h5 id="exampleofanarmwithcaps">Example of an ARM with Caps</h5> <ul> <li><p>Terms are the same as above but includes a 1% annual cap. The lifetime cap is equal to 9.5%.</p> <ul> <li><strong>Year 1:</strong> Index 3.25%, teaser rate 4.5%. Monthly payment:\$507.</li><li><strong>Year2:</strong>Interestrateiscappedat5.5.</li> <li><strong>Year 2:</strong> Interest rate is capped at 5.5% (4.5% + 1%). Monthly payment:\$566.</li><li><strong>Year3:</strong>Interestrate6.25.</li> <li><strong>Year 3:</strong> Interest rate 6.25%. Monthly payment:\$611$$.
    6. Important Considerations for ARMs
    • Always read the loan terms carefully.
    • Know the index, margin, annual cap, and lifetime cap.
    • Understand upfront financing charges.
    • Determine if there are any charges associated with the caps.

    Mortgage Calculations in Excel

    To keep formulas reading correctly, and prevent against any potential issues, instructor grading issues, be gentle on them, and do not format it as a table.

    Basic Setup for Financing Box
    1. Consolidate all loan information into one place so if the loan is updated, the changes can easily be made.
    2. Insert headings for everything necessary.
    3. Surround everything with box, and make all given cells that are not calculations a shade of gray.
    • Interest Rate
    • Amortization Period (Years)
    • Loan Term (Years)
    • Loan to Value Ratio
    • Debt Cover Ratio
    • Final Loan Amount
    • Monthly Payment Amount
    • Annual Payment Amount
    Property Information
    • Purchase Price
    • Year One NOI
    Debt Retirement
    • End Period
    • Loan Balance
    Example Scenario
    • Purchase a property for $5,250,000, year one NOI of $275,645.
    • A fixed rate mortgage loan with a 5 year term, but amortizes over 20 years, interest rate of 3.15%.
    • Loan amount is the lesser of 65% loan to value or a 1.35 debt cover ratio.
    • You want to know what your loan amount is, along with your monthly and annual payments are.
    • What is your loan balance after the first payment? What is your balloon payoff if the loan reaches maturity?
    Calculate the Loan to Value Ratio
    • Simplest calculation, will be the Loan to Value * Purchase Price.
    Calculate the Loan Option Based on Debt Cover Ratio
    • Loan Option 2, will manipulate the year one NOI to find, because this is how we work with debt cover ratios.
    • The formula is: = -PV(Interest Rate / 12, Amortization Period * 12, Year One NOI / Debt Cover Ratio / 12
      Note: Excel does not have decimal places properly, it is best to drop off the decimal places..
    Calculate the Monthly Payment
    • Use the payment function in excel: PMT(Interest Rate/12, Amortization Period * 12, -Loan Amount)
    Calculate the Annual Payment
    • Can be done in one of two ways, take the monthly * 12, or use that payment function again.
    • Using that function will look like this: PMT(Interest Rate/12, Amortization Period * 12, -Loan Amount) * 12
    Calculating Remaining Balance
    Cume Prints Method
    • Most preferred, and advanced way to calculate this.
    • The formula is: =Loan Amount - CUMPRINTS(Interest Rate/12, Amortization Period * 12, Loan Amount, 1, End Period, 0
      Note: Not including a zero at the end the formula will fail..

    Notes on Refinancing Loans

    • More will be discussed in another module.