Doing the Math on Mortgage Notes

Introduction

If you are investing in notes, it is essential the you know how to do the related math.  There are a handful of basic financial calculations which I use personally on a daily basis.  While the math behind the calculations can be done by hand with some knowledge of advanced math, there's no need to take this on when we have financial calculators and spreadsheets with these functions built in.  Personally, I prefer to use a spreadsheet because the calculations can be utilized as part of a larger analysis around a note or a list of notes, so in this article I will provide guidance on the use of the MS Excel formulas which implement these calculations.

Four Basic Calculations

As a first step, let's dive into an explanation of the 4 primary financial calculations; Present Value (PV), Number of Payments (NPER), Rate (RATE), and Payment (PMT).  These go together as a set.  If you have any three of the values you can calculate the value for the fourth.

Present Value Formula (PV)

The PV formula looks at a series of payments made over time and calculates the present day value of these payments using a discount rate.  The formula requires rate, number of payments, and payment amount as inputs. 

         Format: PV(rate, nper, pmt)

To calculate the present value of a particular note, the discount rate used is the interest rate for the loan.  For example, let's say we have a 25K loan at 10% paid monthly over a 5 year term (60 payments).  If you add up all of these payments, you get $31,866.18 (total payout over the life of the loan).  The present value of the loan, however, is lower than the total payout value because of the fact that payments received in the future are not worth as much in todays dollars as a payment received today.  The PV of our example loan at the beginning is actually 25K, the same as its original principal balance, and decreases along with the principal balance as payments are made.

So how can the PV calculation be used? There are many scenarios, but let's walk through a couple of common ones.

Example 1 - Due Diligence Verification

Let's say you are looking at purchasing a note and the seller has provided principal balance and next payment due.  You look at the note itself to get the rate and payment amount.  You can then use the next payment due date to determine number of payments remaining until the maturity date.  With this information in hand, you can now do a present value calculation and validate the principal balance information the seller has provided.  The PV should be equal to the principal balance of the loan.

Example 2 - Pricing for Desired Yield

PV calculations can also be used to determine purchase price when selling/buying a note at a specific desired investment yield to the buyer.  Let's say that we have an existing loan which has 57 remaining payments of $500 @ 9% interest rate and a principal balance of $23,121.43.  We want to purchase the note at a discount to achieve a yield of 12%.  To find the price we need to pay to achieve this yield, we simply plug in the remaining payments, the payment amount, and use 12% for the rate.  The resulting PV is our purchase price (PV @ 12% = $21,643.56).

Key Concept for PV

Notice that in example 1 above, PV is equal to the principal balance while in example 2 it is not.  All of the four formulas deal with PV either as an input or an output, and generally speaking, we use the principal amount/balance of the loan for this value.  It is only when we are dealing with discounting that a number other than principal balance represents our PV value.

Number Of Periods (paymnets) Formula (NPER)

The NPER calculation requires principal amount, rate, and payment amount as inputs, and returns the number of monthly payments (periods) required to pay off the loan.

         Format: NPER(rate, pmt, pv)

This can be helpful in cases where the borrower had stopped making payments for a time and has accrued interest and/or advances that need to be paid in addition to their principal balance.

Example - Calculate Actual Loan Term

Let’s say we have a 50K loan amortized over 10 years (120 payments) at 9% with a payment amount of $633.38.  The borrower had made 24 payments, then stopped paying for a year.  The principal balance after the two years of payments is $43,233.45, but they now have additional accrued interest of $3891.  So we add the accrued interest to our principal balance to get our new loan amount for the calculation.  When we run the calculation we find that if the borrower continues to make the normal payment without interruption from this point forward, they will need to make 109 payments to pay off the loan rather than the 96 payments that remain on the original amortization schedule.

Rate (RATE)

The RATE calculation requires loan amount, number of payments, and payment amount as inputs. 

         Format: RATE(nper, pmt, pv) * 12

This calculation is great for determining investment yield when purchasing notes at a discount with a predefined price.  To do this, we simply use the asking price as the loan amount and plug in remaining payments and monthly P&I payment amount.  In the same manner, it can also be used to determine the yield of a loan modification when the note was originally purchased as an NPN at a discount.  In this case, we use our cost basis for the note (purchase price  + expenses) as our loan amount PV.  The rate, number of payments, and payment amount come from the new note terms you are considering for the mod.

Payment (PMT)

The PMT calculation requires loan amount, number of payments, and rate.

         Format: PMT(rate, nper, pv)

This calculation is also quite useful for loan modification.  I won't bother with examples on this, as by now I think you probably have the idea.

Using Financial Calculation Formulas in MS Excel

There are a couple of tricks to using these functions properly in Excel.I will assume you are familiar with using Excel formulas generally.If not, there is plenty of information online.

Tip #1

Excel expects the rate (RATE) value used in the formula to be a monthly rate. So if you have an annual rate value in your spreadsheet you will need to divide it by 12 inside the formula.

Tip #2

Excel expects the payment (PMT) value used in a formula to be a negative number.  If you are referencing the value in another cell, you will need to put a minus sign in front of the cell reference.

Example - Using PV formula in Excel

Applying both tips above, here's how the PV formula should look when your values which supply inputs to the formula are stored in other spreadsheet cells (B4 = RATE, A4 = NPER, and C4=PMT).

=PV(B4/12,A4,-C4)

Summary

The basic calculations reviewed here are absolutely essential to note investing, not to mention standard real estate investing.  Learning how to use them properly and incorporate them into your analysis spreadsheets will allow you to invest with a much greater level of confidence.  I suspect that some of the explanations above may be a little confusing for those without much experience using formulas in MS Excel, so I have uploaded a sample spreadsheet which shows the 4 formulas in action.  One other thing that is just as essential is a good amortization schedule.  Fortunately Microsoft makes one available as downloadable template.  Enjoy!