Estimate Mortgage Payments for Rental Property
I need to calculate the monthly mortgage payment on a $500k rental property with a 20% down payment and 6% interest.
=PMT(C2/12, D2*12, -(A2*(1-B2))) How it works: The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. * `C2/12` converts the annual interest rate (6%) to a monthly rate. * `D2*12` converts the loan term from years (30) to the total number of monthly payments. * `-(A2*(1-B2))` calculates the present value (PV) or the principal loan amount. `A2*(1-B2)` determines the amount financed after the down payment ($500,000 * (1 - 0.2) = $400,000). The negative sign is used so the PMT function returns a positive payment value, representing an outflow of cash.
Data Setup
| Property Value | Down Payment % | Annual Interest Rate | Loan Term (Years) |
|---|---|---|---|
| 500000 | 0.2 | 0.06 | 30 |
Step-by-Step Guide
Enter the total property value ($500,000) into cell A2.
Enter the down payment percentage (0.2 for 20%) into cell B2.
Enter the annual interest rate (0.06 for 6%) into cell C2.
Enter the loan term in years (e.g., 30 for a 30-year mortgage) into cell D2.
In a new cell (e.g., E2), enter the PMT formula: `=PMT(C2/12, D2*12, -(A2*(1-B2)))`.