23 May HOW DOES THE LOAN LENGTH AFFECT PMT?
use the down payment is $5206, need to write all the inputs in the sheet. use the formulas in the excel. let excel to calculator. use the DS601 style for cell styles. I give 2 examples for that. and also need to answer those questions.
An important and common business transaction is getting a loan from a bank or financial services company. Suppose you want to buy a car with a purchase price of $21,000, but you only have $5,000 available now to make as a down payment. Then you’d need to finance (i.e., get a loan for) the balance ($16,000) at a particular annual interest rate, such as 6%.
Typically, we pay back a loan by making a series of equal-sized payments due at the end of each month. Excel’s PMT function can be used to find the amount of money you must pay every month in order to amortize the loan in some number of periods. Its syntax is: =PMT(Rate,Nper, PV, FV, Type), where
• Rate = the interest rate per period;
• Nper = the total number of payments, or the length of the loan (typically, in months);
• PV = the present value, or loan amount;
• FV = the future value, or cash balance, you want to attain after the last payment is made. If omitted, FV is assumed to be 0, i.e., the future value of a loan is 0.
• Type = 0, if payments are due at the end of each period;
= 1, if payments are due at the beginning of each period.
For example, the cell formula PMT(.06/12, 36, 16000, 0, 0) yields a payment size of $486.75, which must be paid by the end of every month in order to repay the loan in 3 years. The total interest paid(TIP) over the course of a loan is the total amount of all payments minus the amount financed. Here, TIP = 36($486.75) – $16,000 = $1,523. A key decision to make prior to getting a loan is the length of the loan. This assignment explores the relationship between the loan length, payment size, and TIP.
To do:
1. Draw an influence chart for this situation, with TIP as the main output.
2. Create a spreadsheet model in Excel following the format used in class, i.e., have sections for inputs, decision variables, calculated quantities, outputs, and range names.
a. Assume a purchase price of $21,000 and an annual interest rate of 6%.
b. The down payment should equal to the last four digits of your student ID number. For example, if your ID # is 987654321, then the down payment would be $4,321.
3. On the same sheet, make a 1-way data table where the loan length varies from 12 to 60 months in increments of 12 months. Output columns should be monthly payment size and TIP.
4. On the same sheet, make a 2-way data table for TIP where the loan length varies from 12 to 60 months in increments of 12 months and the annual interest rate varies from 4% to 8% in increments of 0.5%. Highlight all cells in the data table with a TIP below $1,500.
Turn in hard copies of:
1. Your influence chart (should fit on 1 page)
2. Your spreadsheet (should fit on 1-2 pages)
3. Brief answers to the following questions (which can be answered on your spreadsheet):
a. What kind of relationship is there between loan length and TIP?
b. How does the loan length affect PMT? Make an XY plot with loan length on the x-axis.
c. How does the interest rate affect TIP?
d. If you can only afford to make monthly payments of $425 or less, and the annual interest rate is 6%, what options do you have regarding the loan length?
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.