How To Calculate And Create An Amortization Schedule Table In Excel Explained
3.9 هزار بار بازدید -
12 ماه پیش
-
In this video we discuss
In this video we discuss how to do all of the calculations and create an amortization schedule or table in excel. We go through all of the steps in detail using an example
Transcript/notes (partial)
We are going to go through how to create an amortization table in excel
As an example, let’s say that someone purchases a house for $240,000, and puts $40,000 down, so, they have a mortgage of $200,000, at fixed rate of 9%, the loan is for 30 years and the number of payments per year will be 12, using the mortgage payment formula, as you see here, their monthly payment will be $1609.25 rounded off.
We are going to want to list these at the top of the spreadsheet, so we can reference their cell values when we create the table.
There are 3 major steps or calculations that we need to make, step 1, calculating the interest amount for each month, step 2, calculating the amount of the payment that goes towards the principal, and step 3, calculating the new principal amount after the payment.
In our table there are going to be 6 columns, the payment number, payment amount, current principal, interest amount, principal reduction, and balance of principal.
For the first column, payment number, this is going to be 1, 2, 3, and so on, all the way to 360, the total number of payments. An easy way to do this is to type in 1, then type in 2 beneath. Then, left click and hold and drag over both cells, then release the left click and hold, so both cells are highlighted. From here, go down to the bottom right hand corner of the highlighted cells, and you will see the cursor turn black, from here, left click and hold and drag down to where 360 will be. Then release the left click and hold and the values will be entered into the cells.
The second column, payment amount, this will always be the $1609.25 payment we calculated a moment ago, so, we can type that into cell B22 and hit the enter key. Since this is always going to be $1609.25, this value needs to go in this column for the whole table. To do this, we left click on cell B22, so it is highlighted. From here, go down to the bottom right hand corner of the highlighted cell, and again, you will see the cursor turn black, from here, left click and hold and drag down to the end of the table, to where payment number 360 is, and release the left click and hold, and the column has been filled in.
The third column, the current principal will be the principal amount before the current payment number. So, the current principal before payment number 1 will be $200,000.
The fourth column, the interest amount is the interest amount for 1 month on the current principal amount, and this number will change each month. The formula to calculate this is interest equals, the principal times the rate, times time, and remember, time is 1 month here, so this will be 1 over 12, and here is this calculation done by hand for the first month.
To do this calculation in excel, we start by left clicking on the cell we want to put the answer in, in this case cell D22, so it is highlighted. Next we type in an equals sign, now, referring to the formula, we left click on the principal amount, cell B2, then we type in a multiplication sign, which is shift 8 on the keyboard. From here we left click on the rate, cell B3, then we type in another multiplication sign.
Next, we type in an open parenthesis, then we type in a 1. Now we type in a division sign, which is a forward slash in excel. From here we left click on the number of payments per year, cell B5, then we type in a closed parenthesis. And now we hit the enter key, and we have our answer of $1500.
Before we move on, we are going to make a few modifications to the formula we just entered into cell D22, to make building the table much, much easier. We are going to left click on cell D22, so it is highlighted. Now, we are going to go up to the text in the formula bar and put the cursor in front of the first B and left click.
Chapters/Timestamps
0:00 Example set up
0:20 How to calculate monthly mortgage payment amount
0:35 3 major calculations in the table
0:50 The 6 columns in the table
1:00 Column 1, payment numbers
1:14 How to quickly enter payment numbers into excel
1:57 Column 2, payment amount
2:11 How to quickly enter payment amount into excel
2:46 Column 3, current principal amount
3:00 Column 4, interest amount
3:10 Formula to calculate interest amount
3:24 How to calculate interest amount in excel
4:15 Modify interest amount calculation in excel
5:14 Column 5, principal reduction amount with formula
5:31 How to calculate principal reduction amount in excel
6:00 Column 6, balance of principal with formula
6:15 How to calculate balance of principal in excel
6:48 Start of 2nd row in the table
8:02 Quickly finish table
8:49 How to adjust last balance of principal amount
Transcript/notes (partial)
We are going to go through how to create an amortization table in excel
As an example, let’s say that someone purchases a house for $240,000, and puts $40,000 down, so, they have a mortgage of $200,000, at fixed rate of 9%, the loan is for 30 years and the number of payments per year will be 12, using the mortgage payment formula, as you see here, their monthly payment will be $1609.25 rounded off.
We are going to want to list these at the top of the spreadsheet, so we can reference their cell values when we create the table.
There are 3 major steps or calculations that we need to make, step 1, calculating the interest amount for each month, step 2, calculating the amount of the payment that goes towards the principal, and step 3, calculating the new principal amount after the payment.
In our table there are going to be 6 columns, the payment number, payment amount, current principal, interest amount, principal reduction, and balance of principal.
For the first column, payment number, this is going to be 1, 2, 3, and so on, all the way to 360, the total number of payments. An easy way to do this is to type in 1, then type in 2 beneath. Then, left click and hold and drag over both cells, then release the left click and hold, so both cells are highlighted. From here, go down to the bottom right hand corner of the highlighted cells, and you will see the cursor turn black, from here, left click and hold and drag down to where 360 will be. Then release the left click and hold and the values will be entered into the cells.
The second column, payment amount, this will always be the $1609.25 payment we calculated a moment ago, so, we can type that into cell B22 and hit the enter key. Since this is always going to be $1609.25, this value needs to go in this column for the whole table. To do this, we left click on cell B22, so it is highlighted. From here, go down to the bottom right hand corner of the highlighted cell, and again, you will see the cursor turn black, from here, left click and hold and drag down to the end of the table, to where payment number 360 is, and release the left click and hold, and the column has been filled in.
The third column, the current principal will be the principal amount before the current payment number. So, the current principal before payment number 1 will be $200,000.
The fourth column, the interest amount is the interest amount for 1 month on the current principal amount, and this number will change each month. The formula to calculate this is interest equals, the principal times the rate, times time, and remember, time is 1 month here, so this will be 1 over 12, and here is this calculation done by hand for the first month.
To do this calculation in excel, we start by left clicking on the cell we want to put the answer in, in this case cell D22, so it is highlighted. Next we type in an equals sign, now, referring to the formula, we left click on the principal amount, cell B2, then we type in a multiplication sign, which is shift 8 on the keyboard. From here we left click on the rate, cell B3, then we type in another multiplication sign.
Next, we type in an open parenthesis, then we type in a 1. Now we type in a division sign, which is a forward slash in excel. From here we left click on the number of payments per year, cell B5, then we type in a closed parenthesis. And now we hit the enter key, and we have our answer of $1500.
Before we move on, we are going to make a few modifications to the formula we just entered into cell D22, to make building the table much, much easier. We are going to left click on cell D22, so it is highlighted. Now, we are going to go up to the text in the formula bar and put the cursor in front of the first B and left click.
Chapters/Timestamps
0:00 Example set up
0:20 How to calculate monthly mortgage payment amount
0:35 3 major calculations in the table
0:50 The 6 columns in the table
1:00 Column 1, payment numbers
1:14 How to quickly enter payment numbers into excel
1:57 Column 2, payment amount
2:11 How to quickly enter payment amount into excel
2:46 Column 3, current principal amount
3:00 Column 4, interest amount
3:10 Formula to calculate interest amount
3:24 How to calculate interest amount in excel
4:15 Modify interest amount calculation in excel
5:14 Column 5, principal reduction amount with formula
5:31 How to calculate principal reduction amount in excel
6:00 Column 6, balance of principal with formula
6:15 How to calculate balance of principal in excel
6:48 Start of 2nd row in the table
8:02 Quickly finish table
8:49 How to adjust last balance of principal amount
12 ماه پیش
در تاریخ 1402/04/31 منتشر شده
است.
3,982
بـار بازدید شده