- Once you have populated all the cells in yellow, please examine the results on the right hand side of the page (beginning in column I). If you entered all of the correct amounts in the cells on the left hand side of the page you should be able to properly analyze the right hand side of the Excel spreadsheet. What do you think Rebecca Young should do? Why?
- What are the key factors that Rebecca Young needs to consider in making her decision to rent or buy?
Question Purchase Price Legal fees Deed transfer tax (@3%) Down Payment (20%) Total Due at Closing Amortization Period (Years) Payment Frequency Number of Payments remaining (4) Principal Outstanding Initially After 2 years After 5 Years After 10 Years $600,000 2000 18000 120000 140000 Enter as +C7+C8+C9 25 23 20 15 12 12 12 12 300 276 240 180 Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (5a) NPV - No Change in Price 2 years 5 years 10 years $600,000 $600,000 $600,000 $30,000 $30,000 $30,000 $2,000 $2,000 $2,000 $456,609 $417.859 $342.109 $111,391 $150,141 $225,891 Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing $140,000 $34,727 $140,000 $92,259 $140,000 $204,722 Mortgage Amount $480,000 Princ OS $456,609.29 $417,858.79 $342,109.01 $ Net FV 63336.72 82117.83 118831.30 PV of Future Net (NPV) 58513.34 67365.22 79970.06 Quoted Rate Eff. Monthly rate 4% 0.0033059 Monthly Mortgage Payments $2,524.90 Monthly Condo Fees Monthly Taxes Monthly Repairs etc. $1,055 $300 $50 Make sure this amount is monthly Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (5b) 10% down, back to 600k, then up 10% 2 years 5 years 10 years 540000.00 600,000.00 660,000.00 27,000.00 30,000.00 33,000.00 2,000.00 2,000.00 2,000.00 456.609.29 417.858.79 342.109.01 54,390.71 150,141.21 282,890.99 Total Monthly Payments if Buy $3,929.90 140,000.00 34,727.42 Monthly Rent Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing $3,000 140,000.00 92,259.04 140,000.00 204,722.29 Net Additional Payments $929.90 [Note: This is a formula which results in a number] Net FV 120,336.72 82,117.83 61,831.30 (2) PV of Future Net (NPV) 111,172.53 67,365.22 41,610.69 Monthly Opportunity Costs of (Closing Costs + Down Pmt) + 462.82 (3) TOTAL Additional Monthly CF "costs" $1,392.72 Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (5c) Condo price increases 2% annually 2 years 5 years 10 years 624,240.00 662,448.48 731,396.65 31,212.00 33,122.42 36,569.83 2,000.00 2,000.00 2,000.00 456.609.29 417.858.79 342.109.01 134,418.71 209,467.26 350,717.81 Note: Any tax benefit or expense has been ignored in this analysis Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing 140,000.00 34,727.42 140,000.00 92,259.04 140,000.00 204,722.29 Net FV 40,308.72 22,791.77 5,995.52 PV of Future Net (NPV) 37,239.02 18,697.19 4,034.81 Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (50) Condo price increases 5% annually 2 years 5 years 10 years 661,500.00 765,768.94 977,336.78 33,075.00 38,288.45 48,866.84 2,000.00 2,000.00 2,000.00 456.609.29 417.858.79 342.109.01 169,815.71 307,621.70 584,360.93 Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing 140,000.00 34,727.42 140,000.00 92,259.04 140,000.00 204,722.29 Net FV 4,911.72 75,362.66 239,638.63 Question Purchase Price Legal fees Deed transfer tax (@3%) Down Payment (20%) Total Due at Closing Amortization Period (Years) Payment Frequency Number of Payments remaining (4) Principal Outstanding Initially After 2 years After 5 Years After 10 Years $600,000 2000 18000 120000 140000 Enter as +C7+C8+C9 25 23 20 15 12 12 12 12 300 276 240 180 Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (5a) NPV - No Change in Price 2 years 5 years 10 years $600,000 $600,000 $600,000 $30,000 $30,000 $30,000 $2,000 $2,000 $2,000 $456,609 $417.859 $342.109 $111,391 $150,141 $225,891 Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing $140,000 $34,727 $140,000 $92,259 $140,000 $204,722 Mortgage Amount $480,000 Princ OS $456,609.29 $417,858.79 $342,109.01 $ Net FV 63336.72 82117.83 118831.30 PV of Future Net (NPV) 58513.34 67365.22 79970.06 Quoted Rate Eff. Monthly rate 4% 0.0033059 Monthly Mortgage Payments $2,524.90 Monthly Condo Fees Monthly Taxes Monthly Repairs etc. $1,055 $300 $50 Make sure this amount is monthly Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (5b) 10% down, back to 600k, then up 10% 2 years 5 years 10 years 540000.00 600,000.00 660,000.00 27,000.00 30,000.00 33,000.00 2,000.00 2,000.00 2,000.00 456.609.29 417.858.79 342.109.01 54,390.71 150,141.21 282,890.99 Total Monthly Payments if Buy $3,929.90 140,000.00 34,727.42 Monthly Rent Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing $3,000 140,000.00 92,259.04 140,000.00 204,722.29 Net Additional Payments $929.90 [Note: This is a formula which results in a number] Net FV 120,336.72 82,117.83 61,831.30 (2) PV of Future Net (NPV) 111,172.53 67,365.22 41,610.69 Monthly Opportunity Costs of (Closing Costs + Down Pmt) + 462.82 (3) TOTAL Additional Monthly CF "costs" $1,392.72 Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (5c) Condo price increases 2% annually 2 years 5 years 10 years 624,240.00 662,448.48 731,396.65 31,212.00 33,122.42 36,569.83 2,000.00 2,000.00 2,000.00 456.609.29 417.858.79 342.109.01 134,418.71 209,467.26 350,717.81 Note: Any tax benefit or expense has been ignored in this analysis Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing 140,000.00 34,727.42 140,000.00 92,259.04 140,000.00 204,722.29 Net FV 40,308.72 22,791.77 5,995.52 PV of Future Net (NPV) 37,239.02 18,697.19 4,034.81 Selling Price Realtor Fees (@5%) Other Selling fees Princ OS Net Proceeds (50) Condo price increases 5% annually 2 years 5 years 10 years 661,500.00 765,768.94 977,336.78 33,075.00 38,288.45 48,866.84 2,000.00 2,000.00 2,000.00 456.609.29 417.858.79 342.109.01 169,815.71 307,621.70 584,360.93 Funds at Closing Opportunity Cost of Extra Payments Incl. Funds Used at Initial Closing 140,000.00 34,727.42 140,000.00 92,259.04 140,000.00 204,722.29 Net FV 4,911.72 75,362.66 239,638.63
