Draw an influence chart for the given situation

Reference no: EM131849582

Applied Management Science Assignment: Loan Calculator

An important and commonbusiness 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 aparticular annual interest rate, such as 6%.

Typically, we pay back a loanby 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) yieldsapayment 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, withTIP as the main output.

2. Create a spreadsheet modelin 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 tablewhere 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. Whatkind 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?

Attachment:- Data.rar

Reference no: EM131849582

Questions Cloud

Is capitalism helpful to the development of democratic : Is capitalism helpful to the development of democratic virtues? Based on the writings from Hirschman and Foucault

Propose a team approach to achieve a long-term business goal : Consider your current work environment or one that you desire to join. Through a PowerPoint® presentation, propose a team approach to achieve a long-term.

Why does the us have only two major political parties : Why does the US have only two major political parties? Make sure and describe Duverger's law and strategic behavior on the part of voters, politicians.

Understand human-environment relationships : One of the goals of this class is to better understand human-environment relationships in the context of global environmental change events

Draw an influence chart for the given situation : Draw an influence chart for this situation, withTIP as the main output. Assume a purchase price of $21,000 and an annual interest rate of 6%.

What is the breakeven point in number of customers per month : What is the breakeven point in number of customers per month for the sports bar/restaurant?

Why do we have parties : Why do we have parties? What roles do they play in a representative democracy? How can these roles be beneficial? How can they be harmful?

Explain the job satisfaction and organizational commitment : Apply motivational theory and performance management principles to evaluate the company as a potential employer.

What is the ultimate source of wealth-economic productivity : Who, very specifically, benefitted from the colonial system? What work did they have to do to keep it in place? Were there any people who helped to maintain

User Account

All Pages