Microsoft Excel Tutorial: How to Calculate Loan Payments and Create Amortization Tables in Excel | MrExcel Podcast.

Welcome to another episode of the MrExcel podcast. In this episode, we will be discussing loan amortization tables in Excel. With spring approaching, it's the perfect time to brush up on your knowledge of Excel's tools for calculating loans and amortization tables.

Let's start with a simple example. Say we have a loan amount of $200,000 to be paid over 15 years with a fixed interest rate of 3.9%. We want to calculate the monthly payments using the PMT function. This can be a bit confusing, so I recommend using the Insert Function wizard to guide you through the process. Remember to divide the interest rate by 12 and use the negative value for the present value to get the correct payment amount.

It's important to note that the PMT function does not include PMI, tax, and insurance. So, if you want to factor those in, you will need to adjust your calculations accordingly. You can also use the PMT function to explore different scenarios, such as a 30-year loan, to see how the payment amount changes.

Now, let's move on to calculating the amortization table. This table shows the breakdown of each payment into interest and principal, as well as the remaining balance after each payment. To get started, we need to calculate the interest and principal for each period. Instead of manually entering the numbers, we can use the ROW function to automatically generate the numbers 1, 2, 3, and so on. Then, we can use the IPMT and PPMT functions to calculate the interest and principal for each period.

To make things easier, we can copy these formulas down to the remaining rows. However, if we change the loan term to 30 years, we will need to copy the formulas again. To avoid this hassle, we can use IF statements to only show the calculations for the number of periods specified in the loan term. But for a quick and simple amortization table, copying the formulas is a great option.

I hope this episode has helped you understand how to use Excel to calculate loan payments and create amortization tables. Thank you for tuning in to the MrExcel podcast, and be sure to join us next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: [ Ссылка ]

You can help my channel by clicking Like or commenting below: [ Ссылка ]


Table of Contents:
(00:00) Loan Amortization Table
(00:13) Excel Tools for Calculating Loans and Amortization Tables
(00:23) Loan Calculation Using the PMT Function
(00:35) Confusion with PMT Function and Insert Function Wizard
(00:48) Adjusting Interest Rate and Number of Payments
(01:00) Calculating Amortization Table
(01:17) Reminder about Exclusions from Payment Amount
(01:32) Calculating Amortization Table
(01:42) Beginning Balance and Interest/Principal Calculation
(02:00) Using ROW Function and IPMT Function
(02:18) Calculating Principal with PPMT Function
(02:55) Copying Formulas for Entire Table
(03:13) Adjusting for Different Loan Terms
(03:38) Clicking Like really helps the algorithm


#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Amortization table calculation
Excel tools for calculating loans and amortization tables
Insert Function wizard
Interest and principal calculation for each period
Interest rate per period
Loan Amortization Table
Loan term and balance adjustments
Monthly payment calculation (P payment)
Monthly payments calculation (PMT)
MrExcel podcast
Present value (PV)


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! [ Ссылка ]

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

Join the MrExcel Message Board discussion about this video at [ Ссылка ]

свернуть