On the next row, you will enter the corresponding information you just recorded from the Website.
Next you will compute the down payment for the car, this is the amount you pay upfront for the car. The balance remaining (price minus down payment) after the down payment is the amount you will have to borrow from the bank. You will assume the down payment is 20% of the MSRP. In the third row of the spread sheet, you will put the labels Down Payment and Borrowed Amount. In the cell corresponding to Down Payment (immediately bellow the cell with the label Down Payment) , you will compute the down payment for the car you choose. To do this, you will use an equation in excel that take the number in the Price cell and multiplies it by 20% (this must be done with an equation). Then, in the cell immediately below the label Borrowed Amount, you will use another Excel equation to compute the amount left to borrow after the down payment. NOTE: If I were to change the price of the car in the corresponding cell, the Down Payment and Borrowed Amount should automatically update. If your spreadsheet cannot do this, then you will receive a failing grade for this assignment.
Now, we will compute the monthly payment for our car loan. On the next line, enter the following labels: Principle, APR, Term, and Monthly Payment (again, four separate cells). In the cell immediately below Principle, you will create a cell reference (an excel formula) that copies the value from Borrowed Amount. The easiest way to do this is to go to the cell where the value for Principle will be entered, then type in the equals sign (as always, this tells excel that we are entering an equation) and then click on the cell that contains Borrowed Amount. Alternatively, after typing in the equals sign you can then type in the cell reference.
In the cell for APR, you will type in 3.4%, and in cell for Term we will type in 5 Years.
For the Monthly Payment cell, we will finally use the PMT function. To start using the PMT function, we will type in an equals sign and then type in PMT (. In the first entry for the PMT function is the periodic interest rate. Since we will be making monthly payments, and the APR is annual interest rate, we need to take the given APR and divide it by 12. Again, this must be done by referencing the cell that contains the APR value (you cannot just type in 3.4% and divide it by 12). Then enter a comma and the next entry will be the number of payments. Since we are making monthly payments, and our term is given in years, we will have a total of 12*5 = 60 periods (you do not need a reference for the number of periods, you may just type in 60). The final entry is the present value, which is the Borrowed Amount. Again, start by putting a comma after the entry for number of payments. To make the monthly payment come out as a positive number, make sure you put a minus sign in front of the cell reference for the Borrowed Amount. Then, finish the PMT function by putting a closing parenthesis ")" and pressing enter.
We will now create a payment schedule. On the next row, we will put the label, Payment Schedule.
Then, on the next row after that we will add the labels Payment Number, Balance, Monthly Interest, Monthly Principle, and New Balance. Since the term of the loan is 5 years, we will need to make 60 payments. So, in the cells below Payment Number, enter the numbers 1 through 60 (so it should be 60 different rows). The easiest way to do this is to put 1 and 2 in the first two rows after Payment Number, highlight those two cells using your mouse, then clicking on the green square in the lower right corner of the highlighted cells and dragging them down until you get have 60 cells highlighted.
In the cell labeled Balance, put a cell reference to the Principle Cell.
In the Monthly Interest cell, we will compute the interest accrued in the current month. To do this, create an excel formula that take the value in Balance and multiplies it by value in the APR cell divide by 12. Make sure these are all cell references, and since the APR is fixed from month to month, we should put in the appropriate dollar signs to prevent that cell reference from changing.
In the Monthly Principle cell, we compute the amount of the monthly payment that is being applied to the remaining balance. To do this, we make a reference to the Monthly Payment cell (again, this will not be changing so will need to use dollar signs to keep it from changing) and then subtract the Monthly Interest (again, this should be done by cell references).
Lastly, we can then compute the New Balance by taking the Balance and subtracting the Monthly Principle (again, all of this should be done via cell references in Excel). This completes the first row of the payment schedule.
In second entry under balance, you will make a cell reference to New Balance from the previous row (again, start by entering an equal sign, then click on the cell containing New Balance from the previous row).
Now, highlight the entries for in the first row of the payment schedule that contain Monthly Interest, Monthly Principle, and New Balance, and then drag it down to the next row (only) by using the green square in the lower right-hand corner of the highlighted cells. This completes row 2.
We will now complete all of the other rows at the same time. Highlight the cells for Balance, Monthly Interest, Monthly Principle, and New Balance in the second row of the payment schedule.
Now, drag all of these entries down until you reach the last payment (number 60) by again clicking on the green square in the lower right-hand corner of the highlighted cells.
If you have done everything correctly, the entry you should have in the 60th row of the payment schedule for New Balance should be zero (or very close to zero depending on round-off error).
Lastly, compute the total amount of interest you paid on your car loan by summing all of the Monthly interest changes in the cell row after the 60th row of the payment schedule, and be sure to label the cell to make it easy to find.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
