My question
I have a question on how to model the cashflows of fixed-rate loans or mortgages.
Let's say the payments are monthly, and the rate remains constant throughout the life of the product; each month the borrower(s) will pay the same amount, easily calculated with the pmt() formula available in most spreadsheets or finance libraries (Excel, numpy_financial, Matlab, etc); as time goes by, the amount paid each month remains the same, but the interest portion of that payment goes down, and the principal portion goes up.
There are loads of examples everywhere, like in the Excel link above, but these examples are all based on periods of equal length, effectively assuming a 30/360 daycount convention. In reality, (maybe not in all but in many countries at least) most consumer loans/mortgages calculate interest daily based on act/365. How do you model this? How do you handle the fact that, this way, it might take one more period to fully amortise the loan (see example below)?
Practical example
Let's consider a 100,000 loan (I am intentionally ignoring the currency as it's irrelevant) for a 5-year period at 10% per annum. If we assume months of equal length, so that the interest in each month will be = balance * rate /12
, then, well, of course by the end of month 60 the balance is zero.
If, however, we consider act/365, keep the total payment constant, and simply calculate principal amortisation = total payment - interest payment
, then, by the end of month 60, the balance won't be zero, but 5.96 - see screenshot below:
Options
I can only think of the following options:
- ignore act/365 and calculate the interest assuming every period has the same length (i.e. 30/360), even though that's not what happens
- Assume one extra period (in the screenshot above, there is a small final payment in month 61)
- Don't assume an extra period, but assume that the final payment may be slightly larger than the other (5.96 more at month 60 in the example)
- Recalculate the total payment each month
Of these, 1. and 3. seem the least worst, so to speak. Probably 3 is better than 1, especially if this modelling is the basis of some kind of ABS/RMBS model, because it would mean the assets (the loans/mortgages) pay interest on the same basis as the liabilities (the bonds backed by those assets) - assuming of course the bonds are act/365.
Thoughts?