Advanced Mortgage Modeling – Video 1: Basic Amortization Structure

In this first video, we begin building the foundation for an advanced mortgage model by calculating the monthly payment (PMT) and constructing the basic amortization schedule. Topics covered include: Mortgage Payment Calculation Using the PMT function in Excel to calculate the required periodic payment. Inputs include: Loan amount (principal) Interest rate Loan term Payment frequency Periodic Interest Rate Converting the annual mortgage rate into a monthly interest rate. Building the Amortization Table Structuring the schedule with the following columns: Beginning Balance Payment Interest Principal Ending Balance Core Mortgage Mechanics Interest is calculated as: Interest=Beginning Balance×Periodic Interest Rate Interest=Beginning Balance×Periodic Interest Rate Principal paid each period: Principal=Payment−Interest Principal=Payment−Interest Ending balance: Ending Balance=Beginning Balance−Principal Ending Balance=Beginning Balance−Principal This schedule shows how each payment gradually shifts from mostly interest to mostly principal over time. If you want, I can also help you structure Video 2, which should naturally move into the advanced modeling features, such as: Extra principal payments Early payoff logic Dynamic PMT adjustments when the balance gets small Removing zeros when the loan is paid off early Return on prepayment calculations That sequence would fit perfectly with your SIM modeling approach that you use in your financial modeling courses.