Estimated Purchase Price
The current value of the house is $178,000 including 2% closing costs, which is equal to $3,490 (178,000-178,000/1.02). The inflation is estimated to be 2.50% per year. The purchase price of the house is determined by using the following formula in Excel.
FV(nper, rate, pmt, [pv], type) (Day, 2015)
Where nper = 5, rate = 2.50%, pmt = 0, pv = 178,000, type = 0
FV = $201,390.66
Down Payment
The down payment is 20% of the future value of the house, i.e., $40,278.13. In order to achieve this amount in the next five years, John and Joan need to have an amount equal to $31,558.97 invested to earn a return of 5% every year. It is calculated by using the present value formula in Excel.
PV(nper, rate, pmt, [fv], type)
Where nper = 5, rate = 5%, pmt = 0, fv = (201,390.66*20% = 40,278.13), type = 1 (beginning of the period)
PV = $31,558.97
Closing, Moving, and Furniture Cost
The future value of the house will be $201,390.66, which includes 2% closing cost, i.e., 201,390.66 x 2% = $3,948.84. Moreover, the moving and furniture cost will be 10% of the down payment required, i.e., 30%. Therefore, the future value of the moving and furniture cost will be 201,390.66 x 30% = $60,417.20. To attain these amounts in the next five years, John and Joan need to invest $50,493.72 at 5% per year. It is calculated by using the present value formula in Excel.
PV(nper, rate, pmt, [fv], type) (Chandra, 2014)
Where nper = 5, rate = 5%, pmt = 0, fv =3,948.84, type = 0
PV (Closing cost) = $3,094.02
Where nper = 5, rate = 5%, pmt = 0, fv = 60,417.20, type = 0
PV (Moving and furniture cost) = $50,432.47
Total amount to be saved = $53,526.49
Saving Per Month
John and John require to save for the house value, closing cost, moving, and furniture cost, i.e., $265,757. Both individuals have a saving of $10,000, which will be deducted from the present value of the amount required at the end of the fifth year. The annual rate is adjusted on a monthly basis and the total number of periods is 60 (5 x 12) (Gitman, Joehnk, & ‎Smart, 2015). The present value of the future amount required is determined by using the following formula.
PV(nper, rate, pmt, [fv], type)
Where nper = 60, rate = 5%/12, pmt = 0, fv = 261,808, type = 1
PV = $204,002.10
Less:
Current saving = $10,000
Balance amount required = $194,002.10
Saving per month required = $194,002.10 / 60 = $3,233.37 per month
Additional Return
If John changes his plan at E-Trade and earns an additional 1.5% risk-free return, then the total return he can earn becomes 6.5% per annum. It will be adjusted on a monthly basis as 6.5%/12 = 0.541%. The present value of the future amount required is determined by using the following formula.
PV(nper, rate, pmt, [fv], type)
Where nper = 60, rate = 6.5%/12, pmt = 0, fv = 261,808, type = 1
PV = $204,002.10
Less:
Current saving = $10,000
Balance amount required = $189,329.32
Saving per month required = $189,329.32 / 60 = $2,988.82 per month
It shows that John will have to save less amount every month due to the additional return he earns to achieve his objective of owning a house by the end of the fifth year (Harvey, 2015).
References
Chandra, P. (2014). Fundamentals of financial management. New Delhi, India: McGraw Hill Education (India) Pvt. Ltd.
Day, A. (2015). Mastering financial mathematics in Microsoft Excel. Harlow, UK: Pearson UK.
Gitman, ‎L. J., Joehnk, M. D., & ‎Smart, S. J. (2015). Fundamentals of investing (13th ed.). Sydney, Australia: Pearson Higher Education AU.
Harvey, G. (2015). Excel 2016 all-in-one for dummies. Hoboken, NJ: John Wiley & Sons.