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 + 0.00485 = 1.00485$$
$$1.00485^{-360} = \frac{1}{1.00485^{360}}$$
Calculate $1.00485^{360}$:
$$1.00485^{360} \approx 5.849$$
So,
$$1.00485^{-360} = \frac{1}{5.849} \approx 0.171$$
7. **Calculate denominator:**
$$1 - 0.171 = 0.829$$
8. **Calculate numerator:**
$$0.00485 \times 770000 = 3734.5$$
9. **Calculate monthly payment:**
$$\text{PMT} = \frac{3734.5}{0.829} \approx 4506.39$$
10. **Interpretation:** The monthly payment is approximately $4506.39.
**Final answer:**
$$\boxed{4506.39}$$