[Solved] Mortgage amortization calculation

[robleyd]

I have a spreadsheet template for figuring all the details of a mortgage amortization. However, all the formulas are set for the number of years and apparently assume monthly payments. I want to set the formulas to calculate the details (interest paid, balance, etc.) based on bi-weekly payments. I have tried several permutations of the PMT function and am having no luck. Can anyone shed some light on what I need to do? All help appreciated.

[Villeroy]

Chances to receive an answer will be better if you share that template with us.

[Forum] How to attach a document here

[stephengnichols]

Thank you for the quick response and tutoring. I am new to this.
The formulas in this template can't be changed since the author has it locked, so I've been trying to reconstruct it from scratch. None of the info in it is mine.

[Lupp]

...several permutations of the PMT function...

Sorry I don't understand the term.

Anyway.
There are 5 formulas in your sheets using a function of the PMT family, 3 of them PMT() and 2 in addition CUMIPMT(). None of these functions works with the length of the payment interval. Both need to know the number of payments, and the interest per interval.

Your sheets have input cells for (some omitted here)
= 'Primary Mortgage' which I would interpret as what is named PresentValue in the specifications of the related functions.
= 'Period Beginning Date', supposedly the date one period in advance of the first payment - if due is at end at least.
= 'Term in Years' what most likely shall tell in a way when the last payment will be due.
= 'Annual Interest Rate' which seems to be rather clear.

The PMT() function expects input for
= 'Interest Rate per Period'
= 'Number of Periods'
= 'Present Value'
= 'Payment Type' (optional; default 0 for end of period)

The formulas as they are calculate
= the rate per period by dividing the rate per year by 12.
= the number of periods by multiplying the number of years by 12.

OK?
I cannot tell. The rate per month e.g. may be calculated this way resulting in a (roughly) effective rate per year of
((1.06)^12 - 1) = 6.168% in your example.
Whether the usage of 0.5% per month is correct you can only decide based on the contract - or legally affirmed usage.
(To get an effective 6% per year e.g. the monthly rate would need to be (1.06 ^(1/12) - 1) expressed as rate. That's about 0.4868%.)

What to change?
Each year has 12 months exactly.
No single year has exactly 52 weeks (or 26 bi-weekly periods).
You need to look in the contract again to see how the payment dates are exactly defined. If there actually payment is due every second wednesday, e.g. there is no simple way to convert a number of years into a number of periods.

Very likely the payment is due twice a month, and you can get the number of periods by changing the multiplier 12 (number of months) to 24 (number of payment dates - two per month).
In the same sense you then will use (yearly rate)/24 for the rate per period.
(This results in an effective rate of about 6.176% per year then.)

Nobody can accept reliability for any suggested formula without knowing the exact terms to be applied. That's neither a question of math expertise nor of spreadsheet expertise but of design of contract.

The exact specifications for the Calc functions used, you find here: http://docs.oasis-open.org/office/v1.2/ ... -part2.pdf. There is no guarantee of course for a correct implementation.

[MrProgrammer]

Hi, and welcome to the forum.

I have a spreadsheet template for figuring all the details of a mortgage amortization. However, all the formulas are set for the number of years and apparently assume monthly payments. I want to set the formulas to calculate the details (interest paid, balance, etc.) based on bi-weekly payments.

I have assumed that "bi-weekly" means "every two weeks" and not "twice a week" nor "twice a month".

202007212256.ods

(39.76 KiB) Downloaded 54 times

A search of the forum for Amortization will find numerous other topics which may lead stephengnichols to a preferred solution.

Nobody can accept reliability for any suggested formula without knowing the exact terms to be applied. That's neither a question of math expertise nor of spreadsheet expertise but of design of contract.

Agreed. I base my calculations with the biweekly interest rate as the annual interest rate divided by 26. But the contract might indicate that the divisor should be 365/14 (26.07143) or 365.25/14 (26.08929) or 365.2425/14 (26.08875) or 12*2 (24.00000 for semimonthly) or something else. If stephengnichols wants to experiment, the divisor is specified in Insert → Names → Define → _Rate. The principal and interest change by relatively small amounts.

Each year has 12 months exactly.

Yes, however there is a detail which I had not considered previously. If the loan is paid on, say, the first of eaach month, the intervals are not of constant length. Some are longer than others, and the accrued interest should be more. The difference will be small, of course. I have a loan payment detail schedule from a bank. I can see that it does consider that the payment intevals are not of constant length! It counts days, including leap days, when calculating interest. In that case calculations such as the ones given in Derivation of Financial Formulas cannot be used. With varying payment intervals, there is no simple way to use a spreadsheet to precisely determine the payment needed each month which will reduce the loan balance to zero at the end of the specified term. And of course the bank does not use a spreadsheet for loan calculations. It has special software for that. For a loan which is paid biweekly, the payment intervals are constant and functions like PMT, IPMT and PPMT should give correct results.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[stephengnichols]

Thank you, Mr. Programmer. Yes, I mean to make payments every two weeks. I followed the link you sent but even though I entered the parameters I needed (loan amount, interest rate, weeks, etc.), something odd happened. The payment calculation never changed the payment amounts - they remained the amount in the original spreadsheet. Additionally, there is no ability to make additional payments. For instance, when I run the calculations in the generic spreadsheet I linked to, I would be paying about $500 a month, once a month. That spreadsheet allows me to input an additional amount and recalculates my amortization figures throughout the life of the loan. The link you sent me does a good job of giving me complete information on interest paid, principal paid, etc., but I would like have a more flexible spreadsheet in terms of being able to adjust the frequency of payment, additional amounts paid as I can afford it, and still know what I'm paying in interest and principal, balance and remaining life of the loan. I do appreciate all your input.

Stephen

[Lupp]

Each year has 12 months exactly.

Yes, however there is a detail which I had not considered previously. If the loan is paid on, say, the first of eaach month, the intervals are not of constant length. Some are longer than others, and the accrued interest should be more. The difference will be small, of course. I have a loan payment detail schedule from a bank. I can see that it does consider that the payment intevals are not of constant length! It counts days, including leap days, when calculating interest. ...

I would expect that there is different usage concerning this kind of calculations. I also had to pay for a loan / mortgage more than once in my life. But these were standardised the way usual in Germany. The RatePerMonth (=1/12 RatePerYear) was calculated the way also used in the sheets attached to the second post by the OriginalQuestioner. Unscheduled payments lead to interest calculations based on days, but only till the next due date ...
The questioner actually must accept that he needs to understand the formulas he uses, and the specifications of used standard functions as well if he wants to get reliable results regarding the actual usage defined by a contract or by legally affirmed usage. There's no schoolboy way (and no king's way) to make a spreadsheet-jockey game accountable. You may rely on Euclid so far(*).

Concerning the relation between RatePerYear, RatePerMonth, RatePerDay ...
There isn't a correct way as long as not explicitly is defined how correct should be understood. If you insist on a mathematical point of view and "the highest accuracy to every detail", you may find it correct to assume a daily rate actually applied either 365 or 366 times a year. In this case (no leapyear now!) you would need to calculate the yearly rate applying the GrowthFactor (1 + DailyRate) 365 times, and thus get a YearlyRate = (((1 + DailyRate)^365) - 1). "Pure" mathematics would even be able to work with rates per second - and finally with a limes expression moving the time interval towardss zero. What good for? If it isn't about millions (of whatever currency) an actual daily interest would be very inexact due to unavoidable rounding. (Just for fun I actually once calculated an example based on standardized rates published by the Deutsche Bundesbank by daily interests rounded to EUR0.01 for a statement concerning costs for a flat I'm renting out. It was wrong in a justforfunny way if compared with a "reasonable" calculation.)

My opinion: Rules an contracts must be simple. Never use PMT() and the like if not you really understand them. Better simplify the rules slightly as needed for your understanding, then work with the math you were teached in school and you hopefully understand therefore. The differences will be very marginal. The gain in reliability is worth to accept them. A contract being as complicated as needed to make it impossible for you to go the described way should be regarded wilful deception or attempted fraud.

(*) https://www.quora.com/Euclid-wrote-Ther ... ou?share=1

[stephengnichols]

I finally figured out how to make the amortization schedule calculate for bi-weekly payments. What I never did figure out is how to get the final payment and its date to automatically calculate. There is a formula in the appropriate cell, but it's not automatic. I had to scroll down to the final balance number and redo the formula with the correct cell references any time I changed the loan amount, additional payments made, or other terms. It'd be nice for that formula to find the last occupied cell in the balance column and use that to compare against the regular payment, making the subtraction/adding the appropriate interest owed, and populate the cell up top, along with the due date of that payment. Oh well. Onward, through the fog. Thanks to all for their input. Solved

[Villeroy]

First of all, I removed all the IFs.
Then I added a negative MATCH to get the position where the balance becomes negative.
With this number and the OFFSET function you can get all the values you need.

[stephengnichols]

Thank you, Villeroy. I don't know why I used the IF formula but it seemed (in my limited experience with spreadsheets) to be the right thing to do. Your MATCH formula does not seem to work properly once I include additional amounts paid, and it only reports the row in which I can find the amount to be paid. I was trying to get the calculations to auto-populate the date and the amount owed for the final payment in the two places.I'll keep at it. Thanks again.

[MrProgrammer]

I finally figured out how to make the amortization schedule calculate for bi-weekly payments.

You're making good progress, but have some errors to correct. I am referring to your Mortgage Amortization - 2 week frequency 2.ods attachment. The most serious error is the formula in C8. =PMT(C7/12;26*C5;-C4;0;0) should be =PMT(C7/26;26*C5;-C4;0;0). This changes the payment from $422.57 to $326.93. After making that change, the value in F5 is incorrect because formula =MAX(I13:I501) has used the value in cell I403 but it should be the value in I402 where the equity is zero. The source of that problem is that C402-E402 is positive, though very small, about 3x10⁻¹¹. Then your calculations make an additional payment when the equity is zero. There are numerous ways to handle this problem. I'll let you choose one since you'll learn more if you solve it yourself.

A more complex design issue concerns the term of the loan. Your spreadsheet requests the term in years. 15 years from the start date of 2020-09-01 would be 2035-09-01. After changing C8 and extending the values in columns A and B down, your calculations (and mine using 202007212256.ods) show the equity is reduced to zero at the end of July, not in August. I find it confusing to use years because 15 years has, depending on leap years, 5478 or 5479 days, which is a bit more than 391 two-week periods. Your calculations show 390 payment periods, which would be correct if you are using a factor of 26 in your PMT calculation. However if the term is really 15 years, you may want to use a factor of 391/15 (26.06667) for PMT since 391 biweekly periods end on 2035-08-28. I avoided this difficulty by specifying the term in weeks when I created my earlier attachment. Using a factor of 26, I can put =15*26*2 in my B2 cell so the spreadsheet calculates 780 for me. 780 weeks is 5460 days and 390 payments. This forum is for OpenOffice, not Finance/Investments, and not a good place to discuss this loan design issue, however I thought you might want to consider it in a spreadsheet you're building. Surely any financial institution offering loans will do these calculations for you. I suppose if this is just a "What If?" analysis tool for yourself, the factor isn't significant. The mistake in C8 is though: $95 per payment.

Your date increment formula in B14, =DATE(YEAR(B13);MONTH(B13);DAY(B13)+14), could be simplified to =B13+14. You seem to like to use leading plus signs in your formulas, for example =+$C$4 in C13. Calc just ignores them. You could use =$C$4 instead.

The payment calculation never changed the payment amounts - they remained the amount in the original spreadsheet.

I cannot reproduce that problem. Perhaps you needed to enable Tools → Cell Contents → AutoCalculate.

[Villeroy]

Be sure that you understand how MATCH, LOOKUP and similar functions work.

=MATCH( x ; vector ; -1 ) scans the given vector (one row or column of cells) until ...
-- either the exact x is found
-- or a value smaller than x is found.
In the latter case it returns the last position where the vector's value is bigger than x.
This requires the search vector being sorted in descending order., a requirement which is fulfilled as long as your balance is constantly decreasing.
---------------------------
In default mode, when the last argument is missing or 1 instead of -1, it searches a search vector until it matches value >= x and the vector needs to be in ascending order. Same mode of operation with the LOOKUP, VLOOKUP and HLOOKUP functions.
Only MATCH has a search mode -1 for descending vectors.
---------------------------
instead of ...
=OFFSET( cell ; match)-1
you may prefer to use
=INDEX(range ; match) e.g. INDEX($H$13:$H$1000;$F$1;1) which returns the value from H13:H1000 at row F1 , column 1 [oh, and remove the -1 from the F1 formula. My example is a little bit bumpy]

[stephengnichols]

Thanks to you both. Mr. P, the payment calc was completely inadvertent, in that I knew I wanted it to be /26, not /12. In an earlier version I had done that. Now ... fixed.

All the + signs were taken straight from the model sheet I was using. I had no idea why the original author put them in but I didn't want things to go haywire by eliminating them. As I said, I am very much a rookie at this. Anyway, now ... fixed.

As to using the IF functions, I wanted to eliminate all the calculations continuing down the page after satisfying the loan amount, hence, If there was no longer a balance owed, I wanted the calculations to end. I figured the best way to do that was to make calculations dependent on there actually being positive balances. When I eliminate the IF function, calculations just seem to keep going far into negative balance territory.

In reference to the I403 cell formula, I changed that to read " <=0 " and that seemed to do it. One thing I don't understand is the calculation in G404. According to the instruction, if the referenced cells add up to more than zero, an addition should take place. If not then the cell should be left blank, as indicated by the "". However, even though the referenced cells add to exactly zero, the addition and population of G404 takes place. Or is that the 3x10⁻¹¹, amount you were talking about, triggering the calculation?

[Villeroy]

I don't see any reason why you need to hide any precious data and change each column's data type from number to text. The human readable results are in the top 10 rows of the sheet and the red formatting of negative values serves as visual aid when scrolling down. No need to hide anything.
Another approach:
Open my version of the file:
C14: =MAX(0;C13-E13) and copy down
Now the balance is either a positive number or zero. Since your document is set up to hide zeroes, the visual effect is the same as with the nasty IF(...;"") but the cells do have a numeric zero value.
F1: =MATCH(0;$C$13:$C$305;0)-1
I only changed the mode of operation to 0. Now it looks up the exact position of the first occurrance of zero regardless of any sort order and subtracts 1 in order to get the position of the last payment. The subsequent formulas remain the same since they still operate with that position number. Apart from the visual effect of hidden zeroes the is one more difference: If the list is not long enough for all the down payments, if there is no zero value, the MATCH will throw error #N/A! indicating that you have to drag down the formulas further.

[MrProgrammer]

However, even though the referenced cells add to exactly zero, the addition and population of G404 takes place. Or is that the 3x10⁻¹¹, amount you were talking about, triggering the calculation?

The difference C402-E402 is not "exactly zero" as can be seen by editing (press F2) the formula in I402, selecting this sub-expression, and pressing F9 to show its value. It is larger than zero and thus the test C402-E402>0 is TRUE. In C403, C402-E402 displays as $0.00 because you have formatted the cell to show only two decimal places. It is important to learn the difference between the value in a cell and how that value is displayed via formatting. Read section 2. Controlling how data is displayed in Ten concepts that every Calc user should know.

All the + signs were taken straight from the model sheet I was using. I had no idea why the original author put them in but I didn't want things to go haywire by eliminating them.

The original designer may have been a financial professional who has not learned much about spreadsheets. They know the financial procedures, but not how to express them with Calc/Excel formulas. Unfortunately, this is common, I think. You can learn more near the end of the tutorial above.

[stephengnichols]

Villeroy, I made your suggested changes to the C14 cell and the balances changed, e.g., in c14, it changed form $98,586.70 to $98,585.78. Won't that skew the entire balance column? Why would that happen and why would I want it to?

[RoryOF]

Rounding error, as Mr Programmer pointed out in previous post.

[stephengnichols]

Really? 92 cents in the second entry is not what I'd consider a rounding error. Must be something else in my formula that's making it go haywire.

[Villeroy]

I reworked my approach as follows:

1) Add MrProgrammer's PMT formula in B8
2) Use the descending match but this time matching 0.01 instead of 0 in cell F1. This ignores any rounding erroro <0.01.
=MATCH(0,01;$C$13:$C$1000;-1) matches the last value >= 0.01
3) Cell I1 still uses =MATCH(0;$C$13:$C$1000;0) as a test if we have enough formulas rows to reach the complete down payment.
4) Cell F2 has a hyperlink jumping to the matched position.
5) the other cells in F use the row offset in F1 from header row #12, for instance =OFFSET(B$12;$F$1;0)

Do you still spot any errors?