Question

Reference all values and label them properly on top of your worksheet.  No number typed in formulas.

Use an Excel file with two worksheets. Use each worksheet for amortization table and one for cash flow table.

• You are going to buy a car. There is a zero down payment and annual interest rate of 3.5% for the $28,000 car. You will pay back this loan in 7 years, with equal payments at the end of each month.
  1. Use a function for estimating monthly payments.
  2. Create an amortization table to show all monthly interest and principal payments, and remaining balance for each month.
  3. Create a table to list cumulative payments of interest and principal for each year.

• Build a cash flow table for your car if you use it for business. Use the following factors and calculate net cash flow for all 7 years:
  1. Life of the car: 7 years
  2. Salvage value: $2,000
  3. Annual revenue share of the car: $15,000 (determined by accounting department)
  4. Operating costs: 20% of the price of the new vehicle. Add $2,000 to operating costs of the first year for DMV tax and title.
  5. Tax rate is 25% of taxable income

*There is no “cost of goods sold for this cash flow table as it is used for service and has operating costs.

Answer 1

Answer-1 $ 28,000.00 Amount of loan financed Time period (in yrs) 7 Interest Rate 3.50% Monthly interest rate 0.29% No of installments 84 Monthly payment a mount -376.32 n

Answer-2 Amortization table (payment rounded to near cent) Interest expenses Monthly payment Principle repayment Outstanding Principle Month $ 294.65 $ 0 28,000.00 1S 81.67 $ 376.32$ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 27,705.35 2 $ 80.81 S 295.51 $ 27,409.83 3$ 4S 5 S 6 S 79.95 $ 79.08 $ 296.37 $ 297.24 S 27,113.46 26,816.22 298.11 $ 298.98 $ 78.21 26,518.11 77.34 $ 26,219.14 76.47 S 7$ 299.85 S 25,919.29 8 S 75.60 S 74.72 $ 73.84 S 72.96 $ 72.07 $ 300.72 S 301.60 $ 302.48 $ 303.36 $ 25,618.57 9 S 10 S 11 S 12 S 25,316.97 25,014.49 24,711.13 304.25 $ 305.13 $ 24,406.88 13 $ 14 S 15 $ 16 S 17 $ 18 $ 71.19 S 70.30 $ 69.40 $ 24,101.75 23,795.73 306.02 $ 306.92 $ 376.32 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32$ 376.32 $ 376.32 $ 376.32 $ 23,488.81 307.81 $ 308.71 S 309.61 $ 68.51 23,181.00 67.61 $ 66.71 $ 65.81 $ 64.90 $ 63.99 S 22,872.29 22,562.68 19 S 20 S 21 $ 310.51 $ 311.42 $ 22,252.17 21,940.75 312.33 S 21,628.43 22 S 313.24 S 314.15 S 63.08 S 62.17 $ 21,315.19 23 S 21,001.04 24 S 61.25 $ 60.33 $ 59.41 S 376.32 $ 315.07 $ 20,685.97 25 $ 26 $ 27 S 376.32 $ 376.32 $ 376.32 $ 376.32 $ 315.99 $ 316.91 $ 317.83 $ 318.76 S 20,369.99 20,053.08 19,735.25 58.49 28 S 57.56$ 56.63 $ 19,416.49 29 $ 30 $ 31 $ 32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 S 376.32 $ 376.32 $ 319.69 $ 19,096.80 55.70 $ 54.76 S 320.62 $ 321.56 $ 322.49 $ 323.43 $ 18,776.18 18,454.62 53.83 S 52.89 $ 51.94 S 18,132.13 33 S 17,808.69 34 $ 324.38 S 325.32 $ 326.27 $ 327.22 $ 328.18 S 17,484.32 35 $ 36 $ 37 $ 38 S 39 S 51.00 $ 50.05 $ 376.32 $ 376.32 $ 376.32$ 376.32 S 376.32 $ 17,158.99 16,832.72 49.10 S 48.14 S 47.18 $ 46.22 S 16,505.49 16,177.32 15,848.18 329.14 $ 330.10 $ 40 $ 376.32 $ 376.32$ 376.32 $ 15,518.08 41 S 42 $ 43 S 45.26 $ 44.30 $ 331.06 $ 332.02 S 332.99 $ 333.96 $ 334.94 S 15,187.02 14,855.00 43.33 S 376.32 14,522.01 44 $ 45 S 376.32$ 376.32 $ 376.32 $ 42.36 S 41.38 S 14,188.04 13,853.10 46 S 40.40 S 39.43 $ 38.44 $ 37.46 S 335.92 $ 13,517.19 47 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 336.89 S 337.88 $ 338.86 $ 339.85 $ 13,180.29 48 S 49 $ 50 $ 12,842.42 12,503.55 36.47 $ 12,163.70 51 S 340.84 S 35.48 S 34.48 S 33.49 $ 32.49 $ 11,822.86 52 $ 53 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32$ 376.32 $ 376.32 S 341.84 $ 342.83 $ 343.83 $ 344.84 $ 345.84 $ 11,481.02 11,138.19 54 $ 55 $ 10,794.36 10,449.52 31.48 56 S 30.48 S 10,103.68 57 $ 58 $ 29.47 $ 346.85 $ 9,756.83 28.46 $ 27.44 $ 26.43 S 347.86 $ 348.88 $ 9,408.96 9,060.09 59 $ 60 $ 376.32 $ 376.32 $ 376.32$ 376.32 S 349.89 $ 350.92 $ 351.94 S 8,710.19 61 $ 25.40 S 24.38 $ 23.35 $ 22.33 S 21.29 $ 20.26 $ 19.22 $ 18.18 $ 8,359.28 62 S 8,007.34 352.97 $ 353.99 $ 63 S 376.32 $ 376.32 $ 376.32 $ 7,654.37 64 $ 7,300.38 65 S 355.03 S 6,945.35 6,589.29 66 S 67 $ 376.32 $ 376.32 $ 356.06 S 357.10 $ 358.14 $ 359.19 $ 360.24 $ 6,232.19 68 S 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 $ 376.32 S 376.32 $ 376.32 $ 5,874.04 69 S 70 S 17.13 5,514.86 16.08 $ 15.03 $ 5,154.62 71 $ 72 $ 73 $ 361.29 S 4,793.34 13.98 S 362.34 4,431.00 12.92 $ 363.40 $ 364.46 $ 4,067.60 74 $ 75 $ 76 $ 77 S 11.86 S 3,703.14 365.52 $ 366.59 $ 367.65 $ 368.73 S 10.80 $ 9.73 $ 8.67 $ 7.59 $ 3,337.62 376.32 $ 376.32 $ 376.32 S 2,971.04 2,603.38 78 $ 2,234.66 79 $ 80 $ 81 $ 6.52 S 376.32 $ 376.32 $ 376.32 $ 376.32 $ 369.80 $ 1,864.86 5.44 $ 4.36 S 3.27 $ 2.18 $ 370.88 $ 371.96 $ 373.05 S 374.14 S 375.23 $ 1,493.97 1,122.01 82 S 748.96 83 $ 84 $ 376.32 374.83 1.09 $ 376.32 S $ 3,610.48 $ 31,610.88 28,000.40

Answer-3 Statement showing Cumulative payments of Interest & Principal each year Principal Amount Total Annual installments Year Interest on 1$ 2S 3 S 4 S 5 $ 6 $ 922.72 $ 3,593.12 $ 4,515.84 794.93 $ 662.59 $ 525.54 $ 383.62 $ 236.64 $ 4,515.84 3,720.91 3,853.25 $ 3,990.30 $ 4,132.22 $ 4,279.20 $ 4,431.39 $ 28,000.39 $ 4,515.84 4,515.84 4,515.84 4,515.84 7$ $ 84.45 S 4,515.84 Total 3,610.49 $ 31,610.88 Answer-4 Cash Flow table for coming 7 years Loan installments |(d) 3,714.28 $ Annual revenue Operating Costs Interest on laon Income tax @ 25 % Net cash flow (a) 1$ (b) payable (e) g(a-b-d-f) 690.75 2,193.41 Depreciation (c) f=(a-b-c-e) 25% Year 15,000.00 $ 15,000.00 $ 3$ 7,600.00 $ 5,600.00 $ 5,600.00 $ 5,600.00 $ 5,600.00 $ 4,515.84 $ 4,515.84 $ 4,515.84 $ 4,515.84 $ 4,515.84 $ 4,515.84 $ 4,515.84 $ 922.72 $ 2 $ 794.93 $ 1,222.70 3,661.46 1,255.78 $3,628.38 1,290.05 $ 1,325.53 3,558.64 1,362.27 $ 3,521.89 1,400.32 3,483.84 3,714.28 $ 3,714.28 $ 3,714.28 $ 3,714.28 $ 3,714.28 $ 3,714.28 $ 15,000.00 $ 15,000.00 $ 5$ 662.59 $ 525.54 $ 383.62 S 236.64 S 84.45 S 4 S 3,594.12 15,000.00 $ 15,000.00 $ 7$ 6 S 5,600.00 15,000.00 $ 5,600.00 $