A mortgage is a loan that you take to buy a property, such as a house or an apartment. You pay back the loan over a period of time, usually with a fixed interest rate. The amount you pay each month is called the mortgage payment, which consists of two parts: the principal and the interest. The principal is the amount of money you borrowed, and the interest is the cost of borrowing the money.
One of the goals of many homeowners is to pay off their mortgage as soon as possible, to save money on interest and to own their property outright. There are different ways to achieve this goal, such as making extra payments, refinancing to a lower interest rate, or shortening the loan term. In this article, we will show you how to use Excel formulas to calculate how much you can save by paying off your mortgage early, and how long it will take you to do so.
To calculate how long it will take you to pay off your mortgage early, you need to use the NPER function in Excel, which returns the number of periods for an investment or a loan. The syntax of the NPER function is:
=NPER(rate, pmt, pv, [fv], [type])
where:
- rate is the interest rate per period
- pmt is the payment made each period
- pv is the present value of the loan
- fv is the future value of the loan (optional, default is 0)
- type is the payment due date (optional, 0 for end of period, 1 for beginning of period, default is 0)
For example, if you want to know how many months it will take you to pay off a $200,000 loan with a 5% annual interest rate, if you pay $1,500 per month, you can use this formula:
=NPER(5%/12, -1500, 200000)
The result is 153.64, which means it will take you about 154 months, or 12.83 years, to pay off the loan.
To use Excel formulas to calculate the mortgage payoff, you need to follow these steps:
- Enter the input data, such as the principal amount, the annual interest rate, the loan term, and the extra payment amount, in separate cells.
- Calculate the original monthly mortgage payment using the PMT function, and enter the formula in a cell.
- Calculate the total interest with the original loan terms using the formula above, and enter the formula in a cell.
- Calculate the new monthly mortgage payment by adding the extra payment amount to the original monthly payment, and enter the formula in a cell.
- Calculate the new number of monthly payments using the NPER function, and enter the formula in a cell.
- Calculate the total interest with the new loan terms using the formula above, and enter the formula in a cell.
- Calculate the interest savings by subtracting the total interest with the new loan terms from the total interest with the original loan terms, and enter the formula in a cell.
- Format the cells as currency, percentage, or number, as appropriate.
Example
Let’s say you have a $300,000 mortgage with a 4% annual interest rate and a 30-year loan term. You want to pay off your mortgage in 20 years by making an extra payment of $500 per month. How much will you save in interest, and how long will it take you to pay off the loan?
Here is how you can set up the spreadsheet with the input data and the formulas:
| A | B | C |
|---|---|---|
| Principal | $300,000 | |
| Annual Interest Rate | 4% | |
| Loan Term (years) | 30 | |
| Extra Payment | $500 | |
| Original Monthly Payment | =PMT(B2/12, B3*12, B1) | $1,432.25 |
| Total Interest (Original) | =C5B312-B1 | $215,608.52 |
| New Monthly Payment | =C5+B4 | $1,932.25 |
| New Number of Payments | =NPER(B2/12, -C7, B1) | 181.45 |
| Total Interest (New) | =C7*C8-B1 | $100,957.01 |
| Interest Savings | =C6-C9 | $114,651.51 |
The result is that you will save $114,651.51 in interest, and you will pay off the loan in 181.45 months, or 15.12 years.
To display the result in a table, you can use this formula in cell A11:
=TEXT(C8, "0")&" months ("&TEXT(C8/12, "0.00")&" years)"
The table will look like this:
| Original | New | |
|---|---|---|
| Monthly Payment | $1,432.25 | $1,932.25 |
| Number of Payments | 360 months (30.00 years) | 181 months (15.12 years) |
| Total Interest | $215,608.52 | $100,957.01 |
| Interest Savings | $114,651.51 |
