Operations > Entrepreneurs and Real Estate > Real Estate Finances
Modeling Your Real Estate Loan in Excel

Real Estate Articles

Modeling Your Real Estate Loan in Excel

Written by Brent Pace for Gaebler Ventures

Whether you are buying a car or a piece of real estate, brokers are always trying to get you focused on your loan payments. Focusing on payments alone, however, is a mistake. In this article you will learn how to create an Excel model for your loan to help you understand what is going on.

If you are taking out a loan for the purchase of real estate, you will want to understand how it works.

For most fixed-rate loans, developing an Excel model that shows the repayment schedule for the loan can be very informative. Here's an example of how you do it. You can enter your own loan terms and follow the same set of steps to get your amortization schedule.

1. Find the important terms.There will be a few important terms you need to figure out in order to set up your loan. These key terms are the loan amount, the interest rate, the number of years for repayment, and the number of payments in a year.

2. Enter the terms. In column A and B you will want to enter these important terms and a label describing what it is. Below is an example of what this should look like for a loan with an initial amount of $100,000, a 7% annual interest rate, 30 year term, and 12 payments in a year. Please note that the $100,000 loan is entered as a negative amount.

3. Compute the payment. To do this you want to put your cursor in cell A5. You then enter the following function: = PMT(A2/A4,A3*A4,A1,0). The result you get for your payment should be $665.30. Let's explain what we just did. The payment function takes your interest rate, loan amount, and payment structure into account to produce your monthly payment. You will note that we divided the interest rate by 12 (the A2/A4 term) because you are making monthly payments. We also multiplied the term by twelve (A3*A4) to have the loan compute the total number of payments. Now that you have your payment number we can set up the amortization table.

Set up the amortization table. Your amortization table should be set up below the terms. Here's an example of what it should look like for the first few periods.

The table should start with a column of periods. This will go all the way out to 360 in this case, one for each month. Period 0 has an ending balance equal to the starting loan amount (or = -$A$1). Entries for cells B9 thru F9 are as follows. In cell B9 enter = F8. In cell C9 enter = B9*($A$2/$A$4). In cell D9 enter = $A$5. In cell E9 enter = D9-C9. In cell F9 enter = B9-E9.

Then you can highlight cells B9 thru F9 and pull them down thru period (month) 360. When you do this cell F368 should read $0.00, demonstrating that at the end of 360 payments you will have paid off your loan in full.

Brent Pace is currently an MBA candidate at University of California at Berkeley. Originally from Salt Lake City, Brent's experience is in commercial real estate development and management. Brent will have tips for small business owners as they negotiate their real estate needs.