Note Analysis File

[Brian Larson]

I have uploaded a file that you might find useful. This is similar to my BRRR Calculator but trimmed down due to notes being a little more straight forward (at least from a variable/output perspective).

I have certain things I want to know about a note before purchasing so this allows me to enter the details (bold items) and get a good picture of its value and return over a period of time. 

Here is a screen shot (note that there is also an amortization data tab that has the raw details):

Please note that I have my own stoplight thresholds that you can change to meet your needs. I also only calculated the first 10 years of average cashflow all up in order to get a picture of what it will look like. 

Last note, I come from the position that you do not calculate the principal as cashflow so that is not included in the calculations. 

Would love to know what others use or factors they like to have in and I can evolve this over time. I hope someone finds it useful. 

The file is here

[Dion DePaoli]

Brian,

Kudos for putting this out there.  A couple thoughts.  If the model is covering investing in discounted loans then you actually need to put the principal back in.  Only in a par loan purchase would the borrower's principal be equal to the capital of the investor.  So at a discount, a portion of the borrower's principal is actually investor return.  That is what the discount does.  So your model short stated the return at the price for this loan paying to maturity on all 180 payments - it is actually 12.15%.  Not 11.50%.  

In addition to the above ideas, it looks like this loan is being purchased in period 17.  So the real return here with 164 periods left is 11.46%.  I can not tell where you computed the 11.5% return.  It looks like a user input.  Is that suppose to be a hurdle of some kind?  

OK, I figured out what you did.  I am not fond of that approach.  You input the "Interest Rate" which is really a desired rate of return.  You also input the purchase price.  You miss used 180 payments, the loan is seasoned.  Making the "Amortization Data" gave you the wrong answer because you choose inputs instead of the loan giving you inputs.  As I stated above, the actual rate of return is 12.5% on the whole loan.  You made it 11.5% by choosing 11.5% as an input.  The issue is further illustrated in the difference between the two payments.  The actual loan payment is $665.30.  You recalculated the loan payment at $642.50.  So you have shorted yourself $22.50 each period.  

The field names in the outputs are misleading.  You are not showing the "Cash Flow (Year X)" you are showing the average interest allocation from the borrower's payment.  You know what it is, but others will not without explanation.  Yield diminishes as we approach maturity in some circumstances however "cash flow" on a fixed rate loan actually remains the same.  

I am curious, what benefit do you gain from knowing the average interest income per period in any given year?  

Investment To Yield is not proper.  You are taking the Note Purchase price and dividing it by Property Sale Price (at origination).  So that field is more appropriately defined as Investment to RE Value or something of that sorts.  There is no yield in your calculation.  

I think you are headed in the right direction, just need to fix the approach a bit.  You don't want the model to be based on your inputs, you want it to be based on the actual underlying loan.