Dear Investor Juan,
I hope everything is well with you.
Are you familiar with loan amortization? My wife and I have a housing loan with a term of 20 years. Our goal is to shortened that to at most 15 years. We have discussed this with our bank and they said that they don’t usually reconstruct loan. We can try but it has a high probability that we will not be approved. So they suggested to us to pay in lump sum every annual repricing. There is no minimum amount for the lump sum payment but their suggestion is it has to be at least 6 times of our monthly amortization if possible so that it can also somehow lower our amortization. It will probably take us 2-3 years to save that amount of money (around 100k). Is it wise to do that or paying in lump sum every year even if it is only 30-50k a wiser move?
We got a fixed interest rate for the first 3 years. I can easily compute the amortization in Excel via the Loan Amortization template. I don’t know what will be the computation for the repricing in the 4th year and onwards that is way I can’t come up which is better. J
Here is the sample data that I used in computing for the amortization for Year 1 to 3 using Excel.
Loan Amount: 2,000,000
Annual interest rate: 7.88%
Loan period in years: 20
Number of payments per year: 12
Is this the correct computation for year 4?
Loan Amount: 1,860,550.26
Annual interest rate: 8%
Loan period in years: 17 ???
Number of payments per year: 12
Thank you so much.
Danison
Dear Danison,
So your objective is to shorten the term of your housing loan from 20 to 15 years by either paying a lump sum of 100,000 on the fourth year or annual payments of 30,000 to 50,000.
.png)
Using the information you have provided me, I have reconstructed your loan amortization schedule here. You have a 20-year loan with an annual interest rate of 7.88% (we'll get back to this later). Using the PMT function of Google Spreadsheet (same as in Excel; "Current" worksheet, double-click cell B4 to see the inputs), we can compute for the monthly amortization of your loan: 16,580 pesos per month. If you paid down payment on your loan, you should actually deduct it from the loan amount, and it will give you a lower amortization. Also, the resulting amount does not include insurance and other fees, so it may differ a bit from what your bank quoted.
The monthly amortization amount is what you have to pay every month to pay off your loan. Every month, a portion of it is used to pay for the interest of your loan, and the remainder goes to the repayment of principal (see the formulas for the cells in the "Interest payment" and "Principal payment" columns. So every month, a portion of the principal is repaid, the loan balance gets smaller, the interest payment correspondingly decreases, and a bigger portion of the amortization goes to principal. In the "Current" worksheet, you'll see how much of the amortization goes to interest, how much goes to principal, and what the loan balance is every month. At the end of 20 years or 240 months, you'll see that the ending balance is zero and the loan will have been completely paid off.
Now go to the "Lump Sum" worksheet to see how much impact a 100,000 peso payment will make. If you make a lump sum payment, the entire amount will go to principal and this will accelerate the repayment of the loan. To see how it will affect the schedule, just manually add the amount to the "Principal payment" cell for the month of your choice. Here, I assumed that you will make the payment at the end of Year 3.
If you go to the bottom of the worksheet, you'll see that with this lump sum payment you will be able to completely pay off the loan at the end of Month 219, or near the beginning of Year 19. So it seems that you would have to make more lump sum payments if you want to completely pay off the loan by Year 15 (Month 180). Feel free to play with the spreadsheet and add whatever lump sum amounts you think you can afford to achieve your goal.
In another email, you mentioned that the interest rate of your loan will be readjusted for Year 4 onward. In the "Adjusted Rate" worksheet, you'll see how a different rate will affect your subsequent payments. You're currently in Year 3, so with the recent credit rating upgrades, you should expect a lower readjusted rate next year (I assumed 6%).
Anyway, I know that I did not really answer your questions, sorry about that, but rather gave you a tool that can hopefully help you find the answers yourself. Good luck!
In case anyone missed it, you'll find the spreadsheet here.
