How to create a mortgage calculator in Microsoft Excel (21 Things to Know)

Because the Payment Schedule relies on the Mortgage Calculator to provide you with an accurate estimate of how much you’ll owe/pay off each month, both documents should be included in the same package. It is necessary to have a separate column for each of the following categories:

• Date – The date the payment in question is made.
• Payment (number) – The payment number out of your total number of payments (e.g., “1”, “6”, etc.).
• Payment ($) – The total amount paid.
• Interest – The amount of the total paid that is interest.
• Principal – The amount of the total paid with no interest (e.g., loan payment).
• Extra Payment – The dollar amount of any extra payments you make.
• Loan – The amount of your loan that remains after a payment.

Add the original loan amount to the payment schedule. In the “Loan” column, this will be placed in the first vacant cell at the very top.

Set up the first three cells in your “Date” and “Payment (Number)” columns.

 

To complete the date column, you’ll need to fill in the date on which you took out the loan, as well as the first two dates on which you intend to make the monthly payment (for example, 2/1/1999, 3/1/1999, and 4/1/1999).

In the Payment column, enter the first three payment numbers that have been received (e.g., 0, 1, 2).

Use the “Fill” function to automatically enter the rest of your Payment and Date values. To do so, you’ll need to perform the following steps:

• Select the first entry in your Payment (Number) column.
• Try to drag your cursor down until you’ve highlighted the number that applies to the number of payments you’ll make (for example, 360).
• Since you have started at “0”, you’d drag down to the “362” row.
• Click Fill in the top right corner of the Excel page.
• Select Series.
• Make sure “Linear” is checked under the “Type” section (when you do your Date column, “Date” should be checked).
• Click OK.

Enter the Payment per Period formula. The formula for calculating your Payment per Period value is as follows:

“Payment per Period, Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year)), Payment per Period, Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year)), Payment per Period, Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year))).

• You must preface this formula with the “=IF” tag to complete the calculations.
• Your “Annual Interest Rate”, “Number of Payments per Year”, and “Payment per Period” values will need to be written like so: $letter$number. For example: $B$6
• Given the screenshots here, the formula would look like this: “=IF($B$10<K8+(K8*($B$6/$B$8)),$B$10, K8+(K8*($B$6/$B$8)))” (sans the quotation marks).
  

The formula used to calculate your interest value is as follows: “Total Loan*Annual Interest Rate/Number of Payments per Year”.

• This formula must be prefaced with a “=” sign in order to work.
• In the screenshots provided, the formula would look like this: “=K8*$B$6/$B$8” (without quotation marks).
  

• Simply deduct the “Interest” value from the “Payment ($)” value in this formula.For instance, if the “Interest” field is H8 and the “Payment ($)” cell is G8, you would type “=G8 – H8” without quotation marks.
  

It is necessary to select the first empty cell in the “Loan” column from the list. This sum should be equal to or less than the amount of the initial loan you acquired (e.g., the second cell in this column).

The following formula is used to determine the loan’s worth: “Loan“-“Principal“-“Extra.”

For the screenshots that were provided, you’d type “=K8-I8-J8” without quotation marks.