Excel for Real Estate: Property Analysis, Comparables, and Investment Calculations

Mortgage Payment Calculations

Calculate monthly mortgage payment:

=PMT(AnnualRate/12, LoanTermYears*12, -LoanAmount)

Total interest over life of loan:

=(MonthlyPayment * NumberOfPayments) - LoanAmount

Amortization Schedule

Build a payment schedule with:

• Payment number (1 to total payments)
• Payment amount (fixed with PMT)
• Interest portion: =PreviousBalance * (Rate/12)
• Principal portion: =Payment - InterestPortion
• Remaining balance: =PreviousBalance - PrincipalPortion

Comparative Market Analysis (CMA)

Track comparable properties with:

• Address and listing date
• Square footage, bedrooms, bathrooms
• List price and sold price
• Price per square foot: =SoldPrice/SqFt
• Days on market
• Adjustments for features (garage, pool, etc.)

Average adjusted sale price: =AVERAGE(AdjustedPrices)

Rental Property Analysis

Key rental metrics:

• Gross Rent Multiplier: =PurchasePrice/AnnualRent
• Cap Rate: =(AnnualRent - AnnualExpenses)/PurchasePrice
• Cash-on-Cash Return: =AnnualCashFlow/CashInvested
• Monthly Cash Flow: =MonthlyRent - Mortgage - Expenses

Investment Return Calculations

Total return on investment:

=((SalePrice - Expenses - PurchasePrice) + TotalCashFlow) / CashInvested

Internal Rate of Return (IRR) for multiple years:

=IRR(CashFlowRange) — include initial investment as negative

Expense Tracking

Common rental property expenses to track:

• Property taxes and insurance
• Property management fees (typically 8-10%)
• Maintenance and repairs
• Vacancy allowance (typically 5-8%)
• HOA fees if applicable
• Utilities (if landlord pays)

Official Resources

• PMT function — mortgage payment calculations
• IRR function — investment return calculations