I currently run Numbers 3.6.2 I'm trying to calculate monthly payments with a static Principal amount and static mortgage term with changing interest rates ie rate may change in month 32 and again in month 46 and again in month 312. It would allow me to re-calculate monthly payments as interest rates change and it would assume the original Principal amount would be retired on the original debt-retirement date. Does such a formula exist?
Here are two ways that you might model this in Numbers.
The one on the left is easy to calculate (it assumes you repay the same amount of principal each month) but the payments are less even. The one on the right results in even payments for periods 1-31, another even amount for 32-45, etc.
The formulas in the first table,
| A2: | =ROW()−1 |
| C2: | =Loan terms::$B$1 |
| D2: | =−Loan terms::$B$1÷Loan terms::$B$2 |
| E2: | =SUM(C2:D2) |
| F2: | =−C2×B2÷12 |
| G2: | =D2+F2 |
| A3: | =ROW()−1 |
| C3: | =E2 |
| D3: | =−Loan terms::$B$1÷Loan terms::$B$2 |
| E3: | =SUM(C3:D3) |
| F3: | =−C3×B3÷12 |
| G3: | =D3+F3 |
With fprmulas in the third row filled down to row 301.
The formulas in the table on the right:
| A2: | =ROW()−1 |
| B2: | =Loan terms::$B$2−A2+1 |
| D2: | =Loan terms::B1 |
| E2: | =J2−G2 |
| F2: | =SUM(D2:E2) |
| G2: | =−D2×C2÷12 |
| H2: | =IF(C2≠C1,B2,H1) |
| I2: | =IF(C2≠C1,D2,I1) |
| J2: | =PMT(C2÷12,H2,I2) |
| A3: | =ROW()−1 |
| B3: | =Loan terms::$B$2−A3+1 |
| D3: | =F2 |
| E3: | =J3−G3 |
| F3: | =SUM(D3:E3) |
| G3: | =−D3×C3÷12 |
| H3: | =IF(C3≠C2,B3,H2) |
| I3: | =IF(C3≠C2,D3,I2) |
| J3: | =PMT(C3÷12,H3,I3) |
With fprmulas in the third row filled down to row 301.
A link to document on Dropbox is here.
SG
Here are two ways that you might model this in Numbers.
The one on the left is easy to calculate (it assumes you repay the same amount of principal each month) but the payments are less even. The one on the right results in even payments for periods 1-31, another even amount for 32-45, etc.
The formulas in the first table,
| A2: | =ROW()−1 |
| C2: | =Loan terms::$B$1 |
| D2: | =−Loan terms::$B$1÷Loan terms::$B$2 |
| E2: | =SUM(C2:D2) |
| F2: | =−C2×B2÷12 |
| G2: | =D2+F2 |
| A3: | =ROW()−1 |
| C3: | =E2 |
| D3: | =−Loan terms::$B$1÷Loan terms::$B$2 |
| E3: | =SUM(C3:D3) |
| F3: | =−C3×B3÷12 |
| G3: | =D3+F3 |
With fprmulas in the third row filled down to row 301.
The formulas in the table on the right:
| A2: | =ROW()−1 |
| B2: | =Loan terms::$B$2−A2+1 |
| D2: | =Loan terms::B1 |
| E2: | =J2−G2 |
| F2: | =SUM(D2:E2) |
| G2: | =−D2×C2÷12 |
| H2: | =IF(C2≠C1,B2,H1) |
| I2: | =IF(C2≠C1,D2,I1) |
| J2: | =PMT(C2÷12,H2,I2) |
| A3: | =ROW()−1 |
| B3: | =Loan terms::$B$2−A3+1 |
| D3: | =F2 |
| E3: | =J3−G3 |
| F3: | =SUM(D3:E3) |
| G3: | =−D3×C3÷12 |
| H3: | =IF(C3≠C2,B3,H2) |
| I3: | =IF(C3≠C2,D3,I2) |
| J3: | =PMT(C3÷12,H3,I3) |
With fprmulas in the third row filled down to row 301.
A link to document on Dropbox is here.
SG
I find it a little difficult to follow what you are describing
What do you mean by "static principal amount"? The original amount of the loan? Or a fixed dollar amoutn of the monthly payment is designated as repayment of principal?
What do you mean by "original Principal amount would be retired on the original debt-retirement date"? Simply that the loan has to be repaid in full by the end of the last period? Or something else?
Just in case you haven't noticed already, in your menu at File > New you can find a Mortgage Calculator template that should give you some ideas.
Relevant functions include PMT, IPMT, PPMT.
SG
Implicit in the PMT function (in column J), when given the number of periods as months and the rate as the monthly rate (annual rate divided by 12) as in my example, is monthly compounding. If you have specifics on exactly how your bank calculates then there will be a way to model that. But the model may already be close enough to the real thing to be useful.
Rather than list out all the months as rows there probably are ways to shorten the table using the built-in financial functions. But often it is more instructive to list everything out. One of the advantages of having the whole table is that you can try charting it to visualize the underlying dynamics.
SG
Thank you for your reply. Static principal amount is the original amount of the loan. The loan has to be repaid in full by the end of the last period. The mortgage term won't change even if rates change during the term. I looked at the mortgage template that Numbers offered but it did not allow for rate changes during the term of the loan. Every mortgage calculator I've seen so far does not allow for rate changes but I'm not surprised because by definition, mortgage terms, rates and principal are to be constant.
FYI, I have a line of credit that I'm treating as a mortgage, however, the line of credit interest rates do vary. I'd like to retire the loan on the 25-year anniversary date of the loan.
Right now, I'm re-calculating payments with new principal, term and rate every time a rate change takes place and I'm not certain whether it is an accurate or correct way to retire the debt.
Thanks again
The line of credit has a minimum monthly payment requirement (basically interest only with small principal pay-down) but I've chosen to treat it as a mortgage with payments that would allow the debt to be retired over 25 years.
Thank you for the spreadsheets. I'm going to drop my numbers in to see how they compare to the mortgage calculation programs that I've been using. I have a question. My bank does semi-annual compounding on mortgages. Do your spreadsheets allow for compounding and if so, how frequently. I'm very impressed with what I'm seeing. Thank you for your efforts. Much appreciated.
BIO
Brian In Ottawa wrote:
I'm re-calculating payments with new principal, term and rate every time a rate change takes place and I'm not certain whether it is an accurate or correct way to retire the debt.
Hi Brian,
I think whether that's "correct" depends on the terms of your line of credit. Does the lending agreement call for you to make level payments (each payment including a principal portion and an interest portion) calculated on the current interest rate and the remaining term at the time the interest rate is reset? Or does require you to make regular minimum principal payments throughout the term of the loan plus interest payments at the then prevailing rate? Or does it allow you to repay principal without a set pattern, as long as you repay the principal by the end of the term of the line of credit?
Depending on the terms of the agreement you could model it using a table similar to the 'Monthly Data' table in the 'Mortgage Calculator' template where you would add a column for the interest rate applicable to each month.
SG
mortgage calculator that allows for changing rates
