Excel Mortgage Calculation Tips

How to do Excel Loan Amortization

If you really want to analyze the nuts and bolts of a loan, you can use a spreadsheet such as Microsoft Excel. If you're an Excel whiz, you can set up a spreadsheet that allows you to change the inputs and calculate mortgage payments under several "what-if" scenarios. Your Excel mortgage model will show how your loan amortizes over time.

Start your Excel mortgage model by using cells at the top of your spreadsheet that you can enter information into and change if you want:

• Loan amount
• Interest rate
• Number of periods

I like to color those cells green, meaning I can change those values as I compare loans.

Then, number each row on the left side for each payment you’ll make. For large numbers such as 30 year loans, it’s easiest to use the "fill handle" and let Excel fill in the numbers for you.

Next, create columns for each piece of information:

• Starting loan balance
• Payment
• Amount applied to interest
• Amount applied to principal
• Remaining loan balance

Next, have Excel calculate the values for you. Remember to use the "$" when you refer to any row number in your calculations except the Period - otherwise Excel will look in the wrong row.

• Use the PMT function to calculate your monthly payment
• Use the IPMT function to show the amount of each payment that goes to interest
• Subtract the interest amount from the total payment to calculate how much principal you paid in that month
• Subtract the principal you paid from your loan balance to arrive at your new loan balance
• Repeat for each period (or month)

If your Excel mortgage model uses monthly payments, make sure you set up each period correctly in the formulas. For example, a 30 year loan has 360 total periods (or monthly payments). Likewise, if you’re paying an annual rate of 6%, you should make the periodic interest rate 0.5% (or 6% divided by 12 months).

If you don't want to do all the work of building an Excel mortgage model, there's an easier way. Use an online calculator:

• Loan Amortization Calculator