Question

Suppose that you wish to purchase a car and that your bank is offering to you a loan. You wish to explore the nature of this loan and the payments that you would have to make given certain circumstances such as the amount that you borrow. Fortunately, Excel offers a function (PMT) that calculates the payment for a loan based on constant payments and a constant interest rate. The syntax of the function is: PMT(rate, nper, pv) where rate is the interest rate for the loan, nper is the total number of payments for the loan, and pv is the amount of the loan. Create a spreadsheet where you may enter the three parameters and have the payment be calculated for you. Once you have done this, create a single variable table that will list for you the different payments given different amounts of money borrowed (assume you are considering buying a car in the $10,000 to $20,000 range looking at $1000 increments). Also on this tab solve the following double variable problem: Create a double variable table that has amount borrowed as the column variable and number of years as the row variable and payment as the output variable. Also on this tab solve the following double variable problem: Create a double variable table that has interest rate as the column variable and number of years as the row variable and Payment as the output variable. (NOTE: Rather than use number of years, if you are making monthly payments, the value would be number of months (i.e., three years would be 36). Finally, create three scenes (high, medium, low) using Scenario for the problem stated above.

How to do this in excel?

Answer 1