Assignment 1. Build a loan amortization model.
Please follow ALL of the instructions below carefully as this is a graded assignment. As with all of the other assignments you will turn in for a grade in this course, this is not a group project - you mustcomplete ALL of the requirements below on your own without help or assistance from others and without giving help or assistance. You are also responsible for safeguarding your work so that it may not be used by others.
Start by opening a new workbook on your computer (File / New / Blank Workbook). As we did in class, create the Getformula function and a{Ctrl}{Shift}Wmacro in this workbook by copying the VBA code from the Getformula Instructions workbook and creating a simple keystroke macro to enter the Getformula function. Save this workbook and a backup copy of it as Macro.xlsm for use in future assignments.Verify that the macro works. You should also backup ALL of the documents for this class on a dedicated flash drive in case your computer’s hard drive fails. Note that this first step in completing the Macro workbook may already have been completed in class.
Withthe Macro.xlsmworkbook openon your computer, copy Sheet1 from the Assignment1Starter workbook to the Macro.xlsmworkbook. Rename this copied sheet “Loan” and delete all of the other sheets in the Macro.xlsm workbook.Next, carefully read and copy the “Certification” sheet from the Assignment1Starter workbook to the Macro.xlsm workbook. (Do not complete the “Certification” sheet yet!) Rename and save the Macro.xlsm workbook using your class number and last name (ex. 04James.xlsm). Close theMacro.xlsmand Assignment1Starter workbooks. For the remainder of this assignment you will work only with the workbook named with your class number and last name.
On the Loan sheet complete the tasks listed below changing format elements as requested. As you work on the “Loan” sheet you will only be able to enter information into cells that already contain text or numbers. (The sheet has been protected so that only cells with numbers or text may be selected.) You may not use information from another sheet to complete the formulas on the “Loan” sheet.
Replace the contents of cell A1 with your class number and name.
Use a format to show decimals, dollar signs, % and commas consistent with the example figures below. Use the format option with parentheses and red for negative valuesin the loan amortization table (see cell F11 on the second example below). Show the dollar sign only for appropriate inputs and top of columns.
Rewrite the Loan Amortization sheet to round the computed payment to the next highest penny.The formula for the last payment should be adjusted to pay off loan to the nearest penny.Do not round any calculations other than those in the payment (Pmt) cells.
Conditional format all dollar values so that pennies are shown only if the amount borrowed is less than one million. Remove all unnecessary conditional formats.
Here are two example outputs with correct formulas and formatting (name and class # omitted):
Set up the inputs to accept only reasonable values and prompt with appropriate messages of your choice. For example, none of the inputs should be negative.Constrain term to a whole number between 2 and 20. Be sure for each of the three inputs to apply appropriate “Settings”, “Input Message” and “Error Alert” using “Data Validation”.
When the workbook is completed the rows beyond the loan term should look blank.You must accomplish this with IF statements and the ROW function (NOT with a conditional format). Remember that two double quotes (“”) is the value for a blank cell. Note that a double quote is not the same as two apostrophes!
Set up the workbook so that only the input cells may be selected and changed by the user. Do NOT use a password to protect the workbook or sheet.
Now, only after completing ALL of the previous steps to the best of your ability, complete the steps required on the “Certification” sheet. Use the drop down list to answer the two questions. Insert a recent head and shoulders picture of yourself and a picture of your legal signature.
Cell B2 on the “Loan” sheet should be active (selected) and the Excel window maximized to show only cells A1.G31.Save the workbook with the same values for the inputs as listed on the Assignment1Starter workbook. This completed workbook should contain only two sheets, “Loan” and “Certification”. If you created the file outside of Windows be sure that it can be read in the Windows version of Excel 2013 (or more recent). Mac users, if unsure of compatibility, can double check to see that the workbook opens correctly on another computer using Windows and a recent version of Excel.
Be sure that you have named the workbook with your class number and last name (ex. 04James.xlsm).Attach and email the completed workbook to both your instructor and TA: and . Add “FIN 4331 Assignment 1” to the email subject field.Do not send a “Read only” or password protected file. Be especially careful to send the completed workbook as an attached file and not just a link to a file on another device or the cloud!It is due8:00AM on the day given in class. Note that late assignments may be accepted with a grade penalty.