Introduction to Mortgage
In Mortgage calculation, consider:
- Principal Amount: The original amount you took as the loan.
- Regular Monthly Payment: The amount you will pay every month, which includes the interest amount and a portion of the principal amount.
- Loan Terms: The total number of years to pay interest and loan. For a mortgage loan, it is normally 15-30
- Annual Interest Rate (APR): Annual Interest Rate you will pay for your loan. If the home loan APR is 6%, the monthly interest rate will be 6%/12 = 5%.
- Extra Payment: There are two types of Extra Payments: Regular Extra Payment and Irregular Extra Payment.
- Interest Savings: If you make extra payments, you will save interest.
- Tax Deduction: Mortgage interest is tax deductible.
Example 1 – With Monthly Extra Payments
Consider this situation: Blake took a home loan of $250,000 on Jan 10, 2018. He made 5 payments. His original loan term was 20 years. The annual Percentage Rate was 6%.
In the last 6 months, he paid extra $2000.
To see how much he has to pay if he wants to pay off his loan in the next 10 years:
Use this Payoff Calc. (Target) worksheet to enter details.
- You will get the following result.
- He has to pay $954.10 extra every month to pay the loan in the next 10 years.
- This is the summary of the loan:
Example 2 – With Quarterly Extra Payments
Blake wants to pay quarterly, not monthly.
Change the Extra Payment Frequency from Monthly to Quarterly.
After every 3 months, he has to pay $2892.20 extra to pay off the loan in the next 10 years.
Example 3 – With Recurring Extra Payments
Consider this situation: Fallon took a mortgage loan:
Original Loan Terms (Years): 20 years.
- Loan Amount: 200,000$
- APR (Annual Percentage Rate): 4.50%
- Loan Date: March 10, 2018.
She wants to extra pay her loan in two ways: - A recurring extra payment: She plans to pay 500$ extra every month. But it can be also bi-monthly, quarterly, and yearly.
- And an irregular extra payment: When she has extra money, she wants to make extra payments
Here are some more details on her present decisions:
- Extra Amount to Add: $500
- Extra Payment Frequency: Monthly
- Extra Payment Starts from Payment No.: 10
- Extra Irregular Payment: Don’t know the date but she can add it to any loan period.
This is her loan summary. In the above image, you see that she can add any amount of extra payment to her regular monthly and regular extra (recurring) payments.
She will be able to repay her loan completely in 11 years, 4 months, and 0 days.
Early Loan Payoff Calculator in Excel
Consider this dataset.
Steps:
- Go C7 and enter the following formula.
=NPER(C5/12,-C6,C4)
- Press ENTER. Excel will calculate the number of months.
It will take approximately 66 months to repay the loan.
Note:
- The annual rate is divided by 12.
- The negative sign is used for the monthly payment, as the amount is paid.
Things to Consider
Observe the image below.
Download Practice Workbook
Download the free template.
Related Excel Templates
- Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
- How to Make Chattel Mortgage Calculator in Excel
- How to Create Reverse Mortgage Calculator in Excel
- Calculator for Effective Interest Method of Amortization
- Creation of a Mortgage Calculator with Taxes and Insurance in Excel
- Interest Only Mortgage Calculator with Excel Formula
- Biweekly Mortgage Calculator with Extra Payments in Excel
- How to Create Fixed Rate Mortgage Calculator in Excel
- How to Create Offset Mortgage Calculator in Excel
- Mortgage Calculator with Extra Payments and Lump Sum in Excel
<< Go Back to Mortgage Calculator | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
This sheet has bugs. Monthly option is not avavilable in extra payment frequency. Also the summary table does not update
Hello MAX
Thank you for your comment. I have examined the entire workbook properly. As per my understanding, the sheet works fine.
The “Monthly” option in Extra Payment Frequency will be available if you select the “Monthly” option in the Regular Payment Frequency field. Actually, the options in Extra Payment Frequency are dependent on the Regular Payment Frequency field.
Coming to the 2nd issue, the summary table updates properly on my device. In your case, could you please check the version of Excel you are operating? This sheet has some advanced functions that may not work in all Excel versions.
Please let us know if you are satisfied with this answer. If not, please feel free to ask us. You can even send your dataset to the following address: [email protected]
Thanks again. Have a good day!