Originally posted by @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.
Hello Dion, first, thank you for taking a look at this. Your input is HUGELY valuable. I want to write you a longer reply and will as soon as I get a moment but you are correct in the terminology being wrong and i also have a reason why the numbers are off...I did some quick edits on a 'real' example to mask a real deal that I have not yet closed on. So i tweaked the numbers a little and didnt back into the math....I should have disclosed as such.
Good point on the discount. I guess the way i looked at it is that I built the amort schedule off the 11.5% yield which includes the discounted/extra principal and thus the interest allocation is actually including that bit of principal as well. I am sure there is a better way to account for this but thats how my head wrapped around it.
Agreed on terminology. CF is wrong name, its Interest Allocation. I will change
The benefit of knowing the interest income for me is so I can get an idea of what the interest allocation will look like over time. It lets me see, on average, yearly, what the return i can expect is (not paymen with principal) so i can compare notes. May not be perfect, but its the best way I could wrap my head around this so i can plan for certain income from notes.
Last note, this is a true 180 payment note i am buying as I am buying a partial. The original note is 7%, 360 month but i am buying the next 180 payments.
I will fix the items you call out and upload a new version so it has 'real' numbers and not me tweaking a little here and there. I am not sure why I get nervous about posting the actual file...I will just do it :)
Thank you for the input
Brian
