Loan Amortization Worksheet‚ My Pick for #1 Most Useful Excel Spreadsheet Ever

Nine years ago I began working full time in the real estate investment field. While I use an HP 12C calculator and I like it a lot, I find it cumbersome to use for common real estate calculations. With a little help from Google, I found the attached spreadsheet online which I have adapted for my own use. Perhaps you will find it useful, too. Please click here to download the excel file.

What is it good for? This spreadsheet comes into play when you are interested in determining the cash flow you will receive from buying income property and financing that purchase with a loan. To do this, it is really helpful to know the mortgage constant for the loan given the interest rate and amortization period. This spreadsheet gives you that information, and it shows you exactly where all the money goes (unlike the HP 12C which just gives you the amount of the payment, if you are able to remember the sequence of buttons to press correctly).

How it works On the tab entitled, "Worksheet," you enter the items in red. Specifically, you need the interest rate on the loan that you can obtain; and the amortization period. You can enter the actual loan amount, although I usually don't bother because I am interested in the mortgage constant, not the actual monthly payment.

Why does the mortgage constant matter so much? As a buyer of income property, investors should be very interested in the relationship between the capitalization rate and the mortgage constant on the loan they can obtain for the acquisition. We like situations where the cap rate is higher than the mortgage constant, because this means our cash-on-cash return will be higher than the cap rate. This situation is known as "positive leverage" in the world of income property investment.

If you are interested in learning more, please contact us and we would be happy to go into more detail on this subject. The topic is addressed in some detail in my book, which is available on Amazon (click here if interested).