Calculating Interest Rates, Down Payments, and Monthly Payments

As a loan officer at First Bank of Missouri, you track house loans. You started a spreadsheet that contains client names, the selling price of houses, and the term of the loans. You are ready to calculate the interest rate, which is based on the term. In addition, you need to calculate the required down-payment, the amount to be financed, and the monthly payment for each customer. To keep your formulas easy to read, you will create and use range names. Finally, you need to calculate some basic statistics.

a. Open the e02m2bank workbook and save it as e02m2bank_Robin Squries
b. Enter a function to display the current date in cell G3.

c. Assign appropriate range names to the number of payments per year value and to the lookup table.

d. Use range names when possible in formulas, and avoid creating circular references.

e. Use an appropriate function to display the interest rate for the first customer.

f. Use an appropriate lookup function to calculate the amount of the down payment for the first customer. The down payment is based on the term and the selling price. The first customer's amount is $68,975.

g. Calculate the amount to be financed for the first customer.

h. Calculate the monthly payment for the first customer using range names and cell references. The first customer's monthly payment is $1,142.65

i. Copy the formulas down their respective columns. Format interest rates with Percent Style with two decimal places. Format monetary values with Accounting Number Format with two decimal places.

j. Calculate the number of loans and other summary statistics. Format the statistics as needed.

k. Create a section, complete with column headings, for the range names. Place this area below the lookup table.

l. Insert a footer with your name (John Smith) on the left side, the sheet name code in the center, and the file name code on the right side.

m. Save and close the workbook.