DIY Loan Calculator

Ever wonder how lenders figure out how much you need to pay them each month to pay off your loan? Just how do they know the amount to put towards interest and principal? It's not as complicated as you'd think, plus with Excel there's a really easy way to figure out your payments.

Jessi and I use this all the time when looking at properties. We use many metrics to determine if we're interested in a property, and one really important one is cash flow (money earned from rent minus expenses). One large component of a property's cash flow is the mortgage payment. We use a simple spreadsheet which needs three pieces of information to determine the monthly payment:

1. The loan amount (75%ish of the asking price). Also known as the starting Principal.
2. The annual interest Rate (I do some general searches to get an idea of current rates)
3. The term of the loan (I assume a 30 year fixed, or 360 Months)

The mathematical formula for determining your monthly payment is:

Payment = (Rate * Principal) / (1 - (1 + Rate)^{- Months})

Not too bad, right?

Excel makes it even easier with a built in function:

=PMT( Rate / 12 , Months , -Principal)

The principal needs to be negative because it's money you owe.

Once you know your payment, you can plug it into whatever you're using. We plug it into our cash flow spreadsheet. You can also use it to determine what your home or car payment would be.

The screen shot above shows the next steps:

• You know your payment
• Interest = (Rate / 12) * Previous Balance
• Principal is simply what remains of your payment: Payment - Interest
• Balance = Previous balance - Principal

The chart above shows what it looks like. At first the majority of your payment goes towards Interest, but over time it shifts to almost all Principal.

I uploaded a working example for both a home and car loan on Google Docs which you can download and play with. Just click the "Download Original" button at the top.

Pretty cool! There are a bunch of online tools for doing this, but it is nice to see it for yourself. Plus you can do so much more. What if at the end of each year you apply part of a bonus towards a payment? How much does that accelerate the loan? Once you understand how it works, you can do the opposite: What if you pay in $1,000 each year to a retirement account for 30 years with an annual return of 5%?