'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Calculate Biweekly Mortgage Payments with Principal-Only in Spreadsheet
Question details
The user needs to create an amortization schedule for a biweekly mortgage containing 26 annual payments, where two of the payments should be applied entirely to the principal.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Building a customized 26-payment biweekly amortization schedule that accurately reflects loan terms where two extra payments per year do not accrue any interest.
- Observed behavior
- Standard amortization formulas calculate interest for all 26 payments, failing to direct the two extra biweekly payments entirely to the principal as required by the loan terms.
Verify your specific loan terms with your lender to confirm that the two extra biweekly payments are definitely applied 100% to the principal and do not accrue interest.
Adjust the Interest Formula for Extra Payments
Use conditional formulas to identify the two extra payment periods in the year and force their interest calculation to zero.
To properly calculate 26 biweekly payments where two payments go directly to the principal, you must treat the schedule as 24 regular payments plus 2 extra principal-only payments.
By modifying the interest column to check if the current payment is one of the extra ones (usually the third payment occurring in a single month), you can allocate the entire payment amount to reducing the balance.
Create columns for Payment Number, Date, Payment Amount, Interest, Principal, and Balance in your spreadsheet.
Create a 'Payment Type' helper column and manually or formulaically flag the two extra payments per year (e.g., mark them as 'Extra' or '1').
In the Interest column, use an IF statement to check the helper column. For example, type =IF(B2="Extra", 0, [Standard Interest Formula]) to ensure zero interest is calculated for those periods.
In the Principal column, subtract the Interest cell from the Total Payment Amount cell. For the extra payments, this will automatically allocate the entire payment to the principal.
Easily Build Amortization Schedules with WPS Spreadsheet
WPS Spreadsheet provides powerful financial functions and conditional logic to help you effortlessly construct and customize complex biweekly mortgage amortization schedules.
- 1. Open WPS Spreadsheet: Launch WPS Office and click 'Spreadsheet' to start a new blank workbook.
- 2. Define loan variables: Enter your Total Loan Amount, Annual Interest Rate, and Payment Frequency at the top of the sheet.
- 3. Set up columns: Create column headers for Payment Number, Date, Payment, Interest, Principal, and Balance.
- 4. Enter custom IF formulas: Input your customized IF formulas in the Interest column to handle the principal-only payments appropriately.
- 5. Fill the schedule: Select the first row of your formulas and drag the fill handle down to populate the schedule for the lifespan of the mortgage.
Frequently Asked Questions
Why do biweekly mortgages have 26 payments instead of 24?
Because there are 52 weeks in a year, paying every two weeks results in exactly 26 payments (52 divided by 2), which is two more than a standard semi-monthly schedule of 24 payments.
How do I use the IPMT function for interest calculations?
The IPMT function calculates the interest portion of a payment for a given period. You can enter =IPMT(rate, period, total_periods, present_value) in your cell, but you will need to nest it inside an IF function to return 0 for the extra principal-only payments.
Can I automate finding the third payment in a month?
Yes, you can use the MONTH function combined with COUNTIFS to count how many payments have occurred in a specific month. If the running count equals 3, you can trigger your IF formula to apply that payment entirely to the principal.
Does WPS Spreadsheet support Excel mortgage templates?
Yes, WPS Spreadsheet is highly compatible with Microsoft Excel. You can seamlessly open, edit, and save any Excel-based amortization schedule or financial template directly within WPS Office.
