Question

1. You and your spouse purchased your first home immediately after the birth of your child. You have lived in the home for the past 18 years, paying your mortgage on time, but otherwise struggled to accumulate savings to pay for college. Your child is off to college this fall and annual cost is estimated to be $70,000.

You originally paid $450,000 for your house, which has appreciated in price at 3.5% per annum. The original mortgage was a 30 year fixed rate, 6% loan. You are considering a cash out refinancing, which means you will borrow up to 80% of the current market value of your residence and after paying off the current outstanding mortgage principal balance you will deposit the remaining cash into an investment account, which will be invested at 2% per annum. You will pay your child’s college cost from the investment account, until they graduate or the account is depleted.

1. What is the current market value of your home? Please write out the excel formula you would utilize to make this calculation as well as the relevant inputs.

Rate =

Nper =

Pmt =

[pv] =

[fv] =

[type] =

Current Market Value ___________________

2. What is the current mortgage principal balance? Please write out the excel formula you would use to calculate the current mortgage balance. Fill in the relevant inputs.

Rate =

Nper =

Pmt =

[pv] =

[fv]=

[type] =

Current Mortgage Balance _____________________

3. Complete the following calculation

Current Market Value Residence      _____________________

80% Market Value                           _____________________

Current Principal Balance                _____________________

College Account Deposit                  _____________________

4. Prepare a four year cash budget for the college account. Assume the 2% return is credited at the end of the period and the payment to the college also occurs at the end of the period. Further, assume the annual $75,000 cost remains constant for the next four years. Calculate the end balance in the college account.

5. Assuming you entered into a new 30 year, 4.5% fixed rate mortgage, what is your new monthly mortgage payment?

Answer 1

A) What is the current market value of your home?

Rate = 3.5% (Rate at which price had appreciated on a per annum basis)

Nper = 18 years

Pmt = 0

PV = -$450,000 (Price paid to buy the house initially, '-' symbol since its a cash outflow)

type = 0 (price appreciation happens at the end of every year)

FV(Price after 18 years or the current market value of home) = FV(rate,nper,pmt,pv,type)

=FV(3.5%,18,0,-$450000,0) = $8,35,870.14

Current Market Value of Home = $8,35,870.14

2)Current Mortgage Balance

Assuming 30 year 6% fixed rate loan involves equal payments.

Payments (Pmt/EMI) = ((1.06)^30)*$450000/30 = $86,152.37 (Assuming annual compounding)

Rate = 6%

Nper = 18

Pmt = $86,152.37

[pv] = $450,000

[type] = 0

Current Mortgage Balance = FV(6%,18,-86152.37,450000,0) = $13,78,142.6