Download Practice Workbook
The dataset showcases Market Price of Asset, Total Loan Amount, Down Payment, Loan Repayment Tenure, and Rate of Interest.
Method 1 – Use the PMT Function to Create a Mortgage Calculator
The formula of the PMT function is
PMT(rate, nper, pv, [fv], [type])
Where
- Rate (required) – the constant interest rate per period (a percentage or decimal number).
- Nper (required) – the number of payments for the loan.
- Pv (required) – the present value: the borrowed amount.
- Fv (optional) – the future value after the last payment. If omitted, the future value of the loan is assumed to be zero.
- Type (optional) – specifies when payments are due.
- Select D10 and use the following formula.
- Press Enter.
=PMT(D9/12,D8*12,-D6)
Method 2 – Applying the PPMT Function to Generate a Mortgage Calculator
The syntax of the PPMT function is
PPMT(rate, per, nper, pv, [fv], [type])
A value for Period was added.
- Select D11 and use the following formula.
- Press Enter.
=PPMT(D10/12,D9,D8*12,-D6)
How to Create a Loan Amortization Schedule in Excel?
Combine the IPMT function and arithmetic calculations.
Create an amortization table with: Total Loan Amount, Loan Repayment Tenure, Payments Per Year, and Annual Rate of Interest.
Calculate the Payment amount using the PMT function.
- Enter the following function in C11 and press Enter.
=PMT($D$8/$D$7,$D$6*$D$7,$D$5)
- Drag the formula to column C.
To calculate the Principal, use the PPMT function.
- Select D11, enter the following formula, and drag it down.
=PPMT($D$8/$D$7,B11,$D$6*$D$7,$D$5)
To calculate the interest of each periodic payment, use the IPMTÂ function.
=IPMT($D$8/$D$7,B11,$D$6*$D$7,$D$5)
- Use the formula in column E.
To calculate the balance for each period, use two different formulas.
- To find the balance after the first payment in C11, add the loan amount (D5) and the principal of the first period (D11).
=D5+D11
- To find the balance after the second period, sum the previous balance and the principal of the second period.
=F11+D12
- Drag down the formula.
It will take 24 months to pay the loan.
Which Things Should You Remember?
- These examples are applicable to compound interest.
Frequently Asked Questions
1. How can I calculate the remaining mortgage balance after making payments in Excel?
Determine the remaining mortgage balance by deducting the principal paid from the original loan amount. By eliminating the interest component of the payment from the entire payment, you can determine the amount of the principal payment. Calculate the principal payment using the PPMT function.
2. How can I calculate the effective interest rate of a loan in Excel?
Use the RATE function to calculate the effective interest rate. The syntax is “=RATE(nper, pmt, pv, [fv], [type])“: “nper” is the total number of payment periods, “pmt” is the monthly payment, “pv” is the present value or loan amount, “[fv]” is the future value (optional), “[type]” indicates whether payments are due at the beginning or end of the period (optional).
3. Can I calculate loans with irregular payment amounts and schedules in Excel?
Yes, you can create a unique amortization schedule that takes into account various payment amounts and due dates.
Excel Mortgage Calculator: Knowledge Hub
<< Go Back to Finance Template |Â Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
