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:

p=Principal=$600,000t=Time expressed in years=15r=APR=5.5%=0.055\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 monthly payment\textbf{monthly payment}, using the monthly payment formula:

Monthly payment=M=p(r12)(1+r12)12t(1+r12)12t1=$600,000(0.05512)(1+0.05512)12×15(1+0.05512)12×151$4,902.50\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 beginning balance\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 towards interest\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).

Towards interest=Beginning balance×r×One month (expressed in years)=Beginning balance×0.055×112\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 towards principle\textbf{towards principle} is then the monthly payment decreased by the amount paid towards interest:

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

Create a free account to view solutions

Create a free account to view solutions

Recommended textbook solutions

Financial Algebra 1st Edition by Richard Sgroi, Robert Gerver

Financial Algebra

1st EditionISBN: 9780538449670Richard Sgroi, Robert Gerver
2,606 solutions
Financial Algebra, Workbook 1st Edition by Richard Sgroi, Robert Gerver

Financial Algebra, Workbook

1st EditionISBN: 9780538449700Richard Sgroi, Robert Gerver
773 solutions
Financial Algebra: Advanced Algebra with Financial Applications 2nd Edition by Richard Sgroi, Robert Gerver

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 Edition by Robert Gerver

Practice Problems for Financial Algebra: Advanced Algebra with Financial Applications

2nd EditionISBN: 9781337271820Robert Gerver
898 solutions

More related questions

1/4

1/7