Question

Chandler and Monica Bing wish to buy a new home. The listing price is $387,500 and they plan to put 20% down. New Rochelle Savings and Loan will lend them the remainder at a 9% fixed APR for 30 years, with monthly payments to begin in one month. For simplicity, let’s ignore closing costs and taxes when solving this question.

a).    How much will their monthly payments be?

b).  Suppose Chandler and Monica wants to pay off the loan in 15 years. How much extra must they pay each month to do so, if the APR remains at 9% and there is no early payment penalty?

c 1). Suppose they chose the 30 year plan and 20 years have passed since the loan started. They have made the past 240 payments exactly on time. What is the remaining balance on their loan now?

c 2). Chandler and Monica’s rich actor friend Joey decides to help them pay a lump sum $100,000 on their remaining balance. After the help from this generous friend, what is their remaining balance at year 20?

c 3). If Chandler and Monica decide to make equal monthly payment for the 10 years left for the remaining balance after this generous help from Joey, what will their monthly payment be?

Answer 1

Price of the home = $387,500

Down payment = 20% of price of home = 20% of $387,500 = $77,500

Loan amount = Price of the home - Down payment = $387,500 - $77,500 = $310,000

Annual interest rate = 9%

Monthly interest rate = 9%/12 = 0.75%

Loan period = 30 years = 360 months

a)

Monthly payment can be calculated using PMT function in spreadsheet

PMT(rate, number of periods, present value, future value, when-due)

Where, rate = Monthly interest rate = 0.75%

number of periods = Loan period = 30 years = 360 months

present value = Loan amount = $310,000

future value = 0

when-due = when is the payment made each month = end = 0

Monthly payment = PMT(0.75%, 360, -310000, 0, 0) = $2,494.33

b)

New loan period = 15 years = 180 months

The new monthly loan payment can be calculated using PV function in spreadsheet

PMT(rate, number of periods, present value, future value, when-due)

Where, rate = Monthly interest rate = 0.75%

number of periods = Loan period = 15 years = 180 months

present value = Loan amount = $310,000

future value = 0

when-due = when is the payment made each month = end = 0

New Monthly payment = PMT(0.75%, 180, -310000, 0, 0) = $3,144.23

Additional monthly payment = New Monthly Payment - Original monthly payment

= $3,144.23 - $2,494.33 = $649.90

c)

1)

Remaining loan period = 10 years = 120 months

'Remaining loan balance can be calculated using PV function in spreadsheet

PV(rate, number of periods, payment amount, future value, when-due)

Where, rate = Monthly interest rate = 0.75%

number of periods = Remaining Loan period = 120 months

payment amount = monthly payment = $2,494.33

future value = 0

when-due = when is the payment made each month = end = 0

Remaining loan balance = PV(0.75%, 120, -2494.33, 0, 0) = $196,906.64

2)

Help from Joey = $100,000

Remaining balance = remaining balance from part 1) - Help from Joey = $196,906.64 - $100,000 = $96,906.64

3)

New Monthly payment can be calculated using PMT function in spreadsheet

PMT(rate, number of periods, present value, future value, when-due)

Where, rate = Monthly interest rate = 0.75%

number of periods = Remaining Loan period = 10 years = 120 months

present value = Remaining Loan Balance = $96,906.64

future value = 0

when-due = when is the payment made each month = end = 0

New Monthly payment = PMT(0.75%, 120, -96906.64, 0, 0) = $1,227.57