Hello. I would be delighted to assist you with this Excel issue.
In actuality, there are many techniques for computing income and margin. It depends upon how you want interest to accrue and what reporting basis you wish to target for tax purposes (like "cost accounting" versus "accrual accounting," that sort of thing).
How much subject matter expertise do you possess in finance in general, and accounting in specific? I ask because I would be happy to steer you into a particular solution path if you're not pre-committed to one for any particular reason(s).
Thank you for your patient cooperation.
Can you provide all the parametric details of the loan?
Do you have something of a spreadsheet already built so that I can just integrate the solution into an existing structure (which reflects YOUR taste and style)? If I do it from scratch, it will look odd: I operate from a scientist's perspective, not an end-user's.
Well, one thing you haven't told me is what the PAYMENTS are on the instrument that you bought. I can't divine that, since I don't know the terms to which the mortgagor agreed--above all, the lifetime of the loan (30 years? 15? 7?).
Also, were those two points points that the mortgagor paid (I suspect not)? Or are you paying two points to the mortgagee (I suspect so) as the price of the loan?
(FYI, I would wonder about a 5% loan that someone is willing to sell you for two points. What's wrong with the borrower? Is he, like, dying of Indonesian foot rot?)
Did you want a table that indicate expected annual income or expected monthly income? At what rate is the mortgagor paying off the instrument?
If you're getting sleepy (I know I am), we can pick this up in the morning. I am usually slamming away by 0700 Eastern time.
Ooh, forgot to ask: you want NIM, but I only see one rate quoted (5%)--other than the "rate" of 2% (which is up-front points, not a rate).
There isn't a NIM in effect unless something is coming in and something (less!) is going out. I would need to know the actual, numeric values of those somethings!
Have a great evening! Cheers.