Chapter 36: Working Out Mortgage Repayments
Chapter Description
In this chapter, you’ll learn how to calculate mortgage repayments in Microsoft Excel using built-in financial functions and formulas. Excel provides powerful tools for estimating monthly loan payments, calculating total interest, and analyzing different repayment options, making it an ideal solution for personal finance and loan planning.
You’ll explore how to use the PMT function to calculate regular mortgage payments, understand the relationship between loan amount, interest rate, and repayment period, and experiment with different loan scenarios. You’ll also learn how to create mortgage repayment schedules and use What-If Analysis tools to evaluate the impact of changing interest rates or loan terms.
This lesson is especially useful for home buyers, financial planners, accountants, loan officers, business professionals, and anyone planning or managing a mortgage or other long-term loan.
What You’ll Learn
-
Understanding mortgage repayment calculations in Microsoft Excel
-
Using the PMT function to calculate monthly loan payments
-
Entering loan amount, interest rate, and repayment period
-
Calculating total repayment and total interest paid
-
Creating a basic mortgage repayment schedule
-
Comparing different loan terms and interest rates
-
Using What-If Analysis to evaluate repayment scenarios
-
Best practices for building accurate mortgage calculators
Learning Outcome
By the end of this chapter, you’ll be able to confidently calculate mortgage repayments in Microsoft Excel using financial formulas and functions. You’ll analyze different loan scenarios, estimate repayment costs, and create accurate mortgage worksheets that support informed financial planning and decision-making.
