Reference no: EM132370477

1. You need to analyze the Monthly payment for a house which you are planning to buy.

Your initial information (knowledge) that the monthly payment depends on:

a. Loan amount

b. Monthly interest rate

c. Number of payments

However, when you go to a loan office they offer you a yearly interest year and let you make a decision on the number of years to pay the loan (load duration) which is 5, 10, 15, or 30 years.

1.A Draw the influence diagram for this problem (model) the problem. 

1.B Design a spreadsheet for this problem with the following values: 

i. Loan Amount $200,000

ii. Interest rate of 8% (which is 0.08)

iii. Load duration 30 years

1.C What is the decision variable? 

1.D What is the monthly payment dollar amount? 

Hint 1:  Look for PMT (Monthly Payment) function under financial functions in Excel formula)

2. Let's assume the following

a. You owe the total amount of loan in the month zero

b. After paying the first month, the loan leftover is:

What is left (the total amount in this case) * (1+Monthly Interest Rate) - Your payment

c. Modify your spreadsheet design such that it shows the first 12 month of your loan amount situation. That is to show the leftover of your loan for each month for the first year (12 months).

Hint 2:  the calculation section of your spreadsheet should show the following for each month:

Month

Monthly pay

Principle (loan)