How To Create Or Enter A Mortgage Payment Calculator, (Calculation Or Formula) In Excel Explained

In this video we discuss how to create or enter a mortgage payment calculator into a cell in excel. We go through a detailed example step by step of the process.

Transcript/notes
Here is the formula to calculate monthly mortgage payments and the definitions of each of the variables.

As you can see there are 4 variables we need to know, which I have listed in column A. And we are going to put the amounts or data of these variables in column B. And we are going to put the calculation or calculator in cell A9.

As an example lets say that after the down payment, someone is left owing $200,000, which is the principal. The annual interest rate is 4.5%, the loan is for 30 years, and there are 12 payments per year, which I have entered in the worksheet for each of the variables. What is the monthly mortgage payment?

Next, we need to enter the monthly mortgage payment formula into cell A9.
So, we left click on cell a9, so it is highlighted. Next, we type in an equals sign. Referring to the formula, we first need to type in an open parenthesis, then we left click on the principal amount, cell B2. Next we type in a multiplication sign, which is shift 8 on the keyboard.

From here, we type in an open parenthesis, then we left click on the yearly rate, cell B3, and next we type in a division sign, which is a forward slash in excel. Now we left click on the number of payments per year, which is cell B5. Next, we are going to type in 2 closed parenthesis, which closes off the R over N portion of the formula and closes off the P times R over N portion of the formula, and the top of the main fraction is complete.

Next we type in a division sign, again a forward slash. From here we type in an open parenthesis and then a 1. Next, we type in a minus sign, and then we type in an open parenthesis. From here we type in a 1 and then another division sign. Next, we type in 2 open parenthesis, then we type in a 1. Now, we type in a plus or addition sign, then we type in another open parenthesis.
From here we left click on the yearly rate, cell B3, then we type in another division sign or forward slash. Next, we left click on the number of payments per year, which is cell B5. Now we are going to type in 2 closed parenthesis, which will close off the R over N portion on the bottom of the formula and the 1 plus R over N portion on the bottom.

From here, we type in a to the power symbol, called a carrot sign, which is shift 6 on the keyboard. Next, we type in an open parenthesis, then we left click on the number of payments per year, cell B5. Now we type in a multiplication sign, shift 8 on the keyboard, then we left click on the number of years, which is cell B4. From here, we are going to type in 4 closed parentheses. Each of the parentheses are colored, so you can see what each one closes off. From here, we hit the enter key, and we have our answer of $1013.37 rounded off.

And now, you can go into each of the variables amounts and change them around, as you see here, and the calculation will automatically update the monthly payment amount.

Chapters/Timestamps
0:00 Formula and variables to calculate monthly mortgage payments
0:17 Example set up
0:35 Start of entering formula into Excel
2:46 Ending formula
3:01 Changing variables amounts