Upon graduating from Argosy University with a degree in Finance, John Simple fou

Question

Upon graduating from Argosy University with a degree in Finance, John Simple found a great job as a banking officer with Capital Two Bank in Dallas. Although he and his partner Joan had several college loans that required payments, their goal was to set aside funds for the next five years so that they could get out of the small apartment in Irving, Texas. After reviewing the listings in the areas surrounding DFW and speaking to their bosses about possible transfers, John and Joan decided upon Plano as their desired future location for a home.

Based on house prices they received from a local realtor, they determined that the home they needed currently costs around $178,000 which includes 2% closing costs. To avoid paying Private Mortgage Insurance, John and Joan need to make a down payment of 20%. Since they are saving for the potential purchase, it will be five years before they buy the home. This gives them time to save for the down payment, moving, and furniture costs, which they estimate will be 10% more than the required down payment. They also expect home prices in Plano to continue to increase each year at 2.5% per annum rate as presented by their realtor.

John, being the finance graduate, wanted to adequately prepare for their future purchase and told Joan that he would take all this information and present an overview of how much would be required once their estimated purchase date became a reality. In addition, John would show how much money they needed to save each month in their house investment account at E-Trade, which averages 5% annual return.

Tasks:

showing all calculations using math or Excel functions.

What is the estimated purchase price of the home in 5 years?

How much would need to be saved for the down payment?

How much would need to be saved for closing, moving, and furniture costs?

Considering that they have $10,000 already saved (half of which was provided by Joan’s parents as a wedding gift), how much money do they need to save each month to reach their goal?

Suppose John could change investment plans at E-Trade and earn an additional 1.5% per annum without additional risk, how much money do they need to save each month to reach their goal? How much in investment dollars would they save by increasing their investment percentage?

Explanation / Answer

Ans. Part A

The amount in five years is $201,390.66. To calculate each gaol in value, the things which are present right now, and will be needed are as follows:

Current value of the house : $178,000 with 2% closing costs

Current value of clossing cost : $3490.20

Part a In this the assumption is that the closing amount 2% is included as it is required to close in on the deal

Either the formula of FV = (rate,nper,pmt,-pv,type)

Here rate is 2.50%, nper taken for each year is 1 and for FV in five years it is 5

PMT =o

PV=-$178,000

Type = end of the year which is 0

Part B

The amount is $31,558.97. The down payment is 20% of the total amount . So, in five years the total amount will be $201,000

In this case the downpayment in fifth yearh will be 20% of $201,000 which is  $40,278.13

In today's term is $31,558.97. Joan would require the savings of 31,558.78 in today's term or $40,278 after five years.

Formula used is PV=(rate,nper,pmt,fv,type)

Here Rate= 5% (rate of investment in E-Trade)

nper=5

pmt=0

FV=40,278.13

Type= 1 (Beining of the year. As Joan will need the amount at the begining of the year. )

Part C

Saving today is $50,432.47. The downpayment is 20% and the moving cost is 30% of the total cost of the house which also included the closing amount(10% more than the downpayment)

Closing price is $3490.20 (today's term)

The formula used is

PV: (rate,nper,pmt,fv,type)

Rate: 5%

nper: 5%

pmt:0

FV: $64,366

Type: 1 (Begining of the period)

Part D

Saving needed is $3667.13. Considering they have $10,000 now, we can calclae the amount needed each month to be saved by reducing the $10,000 amount from the total expense in today's term, and then use the remaining amount to calculate the amount needed to be saved per month

First the present value of the total cost inclusion of furniture cost and the value of the house (inclusion of the closing amount) is calculated taking

PV: (rate,nper,pmt,fv,type)

Rate:5%

nper:5 years

pmt:1 (Begining of the period)

fv:$261,808

This comes out to $205,133.33. After thaht reduced the $10,000 present with the couple. So the total amount required now is $195,133.33

To calculate the per month saving, the formula used is PMT

PMT:(rate,nper,pmt,pv,fv,type)

rate: 5%/12 (as per month is needed to be calculated and 5% is an annual rate

nper : 60 (5*12)

pmt:0

pv: -$195,133.33

fv:0

type: 1 (Begining of the year)

Part E

The saving is $3524.12. In this part the only difference is that the E-Trade will be giving an additional 1.5% (from 5% to 6.5) return which is risk free, so we will use this in the abovoe situation to calculate the per month saving required. As the return is more, the couple will require less amount to save

To calculate the per month value the formula is the same as above PMT

PMT:(rate,nper,pmt,pv,fv,type)

Rate: 6.5%/12

Nper:60

pmt:0

PV: -$261,808

FV:0

Type:1(Begining of the period)

Year 0 1 2 3 4 5 Current House value 178000 $182,450.00 $187,011.25 $191,686.53 $196,478.69 $201,390.66 Inflation for house 2.50% FV in five years $201,390.66

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.