Question

# Use a spreadsheet to generate the last year of payments in a loan amortization table for a \$600,000, 15-year mortgage with an APR of 5.5%

Solution

Verified
Step 1
1 of 10

Given:

\begin{align*} p&=\text{Principal}=\600,000 \\ t&=\text{Time expressed in years}=15 \\ r&=APR=5.5\%=0.055 \end{align*}

We can determine the $\textbf{monthly payment}$, using the monthly payment formula:

\begin{align*} \text{Monthly payment}=M&=\dfrac{p\left(\dfrac{r}{12}\right)\left(1+\dfrac{r}{12}\right)^{12t}}{\left(1+\dfrac{r}{12}\right)^{12t}-1} \\ &=\dfrac{\600,000\left(\dfrac{0.055}{12}\right)\left(1+\dfrac{0.055}{12}\right)^{12\times 15}}{\left(1+\dfrac{0.055}{12}\right)^{12\times 15}-1} \\ &\approx \4,902.50 \end{align*}

The $\textbf{beginning balance}$ is initially the principle, since nothing has been paid previously. Afterwards, the beginning balance is the previous ending balance.

The amount paid $\textbf{towards interest}$ is the interest paid on the beginning balance. The interest is the product of the beginning balance, the interest rate and the time (one month).

\begin{align*} \text{Towards interest}&=\text{Beginning balance}\times r\times \text{One month (expressed in years)} \\ &=\text{Beginning balance}\times 0.055\times \dfrac{1}{12} \end{align*}

The amount paid $\textbf{towards principle}$ is then the monthly payment decreased by the amount paid towards interest:

\begin{align*} \text{Towards principle}&=\text{Monthly payment}-\text{Towards interest} \end{align*}

## Recommended textbook solutions

#### Financial Algebra

1st EditionISBN: 9780538449670Richard Sgroi, Robert Gerver
2,606 solutions

#### Financial Algebra, Workbook

1st EditionISBN: 9780538449700Richard Sgroi, Robert Gerver
773 solutions

#### Financial Algebra: Advanced Algebra with Financial Applications

2nd EditionISBN: 9781337271790Richard Sgroi, Robert Gerver
3,016 solutions

#### Practice Problems for Financial Algebra: Advanced Algebra with Financial Applications

2nd EditionISBN: 9781337271820Robert Gerver
898 solutions