Calculating a land contract spreadsheet can be tricky for those who are unfamiliar with banking or accounting. However, the process of updating a land contract balance sheet is not difficult once it has been set up properly. This article leads the reader step-by-step through the process of setting up a land contract spreadsheet, how to apply payments, and how to maintain the spreadsheet until the loan is paid off.
Scenario:
Imagine someone buys your house for $110,000 at a 5% per annum interest rate on a land contract. They provide a $2000 down payment and finance the rest through you. They have agreed to pay $875 per month until the loan is paid off.
Step One: Set up the following five columns on a ledger or columnar sheet of paper:
(1) Date of Payment, (2) Payment Amount, (3) Amount of Payment Applied to Interest, (4) Amount of Payment Applied to Principal, (5) Principal Remaining
Step Two: In row one, establish the “Principal Remaining” as the $110,000 sales price in column (5).
Step Three: Apply the down payment.
Since a down payment is applied entirely toward principal, on a new line write the down payment of $2000 in columns (2) and (4) and subtract that amount from the previous line’s column (5). Write the new “Principal Remaining” in this line’s column (5).
Step Four: Apply a monthly payment.
Write the monthly payment on a new line next to the date paid. In this example, write $875 in column (2).
Step Five: Determine the amount of the payment that applies toward interest.
Applying a monthly payment is a bit tricky because only part of the payment applies toward principal. First it is important to find out how much of the payment applies to interest, then the remainder of the payment will pay down principal. Remember, apply the payment to interest first, then subtract to determine how much applies to the principal. It will change each month, so this will be a monthly calculation.
In layman’s terms, calculating how much of a monthly payment applies to interest requires two steps. First, multiply the previous line’s “Principal Remaining” by the interest rate as a decimal. (Ex. 108,000 * 0.05) Second, divide by 12 due to the interest being spread out over a full year and this only being a one month payment (Ex. (108,000 * 0.05)/12). Write this answer in column (3) next to the payment they are making.
Step Six: Determine the amount of the payment that applies toward the principal.
Since column (3) determined the amount of payment that applied toward interest that month, it follows that the remainder of the payment applies to principal. Therefore, subtract the amount of column (3) from the payment in column (2). Write the answer in column (4), the amount applied from the payment toward principal.
Step Seven: Recalculate the new principal remaining.
After identifying how much of the payment applies toward principal, subtract column (4) from the previous line’s “Principal Remaining”. Write your answer in the current line’s column (5).
Step Eight: Repeat until paid.
Repeat steps three through seven until the loan is paid off. An exception occurs, however, if a buyer wants to make an extra payment toward principal. When this happens, simply apply the extra payment toward principal and subtract from the “Principal Remaining” as you did with the down payment.
Following these steps should help with creating and maintaining a land contract. Maintaining a land contract does not need to be difficult or scary. Simply follow this guide and you should do great!
Have you ever dealt with a land contract? Did you do the calculations yourself or set up a spreadsheet on a computer to help? Please comment below.
