CASE 120 Business and Property Spreadsheet Project he spreadsheet calculations s

Question

CASE 120 Business and Property Spreadsheet Project he spreadsheet calculations should be set up in a systematic manner. Your set-up should contain a list of the given values, and as many calculated values as possible. Make your spreadsheet as active' as possible by using cell references (so that i is changed, subsequent calculations will automatically update). Use absolute cell references in special situations Bob and Angelique Mackenzie bought a property valued at $84,000 for $15,000 down with the balance amortized over 20 years. The terms of the mortgage require equal payments at the end of each month. Interest on the mortgage is 3.4% compounded semi-annually and the mortgage is renewable after five years f one value a. What is the size of each monthly payment? b. Prepare an amortization schedule for the first five-year term. Make sure your values are rounded to the nearest cent Express totals at the bottom of each column as currency c. What is the cost of the debt during the first five-year term? d If the mortgage is renewed for a further five years at 4.2% compounded semi-annually, what will be the size of each monthly payment? he Mackenzie's also bought a business for $10,000. They borrowed the money to buy the business at 6.9% compounded semi-annually and are to repay the debt by making quarterly payments of $3645 e. How many payments are required to repay the loan? f. What is the term of the loan in years and months? g. Prepare a com plete amortization schedule for the loan. Make sure your values are rounded to the nearest cent. Express totals at the bottom of each column as currency. f. What is the principal reduction in the 6th year? i. What is the total cost of financing the debt? j If Angelique makes a lump sum payment of$10,000 at the end of the fourth year, by how nuch is the amortization period shortened?

Explanation / Answer

Time (in months) 20*12 240 Interest 3.4%*2/12 0.0057 Property Value 84,000 Down Payment 15,000 Amortized Balance 69,000 a. Monthly Payment $528.35 b. Amortization schedule With reference to the partial amortization schedule in the figure below, the portion of the first payment that represents interest is $393.30 (0.0057 x $69,000). The remainder of the payment, $ 133.40 ($526.70-393.90), goes toward the reduction of principal. The portion of the second payment that represents interest is $392.54 (0.0057 x $68866.60). The remaining $ 134.16 ($526.70 - $392.54) goes toward the further reduction of principal. Payment Number Initial Principal Monthly Payment Interest Component Reduction of principal Outstanding Principal 1 69000 $528.35 393.30 $135.05 $68,864.95 2 $68,864.95 $528.35 $392.53 $135.82 $68,729.13 3 $68,729.13 $528.35 $391.76 $136.59 $68,592.54 4 $68,592.54 $528.35 $390.98 $137.37 $68,455.17 5 $68,455.17 $528.35 $390.19 $138.16 $68,317.01 6 $68,317.01 $528.35 $389.41 $138.94 $68,178.07 7 $68,178.07 $528.35 $388.61 $139.73 $68,038.33 8 $68,038.33 $528.35 $387.82 $140.53 $67,897.80 9 $67,897.80 $528.35 $387.02 $141.33 $67,756.47 10 $67,756.47 $528.35 $386.21 $142.14 $67,614.33 11 $67,614.33 $528.35 $385.40 $142.95 $67,471.38 12 $67,471.38 $528.35 $384.59 $143.76 $67,327.62 13 $67,327.62 $528.35 $383.77 $144.58 $67,183.04 14 $67,183.04 $528.35 $382.94 $145.41 $67,037.63 15 $67,037.63 $528.35 $382.11 $146.24 $66,891.40 16 $66,891.40 $528.35 $381.28 $147.07 $66,744.33 17 $66,744.33 $528.35 $380.44 $147.91 $66,596.42 18 $66,596.42 $528.35 $379.60 $148.75 $66,447.67 19 $66,447.67 $528.35 $378.75 $149.60 $66,298.07 20 $66,298.07 $528.35 $377.90 $150.45 $66,147.62 21 $66,147.62 $528.35 $377.04 $151.31 $65,996.32 22 $65,996.32 $528.35 $376.18 $152.17 $65,844.14 23 $65,844.14 $528.35 $375.31 $153.04 $65,691.11 24 $65,691.11 $528.35 $374.44 $153.91 $65,537.20 25 $65,537.20 $528.35 $373.56 $154.79 $65,382.41 26 $65,382.41 $528.35 $372.68 $155.67 $65,226.74 27 $65,226.74 $528.35 $371.79 $156.56 $65,070.18 28 $65,070.18 $528.35 $370.90 $157.45 $64,912.73 29 $64,912.73 $528.35 $370.00 $158.35 $64,754.38 30 $64,754.38 $528.35 $369.10 $159.25 $64,595.14 31 $64,595.14 $528.35 $368.19 $160.16 $64,434.98 32 $64,434.98 $528.35 $367.28 $161.07 $64,273.91 33 $64,273.91 $528.35 $366.36 $161.99 $64,111.92 34 $64,111.92 $528.35 $365.44 $162.91 $63,949.01 35 $63,949.01 $528.35 $364.51 $163.84 $63,785.17 36 $63,785.17 $528.35 $363.58 $164.77 $63,620.39 37 $63,620.39 $528.35 $362.64 $165.71 $63,454.68 38 $63,454.68 $528.35 $361.69 $166.66 $63,288.02 39 $63,288.02 $528.35 $360.74 $167.61 $63,120.41 40 $63,120.41 $528.35 $359.79 $168.56 $62,951.85 41 $62,951.85 $528.35 $358.83 $169.52 $62,782.33 42 $62,782.33 $528.35 $357.86 $170.49 $62,611.84 43 $62,611.84 $528.35 $356.89 $171.46 $62,440.37 44 $62,440.37 $528.35 $355.91 $172.44 $62,267.93 45 $62,267.93 $528.35 $354.93 $173.42 $62,094.51 46 $62,094.51 $528.35 $353.94 $174.41 $61,920.10 47 $61,920.10 $528.35 $352.94 $175.41 $61,744.70 48 $61,744.70 $528.35 $351.94 $176.40 $61,568.29 49 $61,568.29 $528.35 $350.94 $177.41 $61,390.88 50 $61,390.88 $528.35 $349.93 $178.42 $61,212.46 51 $61,212.46 $528.35 $348.91 $179.44 $61,033.02 52 $61,033.02 $528.35 $347.89 $180.46 $60,852.56 53 $60,852.56 $528.35 $346.86 $181.49 $60,671.07 54 $60,671.07 $528.35 $345.83 $182.52 $60,488.54 55 $60,488.54 $528.35 $344.78 $183.56 $60,304.98 56 $60,304.98 $528.35 $343.74 $184.61 $60,120.37 57 $60,120.37 $528.35 $342.69 $185.66 $59,934.71 58 $59,934.71 $528.35 $341.63 $186.72 $59,747.98 59 $59,747.98 $528.35 $340.56 $187.79 $59,560.20 60 $59,560.20 $528.35 $339.49 $188.86 $59,371.34 d. Time (in months) 15*12 180 Interest 4.2%*2/12 0.0070 Amortized Balance $59,371.34 Monthly Payment $581.18

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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