Scenario You have recently been hired at a local bank as a teller. As you visit
Scenario You have recently been hired at a local bank as a teller. As you visit with customers about loans the bank offers, you notice they ask you questions about what their payment will be or how much total interest they will have to pay on the loan. You decide to create a spreadsheet to help you and the other members of the bank. Using some customer data, you will build a Payment Schedule that changes based on the customer name selected in a drop-down list. The interest rate will depend on the amount of their loan and its duration (the logic is explained below). The customer data will supply the loan amount, number of years, and other details. Your spreadsheet should then do the rest, including calculating the scheduled payment amount, the number of payments, and total interest. Each period of the loan should also be shown and should display the amounts for each period (payment number, payment date, beginning balance, scheduled payment, principal, interest, principal balance, and cumulative interest). Requirements You plan to share this spreadsheet with other tellers at the bank so it needs to look professional and should be easy for someone else to use! Please format cells with money using the currency format. Ensure all columns are wide enough for the data contained in them. Customer Data The bank's computer exported data for 100 customers, but it is not nicely split out into cells. On the Customer Data sheet, use the Text-to-Columns feature so that each of the 10 data fields has its own column. Next, use some text functions to split the Full Name column into two new columns: First Name and Last Name. Do not remove the Full Name column; this means that there will be 12 columns in total. Make sure each column has a heading. On the Customer Data sheet, use logic functions to calculate the rate that will be offered to each borrower in a new column called Effective Interest Rate. The bank offers different interest rates based on how much money is being borrowed and how many years the loan will last. The starting rate is 4.25% and then borrowers get the following adjustments: In cell A2, type "Starting interest rate"; in B2, enter 4.25%—you must reference this cell in your adjustment calculations If the loan amount is over $400,000 subtract 1.00 percentage point (meaning that the Effective Interest Rate would be 3.25%) If the loan amount is equal to or under $400,000 and over $175,000 and 20 or fewer years subtract 0.50 percentage points If the loan amount is equal to or under $100,000 add 0.25 percentage points Payment Schedule Starting at the top of the template provided, use data validation to allow the user to select a Full Name from a drop-down list. The ID Number, loan amount, annual interest rate, and loan period in years should be sourced from the Customer Data sheet using look-up functions. All loans will have 12 payments per year. The start date is whatever today's date is and you should use a formula so that as the day changes the start date changes automatically. Create a Payment Schedule that will calculate each period of the loan, up to a 30 year loan, by displaying the amounts for each pay period which includes: Payment Number Payment Date Beginning Balance Scheduled Payment Interest Principal Balance Cumulative Interest

Leave a Reply

Your email address will not be published.

Click=Order Your Paper Now !