Question: Use the PMT function in Excel to compute the monthly payment on a home each month if you borrowed $770,000 at an annual interest rate of 5.82% over 30 years, where the interest is compounded monthly.
Hint: The PMT (Payment) function is entered in Excel as =PMT(Rate, Nper, Pv, Fv, Type).
Fv and Type are not necessary. Ignore them.
Enter the amount of your monthly payment below
1. **State the problem:** Calculate the monthly payment for a loan of $770,000 with an annual interest rate of 5.82% over 30 years, compounded monthly.
2. **Formula used:** The PMT function in Excel calculates the payment for a loan based on constant payments and a constant interest rate:
$$\text{PMT} = \frac{r \times P}{1 - (1 + r)^{-n}}$$
where:
- $r$ is the monthly interest rate
- $P$ is the loan principal (present value)
- $n$ is the total number of payments
3. **Calculate monthly interest rate:**
$$r = \frac{5.82}{100 \times 12} = \frac{0.0582}{12} = 0.00485$$
4. **Calculate total number of payments:**
$$n = 30 \times 12 = 360$$
5. **Substitute values into the formula:**
$$\text{PMT} = \frac{0.00485 \times 770000}{1 - (1 + 0.00485)^{-360}}$$
6. **Calculate denominator:**
$$1 - (1 + 0.00485)^{-360} = 1 - (1.00485)^{-360}$$
Calculate $(1.00485)^{-360}$:
$$ (1.00485)^{-360} = \frac{1}{(1.00485)^{360}} \approx \frac{1}{5.849} = 0.171\quad \text{(approx)}$$
So denominator:
$$1 - 0.171 = 0.829$$
7. **Calculate numerator:**
$$0.00485 \times 770000 = 3734.5$$
8. **Calculate PMT:**
$$\text{PMT} = \frac{3734.5}{0.829} \approx 4506.39$$
9. **Interpretation:** The monthly payment is approximately $4506.39.
10. **Excel PMT function:**
In Excel, you would enter:
`=PMT(0.0582/12, 360, -770000)`
The negative sign before the principal is used because it represents money borrowed (cash outflow).
**Final answer:**
The monthly payment is approximately **4506.39**.