stephengnichols wrote:...several permutations of the PMT function...
I have assumed that "bi-weekly" means "every two weeks" and not "twice a week" nor "twice a month".stephengnichols wrote: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.
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.Lupp wrote: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.
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.Lupp wrote:Each year has 12 months exactly.
MrProgrammer wrote: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. ...Lupp wrote:Each year has 12 months exactly.
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.stephengnichols wrote:I finally figured out how to make the amortization schedule calculate for bi-weekly payments.
I cannot reproduce that problem. Perhaps you needed to enable Tools → Cell Contents → AutoCalculate.stephengnichols wrote:The payment calculation never changed the payment amounts - they remained the amount in the original spreadsheet.
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.stephengnichols wrote: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 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 wrote: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.
Users browsing this forum: mike.stirton and 14 guests