The first model I technically built was probably in college all the way back in 2009 when we had to code in a simple amortization schedule for a loan. I remember it was not that easy and took me a few tries to make it work right. We also studied IRR (internal rate of return) and little did I know how important that metrics would be in my professional career at the time. So, today we are going to look at doing an amortization schedule for seller financing and all the relevant pieces it should have.
Relevant Template:
A seller financing spreadsheet should include several key aspects to ensure that all relevant information is captured accurately and that the seller can make informed decisions about the financing agreement. Some of the aspects that a seller financing spreadsheet should have are:
- Loan Amount: The total amount being financed by the seller.
- Down Payment: The amount of money the buyer is paying upfront as a down payment.
- Interest Rate: The annual interest rate charged on the loan.
- Term: The length of the loan in years or months.
- Amortization Length: This could be different than the term if you have a balloon payment situation where the amortization is based on 20 years or 30 years or what have you, but the loan is required to be fully paid off at the end of year 5.
- Monthly Payment: The amount the buyer will pay each month to the seller.
- Interest and Principal need to be broken out so you have to use Excel's PMT function or PPMT function. This is also important for tax purposes for both buyer and seller.
Amortization Schedule: A table showing the breakdown of each payment made by the buyer, including the portion that goes towards the principal and the portion that goes towards the interest. This is where you code the first line and then you can drag it down and if you did all the formulas right, it will go to 0 at the end of the term and show all the right payment amounts.
Total Payments: The total amount the buyer will pay over the life of the loan, including principal and interest. You can do this with a simple sum function.
Return of Basis vs. Gain Calculation: As the seller of the property, you will have some various tax effects that need to be calculated over time. To do this, simply take the total sales price you are selling the property for and subtract your cost basis. The percentage gain is the multiplied by all principal repayments and the down payment. That amount will be your taxable capital gains and everything else will be return of basis (not taxable). There is also interest payments, which will be taxable as regular earned income. Another aspect that is relevant at the moment of sale will be depreciation recapture, and there will be all sorts of nuance here depending on the depreciation method used, but generally you will have a tax on all the depreciation you deducted if you sold the property for more than the net book value. That can be off-set by the capital loss if you did sell it for less than the initial cost basis.
Balloon Payment: If the loan includes a balloon payment, the amount of the payment and when it is due should be included.
Late Payment Penalties: If the buyer is late in making a payment, the penalties and fees that will be charged should be included.
Default and Remedies: The consequences if the buyer defaults on the loan and the remedies available to the seller should be clearly stated.
Closing Costs: Any closing costs associated with the loan should be included.
Additional Terms and Conditions: Any other terms and conditions that are specific to the loan agreement should be included.
The key to making a good financial spreadsheet is simply understanding all the terms and all the important items that need to be calculated. Once you know that, it just takes a few simple formulas to put together and you'll have a great model going.
Overall, the seller financing spreadsheet should be comprehensive and provide all the necessary details of the financing agreement.
Article found in Real Estate.
