Mortgage Calculator With Early Payment With Apple Numbers
What information do you need for the mortgage calculator?
The mortgage calculator needs to know your mortgage amount, the annual percentage rate (APR), the number of years on the mortgage, and the early extra payment you want to make per month on top of the required payment.
The input of your mortgage calculator will look like the screenshot below
How to setup mortgage calculator with early payment on Apple Numbers
The goal of a mortgage calculator is to give you the monthly payment and to tell you how long it takes to pay off the mortgage. In order to achieve that goal, you need to use formulas in Apple Numbers. The steps are below:
- Step 1: Calculate the required monthly payment including the extra payment
- Step 2: Calculate the number of months to pay off the mortgage
- Step 3: Calculate the number of years to pay off the mortgage. The detailed instruction is below.
First you must use the PMT function to determine the required mortgage payment per month. The formula to calculate such payment is as follows: PMT( APR/12, Year*12, Mortgage Amount, Ignore future-value, choose End ).
You will get a negative result. It means the money flies out of your pocket. You pay the bank.
Then you must subtract the result from the monthly extra payment. Now you arrive at the monthly payment. In the below example, the monthly payment including the extra payment is $621.60
After that, you will need to know how many years it takes to pay off the mortgage if you pay an early extra amount every month. You use the NPER function to calculate the number of months to pay off the mortgage. The formula is as follows: NPER( APR/12, Monthly Payment, Mortgage Amount, ignore future-value, choose End ).
In the below example, it takes 206 months to pay off the mortgage.
The large number of months confuses people sometimes. You can calculate the number of years by dividing the number of months by 12. The cell right next to the number of months is the number of years. It is 17 years in the above example.
