Purchase Solution

Amortization for a Car Loan

Not what you're looking for?

Ask Custom Question

In this problem, you will create an amortization schedule for a car loan for 5 years with a fixed APR given.

Steps to Perform:

2 In cell B10, enter the balance at the beginning of month 1. Format cell B10 as Currency with 2 decimal places.

3 In cell B11, enter the balance at the beginning of month 2. Fill cell B11 down the column to cell B69. Format cells B11:B69 as Currency with 2 decimal places.
Note: The values in the range B11:B69 will automatically change after completing the next steps.

4 In cell C10, calculate the amount of monthly payment for month 1. Fill cell C10 down the column to cell C69. Format cells C10:C69 as Currency with 2 decimal places.
Note: The amount of monthly payment is expected as a negative value and it doesn't change during the whole period.

5 In cell D10, calculate the amount of interest payment for month 1. Fill cell D10 down the column to cell D69. Format cells D10:D69 as Currency with 2 decimal places.
Note: The amounts of interest payment are expected as positive values. The values in the range D11:D69 will automatically change after completing the next steps.

6 In cell E10, calculate the amount of principal payment for month 1. Fill cell E10 down the column to cell E69. Format cells E10:E69 as Currency with 2 decimal places.
Note: The amounts of principal payment are expected as negative values. The values in the range E11:E69 will automatically change after completing the next step.

7 In cell F10, calculate the new balance at the end of month 1. Fill cell F10 down the column to cell F69. Format cells F10:F69 as Currency with 2 decimal places.

8 In cell I10, determine the balance at the end of the first year. Format cell I10 as Currency with 2 decimal places.

9 In cell I13, calculate the sum of the interest payments over the life of this loan. Format cell I13 as Currency with 2 decimal places.

10 In cell L10, enter the balance at the beginning of month 1 if an extra principal payment is made at the end of each month. Format cell L10 as Currency with 2 decimal places.

11 In cell L11, enter the balance at the beginning of month 2 if an extra principal payment is made at the end of each month. Fill cell L11 down the column to cell L69. Format cells L11:L69 as Currency with 2 decimal places.
Note: The values in the range L11:L69 will automatically change after completing the next steps.

12 In cell M10, calculate the amount of monthly payment for month 1. Fill cell M10 down the column to cell M69. Format cells M10:M69 as Currency with 2 decimal places.
Note: The amount of monthly payment is expected as a negative value and it doesn't change during the whole period.

13 In cell N10, calculate the amount of interest payment for month 1 if an extra principal payment is made at the end of each month. Fill cell N10 down the column to cell N69. Format cells N10:N69 as Currency with 2 decimal places.
Note: The amounts of interest payment are expected as positive values. The values in the range N11:N69 will automatically change after completing the next steps.

14 In cell O10, calculate the amount of principal payment for month 1 if an extra principal payment is made at the end of each month. Fill cell O10 down the column to cell O69. Format cells O10:O69 as Currency with 2 decimal places.
Note: The amounts of principal payment are expected as negative values. The values in the range O11:O69 will automatically change after completing the next step.

15 In cell P10, calculate the new balance at the end of month 1 if an extra principal payment is made at the end of each month. Fill cell P10 down the column to cell P69. Format cells P10:P69 as Currency with 2 decimal places.

16 In cell I17, determine the month in which the loan will be repaid if an extra principal payment is made at the end of each month.
Note: Type only the numeric value of the corresponding month.

17 In cell I24, determine by how much the loan repayment period will decrease if an extra principal payment is made at the end of each month.

18 In cell I28, calculate the sum of interest payments if an extra principal payment is made at the end of each month. Format cell I28 as Currency with 2 decimal places.

19 In cell I30, determine whether the total interest will also double if you double the APR.

Attachments
Purchase this Solution
Solution Summary

The Excel file contains all of the required computations, formulas and solutions for the car loan amortization example.

Solution provided by:
J Pye, MSc
Education
  • MSc, California State Polytechnic University, Pomona
  • MBA, University of California, Riverside
  • BSc, California State Polytechnic University, Pomona
  • BSc, California State Polytechnic University, Pomona
Recent Feedback
  • "Excellent work. Well explained."
  • "Can you kindly take a look at 647530 and 647531. Thanks"
  • "Thank you so very much. This is very well done and presented. I certainly appreciate your hard work. I am a novice at statistics and it is nice to know there are those out there who really do understand. Thanks again for an excellent posting. SPJ"
  • "GREAT JOB!!!"
  • "Hello, thank you for your answer for my probability question. However, I think you interpreted the second and third question differently than was meant, as the assumption still stands that a person still independently ranks the n options first. The probability I am after is the probability that this independently determined ranking then is equal to one of the p fixed rankings. Similarly for the third question, where the x people choose their ranking independently, and then I want the probability that for x people this is equal to one particular ranking. I was wondering if you could help me with this. "
Purchase this Solution
Free BrainMass Quizzes
Basics of corporate finance

These questions will test you on your knowledge of finance.

Paradigms and Frameworks of Management Research

This quiz evaluates your understanding of the paradigm-based and epistimological frameworks of research. It is intended for advanced students.

Six Sigma for Process Improvement

A high level understanding of Six Sigma and what it is all about. This just gives you a glimpse of Six Sigma which entails more in-depth knowledge of processes and techniques.

Learning Lean

This quiz will help you understand the basic concepts of Lean.

Production and cost theory

Understanding production and cost phenomena will permit firms to make wise decisions concerning output volume.

View More Free Quizzes
Related BrainMass Solutions

  • Car Buying Comparison

    The solution computes monthly payment for buying a new or a used car in different scenarios along with amortization schedule. Attached in Excel.

  • Amortization schedule of a car and house in excel

    549116 Amortization of a car and house The scenario is designed to help you determine and evaluate the payment amount of a car loan and a mortgage, based on the assumption that your household income is $36,000 per year or $3,000 per month.

  • Honda Accord Car: Loan Amortization Schedule

    279595 Honda Accord Car: Loan Amortization Schedule Please prepare a loan amortization for "Honda Accord car" REQUIREMENTS: YOU ARE TO USE EXCEL TO CREATE AN EXCEL FILE TO COMPLETE THE FOLLOWING ASSIGNMENT.

  • Loan Amortization Schedule Comparison Project

    466590 Loan Amortization Schedule Comparison Project Develop a loan amortization project using Excel spreadsheets. This can be a car loan or a home mortgage.

  • Amortization Schedule

    154234 Amortization Schedule There are seven years remaining on a ten year car loan. The interest rate is 10.25%. The monthly payments are $450.00.

  • If you take $8,000 car loan that calls 48 monthly payments at an APR of 10 percent

    174082 Loan Payment If you take $8,000 car loan that calls 48 monthly payments at an APR of 10 percent. 22. Loan Payment If you take $8,000 car loan that calls 48 monthly payments at an APR of 10 percent. What is your monthly payment?

  • Auto loan amortization

    Sally's car loan amortization Total Interest Principal Balance Loan 12,000 Down payment 2,000 2,000 10,000 End of year 1 4,000 1,500 2,500 7,500 End of year 2 3,625 1,125 2,500 5,000 End of year 3 3,250 750

  • Loans and payments

    Loan Payments. If you take out an $8,000 car loan that calls for 48 monthly payments at an APR of 10 percent, what is your monthly payment? What is the effective annual interest rate on the loan? (Using the excel PMT function) 36.

  • Finance : Depreciation and Amortization

    The purpose of this problem is to make an amortization schedule for this 3-year loan. The minimum monthly payment required for this amortization is $304.22.

  • Loan amortization schedule, value of bonds, monthly payment

    354408 Loan amortization schedule, value of bonds, monthly payment amount, accept or reject To expand its operation, International Tools Inc. has applied to the International Bank for a 3-year, $3,500,000 loan.

View More Related Solutions