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 usedis 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 valueof 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 paymentreceived 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 itselfto 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 cannow do a present value calculation and validate the principalbalanceinformation the seller has provided. The PV should be equal tothe 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 aprincipal 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 example2 it is not. All of the four formulas deal with PV either as an input or an output, andgenerally speaking, we use the principal amount/balanceof the loan for this value. It is only when we are dealing with discounting that anumber other than principal balancerepresents our PV value.
Number Of Periods (paymnets) Formula(NPER)
The NPER calculation requires principal amount, rate, and payment amountas 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 newloan amountfor the calculation. When we run the calculation we find that if the borrower continues to make the normal payment without interruptionfrom 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 andmonthly 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, andrate.
Format: PMT(rate, nper, pv)
This calculation is also quite useful for loan modification. Iwon'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 valueswhich supply inputs to the formula are stored in other spreadsheetcells (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!
