Question

You and your spouse purchased your first home immediately after the birth of your child. You...

  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 ___________________

  1. 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 _____________________

  1. Complete the following calculation

Current Market Value Residence      _____________________

80% Market Value                           _____________________

Current Principal Balance                _____________________

College Account Deposit                  _____________________

  1. 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.
  1. Assuming you entered into a new 30 year, 4.5% fixed rate mortgage, what is your new monthly mortgage payment?

Homework Answers

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

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
You have purchased a home for $200,000 with a down payment of 10 percent. Prevailing mortgage...
You have purchased a home for $200,000 with a down payment of 10 percent. Prevailing mortgage interest rates are 4.5 percent. If you finance the home for 30 years, what is your monthly payment? N I/Y PV PMT FV If you finance it for 20 years, what is your monthly payment? N I/Y PV PMT FV If you finance it for 15 years, what is your monthly payment? N I/Y PV PMT FV Assuming you choose a 20-year payment period...
I was looking at the solution to the following question on this site. I could not...
I was looking at the solution to the following question on this site. I could not understand why use 12 when working out the NPER. Since the monthly payments start 1 month after should you not use 11? Question 3. (a) A family member is thinking about funding his granddaughter’s university education in 8 years when she is expected to enrol at UWI, St. Augustine. He opens a special savings account, where he can receive a lump sum in 8...
10)You have just retired with $1,000,000 in savings. This is the amount that you will be...
10)You have just retired with $1,000,000 in savings. This is the amount that you will be drawing down for the rest of your life. You expect to earn 6% and withdraw $70,000 per year. You also want to leave an inheritance of $100,000 to your favorite charity. How long can you rely on your savings? Assume that your first withdrawal will occur one year from today. Use Excel’s =NPER(RATE,PMT,PV,[FV],[TYPE]) function. RATE is 6%, PMT is $70,000, PV is $1,000,000, FV...
Please answer Parts C & D. The findings are below. Thank you. C. Using your findings,...
Please answer Parts C & D. The findings are below. Thank you. C. Using your findings, discuss the impact of delaying making deposits into the IRA for 10 years (age 25 to age 35) on the amount accumulated by the end of Hal’s sixty-fifth year. D. Discuss the effect of beginning-of-year deposits on the future value accumulated by the end of Hal’s sixty-fifth year. Annual Deposit (PMT) 2,000 Return (RATE) 10% Years (NPER) 40 Amount Accumulated $885,185.11 Annual Deposit (PMT)...
A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an...
A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an annual fixed rate of 5.49% (15-year term) with monthly payments of $1,881.46. They just made their twenty-fifth payment and the current balance on the loan is $208,555.87. Interest rates are at an all-time low, and Dave and Jana are thinking of refinancing to a new 15-year fixed loan. Their bank has made the following offer: 15-year term, 3.0%, plus out-of-pocket costs of $2,937. The...
A company is considering the purchase of a piece of equipment that would cost $400,000 and...
A company is considering the purchase of a piece of equipment that would cost $400,000 and would last for 9 years. At the end of 9 years, the equipment would have a salvage value of $93,000. The equipment would provide annual cost savings of $73,000. The company requires a minimum pretax return of 17% on all investment projects. (Ignore income taxes.) Required: Provide your Excel input and the final net present value amount you calculated. (If a variable is not...
In your answers, you should properly show your work by writing down your entries into the...
In your answers, you should properly show your work by writing down your entries into the calculator. For instance, if you use the TVM worksheet of your financial calculator to compute how long it takes to double your account balance given 5% annual interest rate, you should write down your entries as: I/Y=5, PV=-1, PMT=0, FV=2, CPT N=? --- the question mark here stands for your answer to the question. Question 1 - Basic Setting [2 points] Change the number...
Nalu and Kamaile take-out a mortgage in the amount of $260000 to purchase an apartment as...
Nalu and Kamaile take-out a mortgage in the amount of $260000 to purchase an apartment as their principal place of residence. They are able to obtain a 15-year mortgage at a fixed rate of 6%. Below, you will be asked for the amount of their monthly payment and for a aggregated amount of interest that they paid. Clearly, this is a TVM (time value of money) problem, so get started by completing the table of "TVM Basic Data" c n...
You hope to be able to purchase a home within 1-3 years after graduation. You expect...
You hope to be able to purchase a home within 1-3 years after graduation. You expect to have to save for the necessary down payment and closing costs without relying on financial help from your family or relatives. Your financial goal is to purchase a home in the $300,000 price range and have the available cash for the down payment and required closing costs for the type of mortgage financing that you select and are able to receive home mortgage...
HW17. Suppose that beginning on your 25th birthday, you put $1,000 into a savings account, and...
HW17. Suppose that beginning on your 25th birthday, you put $1,000 into a savings account, and you make a new $1000 deposit every 3 months, up to and including your 45th birthday. The savings account pays 6% annual interest, compounded quarterly. \\ a. How much money will be in the account on your 45th birthday? b. How much will your first $1000 deposit end up contributing to the amount in your bank account, on your 45th birthday? c. The total...