Free Loan Calculation Spreadsheet

Purchasing a property investment involves most of the times taking a loan for a significant percentage of the purchase price in order to finance its acquisition. When evaluating the profitability and expected return of such an investment it is important to estimate the leveraged return of the property using the discounted cash flow model in which the monthly, quarterly or annual loan installments (depending on the nature of the periodic cash flows used int he model) need to be taken into account (see our post Property Investment Analysis: The Discounted Cash Flow Model for a more detailed discussion of the discounted cash flow model and how it can be used to calculate a leveraged return). Below we provide a free Loan Calculation Spreadsheet with embedded formulas that can help you do that automatically.

In particular, the Loan Calculation Spreadsheet Calculates Automatically the Monthly, Quarterly and Annual Mortgage Constants which are necessary for the calculation of respective periodic payments for a fixed-rate loan. Just enter the interest rate and number of years of your choice and the spreadsheet will automatically calculate all three mortgage constants.

With these inputs, the Spreadsheet calculates also automatically Month-by Month, Quarter-by-Quarter, and Year-by-Year full Loan Amortization Schedule with Beginning Balance, Mortgage Payment, Interest portion of payment, Principal portion of payment, and the Ending Balance of any Loan of up to 40 years. Just enter the Property Value, Loan to Value Ratio, the Loan Term (duration), and the interest rate, and the spreadsheet will automatically calculate the Loan Amount and the corresponding Monthly, Quarterly and Annual amortization schedules.

The spreadsheet provides also the following formulas for you independent own use as your deem appropriate:

Loan-To-Value Ratio
Monthly Interest Rate
Quarterly Interest Rate
Mortgage Constant
Mortgage Payment
Interest Payment
Principal Payment
Ending Balance
Beginning Balance

Click on the link below to download the spreadsheet.

Download the Free Loan Calculation Spreadsheet

 

Author: Petros Sivitanides, Ph.D.

Dr. Sivitanides is a seasoned expert in real estate investment strategy and analysis, property portfolio modeling and strategic analysis, and real estate market research and econometric forecasting with over 16 years of experience with leading global real estate investment managers and real estate consultants (CBRE Global Investors, AXA Real Estate, Torto Wheaton Research, DTZ, etc.). He is the editor of the textbook titled “Market Analysis for Real Estate”, which has been used as the main textbook for a graduate course at Harvard University. He is also the author of the book "Real Estate Investing for Double-Digit Returns" and many widely quoted articles that have been published in popular real estate journals. Currently, he is the Head of the Real Estate Department at Neapolis University in Cyprus, and an international real estate consultant.

9 thoughts on “Free Loan Calculation Spreadsheet

  1. Pingback: Property Investment Basics: Blanket Mortgages – Smart Property Investment
  2. Pingback: Property Investment Basics: Before-Tax Cash Flow – Smart Property Investment
  3. Pingback: Free Spreadsheet for Calculation of Before-Tax Cash Flow for Apartments – Smart Property Investment
  4. Pingback: Real Estate Financing Basics: Debt Coverage Ratio – Smart Property Investment
  5. Pingback: Property Investment Financing: Loan Amortization – Smart Property Investment
  6. Pingback: Before-Tax Cash Flow Calculation: Property Investment Analysis
  7. Pingback: Debt Coverage Ratio: A Key Metric in Commercial Property Financing
  8. Pingback: Return on Equity: The Most Important Return Measure when Using a Loan
  9. Pingback: Debt Service and Property Investment Performance

Comments are closed.