How To Calculate Mortgage Payment (By Hand) In Excel Explained - Create A Mortgage Payment Formula
1.5 هزار بار بازدید -
12 ماه پیش
-
In this video we discuss
In this video we discuss how to create and calculate a mortgage payment formula in an excel spreadsheet. We go through a detailed step by step process of creating the formula with an example
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 things we need to know. As an example, lets say that after the down payment, you are left owing $200,000, which is your principal. The annual interest rate is 4.5%, the loan is for 30 years, and there are 12 payments per year. What is the monthly mortgage payment?
We are going to calculate it, and put the answer in cell B8, so, we left click on cell B8, 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.
Chapters/Timestamps
0:00 Formula to calculate monthly mortgage payment
0:10 Example set up
0:22 Formula in excel for calculating monthly mortgage payments
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 things we need to know. As an example, lets say that after the down payment, you are left owing $200,000, which is your principal. The annual interest rate is 4.5%, the loan is for 30 years, and there are 12 payments per year. What is the monthly mortgage payment?
We are going to calculate it, and put the answer in cell B8, so, we left click on cell B8, 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.
Chapters/Timestamps
0:00 Formula to calculate monthly mortgage payment
0:10 Example set up
0:22 Formula in excel for calculating monthly mortgage payments
12 ماه پیش
در تاریخ 1402/04/25 منتشر شده
است.
1,558
بـار بازدید شده