Question

In: Finance

Create an Excel spreadsheet to enable users to calculate can compare 3 car loans. The objective...

Create an Excel spreadsheet to enable users to calculate can compare 3 car loans. The objective is to see the Total Amount Paid for a given loan amount, with different interest rates and a number of finance months. Allow the user to enter car price and down payment amount in cells that will be used by all 3 loan options. Add charts to compare the three loans visually. Make sure to compare monthly, months, as well as the total cost of the call after all payment, is done.

Solutions

Expert Solution

The 3 car loans that I have considered below are:

Loan 1: Loan amount is $100,000 with 0 down payment and payment to be made in 24 months with interest rate of 12%.

Here monthly payment will be = PMT(12%/12, 24, 90000) = $4,707.35 per month

Loan 2: Loan amount is $100,000 with 0 down payment and payment to be made in 12 months with interest rate of 12%

Here monthly payment will be = PMT(12%/12, 12, 100000) = $8,884.88 per month

Loan 3: Loan amount is $100,000 with 0 down payment and payment is made in 18 months with interest rate of 18%

Here monthly payment will be = PMT(18%/12, 18, 100000) = $6,380.58 per month

The excel for all the 3 options are provided below:

Loan option 1 Loan option 2 Loan option 3
Month Loan amount due at the start of the month Monthly payments Interest Principal paid Loan amount due at the end of the month Month Loan amount due at the start of the month Monthly payments Interest Principal paid Loan amount due at the end of the month Month Loan amount due at the start of the month Monthly payments Interest Principal paid Loan amount due at the end of the month
1 100,000.00 4,707.35 1,000.00 3,707.35 96,292.65 1 100,000.00 8,884.88 1,000.00 7,884.88 92,115.12 1 100,000.00 6,380.58 1,500.00 4,880.58 95,119.42
2 96,292.65 4,707.35 962.93 3,744.42 92,548.23 2 92,115.12 8,884.88 921.15 7,963.73 84,151.39 2 95,119.42 6,380.58 1,426.79 4,953.79 90,165.63
3 92,548.23 4,707.35 925.48 3,781.86 88,766.37 3 84,151.39 8,884.88 841.51 8,043.36 76,108.03 3 90,165.63 6,380.58 1,352.48 5,028.09 85,137.54
4 88,766.37 4,707.35 887.66 3,819.68 84,946.68 4 76,108.03 8,884.88 761.08 8,123.80 67,984.23 4 85,137.54 6,380.58 1,277.06 5,103.52 80,034.03
5 84,946.68 4,707.35 849.47 3,857.88 81,088.80 5 67,984.23 8,884.88 679.84 8,205.04 59,779.19 5 80,034.03 6,380.58 1,200.51 5,180.07 74,853.96
6 81,088.80 4,707.35 810.89 3,896.46 77,192.34 6 59,779.19 8,884.88 597.79 8,287.09 51,492.11 6 74,853.96 6,380.58 1,122.81 5,257.77 69,596.19
7 77,192.34 4,707.35 771.92 3,935.42 73,256.92 7 51,492.11 8,884.88 514.92 8,369.96 43,122.15 7 69,596.19 6,380.58 1,043.94 5,336.64 64,259.55
8 73,256.92 4,707.35 732.57 3,974.78 69,282.14 8 43,122.15 8,884.88 431.22 8,453.66 34,668.49 8 64,259.55 6,380.58 963.89 5,416.68 58,842.87
9 69,282.14 4,707.35 692.82 4,014.53 65,267.62 9 34,668.49 8,884.88 346.68 8,538.19 26,130.30 9 58,842.87 6,380.58 882.64 5,497.94 53,344.93
10 65,267.62 4,707.35 652.68 4,054.67 61,212.95 10 26,130.30 8,884.88 261.30 8,623.58 17,506.72 10 53,344.93 6,380.58 800.17 5,580.40 47,764.53
11 61,212.95 4,707.35 612.13 4,095.22 57,117.73 11 17,506.72 8,884.88 175.07 8,709.81 8,796.91 11 47,764.53 6,380.58 716.47 5,664.11 42,100.42
12 57,117.73 4,707.35 571.18 4,136.17 52,981.56 12 8,796.91 8,884.88 87.97 8,796.91 0.00 12 42,100.42 6,380.58 631.51 5,749.07 36,351.35
13 52,981.56 4,707.35 529.82 4,177.53 48,804.03 13 36,351.35 6,380.58 545.27 5,835.31 30,516.04
14 48,804.03 4,707.35 488.04 4,219.31 44,584.72 14 30,516.04 6,380.58 457.74 5,922.84 24,593.20
15 44,584.72 4,707.35 445.85 4,261.50 40,323.22 15 24,593.20 6,380.58 368.90 6,011.68 18,581.52
16 40,323.22 4,707.35 403.23 4,304.12 36,019.10 16 18,581.52 6,380.58 278.72 6,101.86 12,479.67
17 36,019.10 4,707.35 360.19 4,347.16 31,671.95 17 12,479.67 6,380.58 187.20 6,193.38 6,286.28
18 31,671.95 4,707.35 316.72 4,390.63 27,281.32 18 6,286.28 6,380.58 94.29 6,286.28 0.00
19 27,281.32 4,707.35 272.81 4,434.53 22,846.79
20 22,846.79 4,707.35 228.47 4,478.88 18,367.91
21 18,367.91 4,707.35 183.68 4,523.67 13,844.24
22 13,844.24 4,707.35 138.44 4,568.90 9,275.33
23 9,275.33 4,707.35 92.75 4,614.59 4,660.74
24 4,660.74 4,707.35 46.61 4,660.74 0.00
Total 112,976.33 12,976.33 106,618.55 6,618.55 114,850.41 14,850.41
Total payments Total interest paid
Loan 1 112,976.33 12,976.33
Loan 2 106,618.55 6,618.55
Loan 3 114,850.41 14,850.41
Interest as a % of total payments
Loan 1 11.49
Loan 2 6.21
Loan 3 12.93

Chart:

jeff jeffy answered 1 year ago
Next > < Previous

Related Solutions

Create an Excel spreadsheet to enable users to calculate can compare 3 car loans. The objective...
Create an Excel spreadsheet to enable users to calculate can compare 3 car loans. The objective is to see the Total Amount Paid for given loan amount, with different interest rates and number of finance months.
How do you make an excel spreadsheet that will enable users to calculate and compare 3...
How do you make an excel spreadsheet that will enable users to calculate and compare 3 car loans? The table should include the total amount paid for given loan amount with different interest rates and number of finance months
Create a budget spreadsheet for a hypothetical organization in excel
Create a budget spreadsheet for a hypothetical organization in excel
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create a new data set that is a subset of this larger provided data set. The new data set should include the dependent variable (VALUE) and the following independent variables (AGE, LOTSIZE, RMS, MOD KITCH, MOD BATH, AIRCON and FIREPL). b. For this new data set, use the Correlation option of Data Analysis in Excel and produce the correlation matrix associated with the dependent variable...
Create an Excel spreadsheet that can do the calculations for the credit card below. Using formulas...
Create an Excel spreadsheet that can do the calculations for the credit card below. Using formulas and the drag function of Excel (see the notes), find the values for 1 year of charges. You can start this by taking the finance charge in month 1. With a beginning balance of $2500 and no additional charges, there is no grace-period, you will just pay the minimum payment each month for the next year. The APR is 24.99% and the minimum payment...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A firm that is in the 35% tax bracket forecasts that it can retain $4 million of new earnings plans to raise new capital in the following proportions: 60% from 30-year bonds with a flotation cost of 4% of face value. Their current bonds are selling at a price of 91...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company is evaluating the purchase of a machine to improve product quality and output levels. The new machine would cost $1.6 million and would be depreciated for tax purposes using the straight-line method over an estimated six-year life to its expected salvage value of $100,000. The new machine would require...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have interest expense of $2,000,000 and 1,000,000 common shares outstanding, whereas the second would have interest expense of $3,800,000 but only 750,000 shares outstanding. The company is in the 35% tax bracket. Part 1: Construct the...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A firm had the following abbreviated income statement for 2020 and abbreviated balance sheets at the end of 2020 and 2021. 2020 Sales $600,000 Less Cost of goods sold 320,000 Gross Profit 280,000 Less Operating expenses 190,000 Less Depreciation 30,000 Operating Income (EBIT) 60,000 Less Interest expense 20,000 Earnings Before Taxes...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have $25 million of bonds at 8% interest and 1,000,000 common shares outstanding, whereas the second would have $47.5 million of bonds at 8% interest and only 750,000 shares outstanding. The company is in the 35%...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT