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
9 thoughts on “Free Loan Calculation Spreadsheet”
Comments are closed.