In the United States, Spring season is traditionally the New Home buying season. The question is: "Have you ever wanted to learn how to calculate a loan payment on your own?" Today, in Episode #1664, Bill shows us how to calculate loan payments on the Loan Amount as well as a monthly breakdown over the life of the loan using Excel 2013.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, episode 1664. Loan Amortization Table.
Here in the USA it's getting close to spring spring is traditionally the new home buying season.
So, let's take a look at Excels tools for calculating loans and amortization tables.
Okay so, we have a loan amount of $200,000 pay for it over 15 years, 180 months, 3.9 percent fixed interest rate.
And we want to calculate the monthly payments, that's equal PMT.
PMT is one of the more confusing things I like to go into the Insert Function, wizard. Because they actually walk us through the rate is the interest rate per period for the loan 6% divided by 4 for quarterly payments.
Okay well, so we take that rate that the bank gives us the 3.9 percent and we need to divide by 12 that's the, that's the first thing that gets you and then n pair total no more payments for the loan.
That's the term and then PV this is the present value this has to be negative if you want your payment amounts, to appear as part of the sub minus sign B1 there and we'll have our answer 1469.37.
Okay and just be careful that excludes PMI, Tax and Insurance! and of course you can do all kinds of what if what if we go with a 30 year loan.
All right, the payment comes down and so on, but how do we calculate the amortization table.
All right! So, we start out our beginning balance is the loan amount.
So, we'll just get that started and then we have to calculate the interest in principle for each period.
All right! So, rather than put the number 1 here I'm going to use equal ROW of 1 colon 1 the reason I do that is as I copy that down it's going to change to 2, 3, 4, 5, 6 and then to calculate the interest equal I payment, I payment that's very similar to what we just did.
We're going to take that rate, I'll press F4.
So, you put those dollar signs in divided by 12 to get a monthly rate which period is the period over here in A8 comma and then the number of periods. We're going to use B2 again.
I'll press F4 to put the dollar signs in comma and the present values.
So minus the original amount and again press F4 there.
Close the parentheses, those other ones FV and type FV is like if there's some residual value that you didn't have to pay and type is whether we pay at the beginning of the month at the end of the month. It really is not that different.
Okay so, there's our interest the principle we could use equal P payment.
But it's just as easy to take our monthly payment that one.
F4 to lock it down minus the value to the left and that will get our principal for this particular month.
Our old balance cell above us minus the principal payment will get our new balance and now that we have this we can just copy this down not asking it down to 180 rows.
So, I'm going to hold down the Shift key and press Page Down a whole bunch of times watching that little tool tip there 184, 180, 322, 181 and Control+V to paste.
Let's take a look I'm going to go to the next corner of the selection.
So, Control+Period and that should get us down there I actually copied it one too far, one too far.
So, we'll get rid of that extra row and we're good to go.
Now of course, the hassle here is if you change to a 360, month loan 30 year loan.
You're going to copy this down again.
You could get around this by using all kinds of IF statements you know, if the row is greater than the term, then showed quote quote. But for just a quick little loan amortization table.
This is a great way to go.
Okay, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, episode 1664. Loan Amortization Table.
Here in the USA it's getting close to spring spring is traditionally the new home buying season.
So, let's take a look at Excels tools for calculating loans and amortization tables.
Okay so, we have a loan amount of $200,000 pay for it over 15 years, 180 months, 3.9 percent fixed interest rate.
And we want to calculate the monthly payments, that's equal PMT.
PMT is one of the more confusing things I like to go into the Insert Function, wizard. Because they actually walk us through the rate is the interest rate per period for the loan 6% divided by 4 for quarterly payments.
Okay well, so we take that rate that the bank gives us the 3.9 percent and we need to divide by 12 that's the, that's the first thing that gets you and then n pair total no more payments for the loan.
That's the term and then PV this is the present value this has to be negative if you want your payment amounts, to appear as part of the sub minus sign B1 there and we'll have our answer 1469.37.
Okay and just be careful that excludes PMI, Tax and Insurance! and of course you can do all kinds of what if what if we go with a 30 year loan.
All right, the payment comes down and so on, but how do we calculate the amortization table.
All right! So, we start out our beginning balance is the loan amount.
So, we'll just get that started and then we have to calculate the interest in principle for each period.
All right! So, rather than put the number 1 here I'm going to use equal ROW of 1 colon 1 the reason I do that is as I copy that down it's going to change to 2, 3, 4, 5, 6 and then to calculate the interest equal I payment, I payment that's very similar to what we just did.
We're going to take that rate, I'll press F4.
So, you put those dollar signs in divided by 12 to get a monthly rate which period is the period over here in A8 comma and then the number of periods. We're going to use B2 again.
I'll press F4 to put the dollar signs in comma and the present values.
So minus the original amount and again press F4 there.
Close the parentheses, those other ones FV and type FV is like if there's some residual value that you didn't have to pay and type is whether we pay at the beginning of the month at the end of the month. It really is not that different.
Okay so, there's our interest the principle we could use equal P payment.
But it's just as easy to take our monthly payment that one.
F4 to lock it down minus the value to the left and that will get our principal for this particular month.
Our old balance cell above us minus the principal payment will get our new balance and now that we have this we can just copy this down not asking it down to 180 rows.
So, I'm going to hold down the Shift key and press Page Down a whole bunch of times watching that little tool tip there 184, 180, 322, 181 and Control+V to paste.
Let's take a look I'm going to go to the next corner of the selection.
So, Control+Period and that should get us down there I actually copied it one too far, one too far.
So, we'll get rid of that extra row and we're good to go.
Now of course, the hassle here is if you change to a 360, month loan 30 year loan.
You're going to copy this down again.
You could get around this by using all kinds of IF statements you know, if the row is greater than the term, then showed quote quote. But for just a quick little loan amortization table.
This is a great way to go.
Okay, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
