Creating a Mortgage Repayment Sheet in Excel
This guide demonstrates how to set up a mortgage repayment sheet in Excel from scratch. We’ll use functions like PMT to calculate payments and track interest, making it easy to visualize your mortgage amortization schedule.
Need Help with PMT, Loan Calculations and Excel Skills?
We offer live instructor-led Excel training for teams who need practical help with formulas, financial spreadsheets, reporting and everyday workbook tasks.
Step-by-Step Instructions for Mortgage Repayment Sheet
1. Enter Loan Amount
- In cell A1, type "Loan Amount" and enter 200,000. Format the cell for pounds and pence.
2. Enter Rate and Term
- In a new cell, type the rate (e.g., 5%) and the years (e.g., 25 years).
3. Calculate Monthly Payment with PMT
- Use the PMT function to calculate monthly payments, dividing the rate by 12 and multiplying the term by 12.
4. Set Up Monthly Schedule
- Label months from 1 to 400 and autofill them. Use formulas to calculate monthly interest, payments, and remaining balance for each month.
Benefits of a Mortgage Repayment Sheet
Creating a mortgage repayment sheet in Excel provides a clear view of how monthly payments affect the loan balance over time. It enables you to experiment with different rates, terms, and payment schedules to better understand your mortgage options.