Question

Excel is allowed!

For this lab, we will create a spreadsheet that allows somebody to type in a loan amount, interest rate, and length of the loan in years. The spreadsheet will then calculate the monthly payment required and the actual amount paid on the loan. First, setup your spreadsheet: • In Cell A1, put the label Loan Amount:. The corresponding value would be input in Cell B1. • In Cell A2, put the label Interest Rate:. The corresponding value would be input in Cell B2. • In Cell A3, put the label Duration (in years):. The corresponding value would be input in Cell B3. • In Cell A5, put the label Monthly Payment:. The corresponding calculation will be made in Cell B5. Use the PMT function to calculate this. The PMT function takes 3-5 arguments; however, we will only need three. The first argument is the Interest rate. Note that, given the interest rate is annual, you will need to divide by 12 for the monthly rate. The second argument is the number of payments made, so you must convert years input to months. The third argument is the amount of the loan. Note that PMT will return a negative number, so you should either take the absolute value of the result of the PMT function using the ABS function, or multiply the result of the PMT function by -1. • In Cell A6, put the label Total Amount Paid:. The corresponding calculation will be made in Cell B6. You should be able to calculate this using the input Duration and calculated Monthly Payment. After you are finished, you should ensure the following: • That users of the spreadsheet can only edit cells B1, B2, and B3. Do not put a password on the worksheet/workbook! • That users can only enter the following types of values: o A loan amount that is a positive number o An interest rate that is a percentage between 1% and 20% (inclusive) o A duration that must be a positive, whole number between 1 and 30. If the total amount paid is equal to 150% or more of the amount of the loan, highlight the text in the total amount paid in red.

Answer 1

Note: unable to attach an excel to this answer, so sharing screenshots of how the requirement can be completed.

1. Formula

1 Loan Amount 2 Interest Rate 3 Duration (in years) 10000 0.15 5 Monthly Payment 6 Total Amount Paid =ROUND(ABS(PMT(B2/12,B3*12,B1)),2) =ROUND(B5*12*B3,2)

B5 Formula = ROUND(ABS(PMT(B2/12,B3*12,B1)),2)

B6 Formula = ROUND(B5*12*B3,2)

A 1 Loan Amount 2 Interest Rate 3 Duration in years) 10,000.00 15% 10 5 Monthly Payment 6 Total Amount Paid 161.33 19,359.60

2. Protect sheet

Step 1: Select green cells (B1, B2, B3). Go to "Format Cells" by right clicking and choosing "Format Cells" or by using the short-cut Ctrl+1.

Step 2: Deselect the "Locked" option. Press OK.

S Format Cells 10,000.00 i Loan Amount 2 Interest Rate 3 Duration (in years) Number Alignment Font Border Patterns Protection Locked Hidden Locking cells or hiding formulas has no effect until you protect the worksheet(Review tab, Protect Sheet button). 5 Monthly Payment 6 Total Amount Paid 161.33 19,359.60

Step 3: Go to "Review" tab in the Excel Ribbon and select "Protect Sheet".

Home Insert Page Layout Formulas Data Review View Cloud Sign in Show/Hide Comment Reset Comment HA Convert to Convert to Simplified Chinese Traditional Chinese Translation Edit Delete Comment Comment Comment Previous Next Show All Comments Reset All Comments Lock Cell Protect / Protect Sheet Workbook A Q W W PS Cloud x PMT Formula.xlsx* X +

Step 4: No need to give a password. Press OK with below checks.

IS Protect Sheet Password(optional): Allow all users of this worksheet to: Select locked cells Select unlocked cells Format cells Format columns Format rows Insert columns Insert rows Insert hyperlinks Delete columns Delete rows Ток | Canceled

4. Validating inputs.

• Loan Amount - Positive Number. Select the cell B1 and click on "Data Validation" or "Validation" in the "Data" tab in the Excel Ribbon

sert Page Layout Formulas Data Review View Cloud - Show All ZI A1 AL B E Remove Duplicates 宣7 ) Form Sort Highlight Duplicates Reapply Validation InsertDropDownList Text to Columns E Reject Duplicate Entries Consolidate - W WPS Cloud x PMT Formula.xlsx* X +

• Choose below options and Press OK.

S Data Validation Settings Input Message Error Alert Validation criteria Allow: Whole number Data: greater than or equal to Ignore blank Minimum: Apply these changes to all other cells with the same settings Clear All OK Canceled

• Interest Rate - Between 1% to 20%. Similarly choose below options and Press OK.

S Data Validation Error Alert Settings Input Message Validation criteria Allow: Decimal Ignore blank Data: between Minimum: 0.01 Maximum: 0.2 Apply these changes to all other cells with the same settings Clear All OK Canceled

• Time Period - Whole Number between 1 to 30 years. Choose below options and Press OK.

S Data Validation Error Alert Settings Input Message Validation criteria Allow: Whole number - Ignore blank Data: between Minimum: Maximum: 30 Apply these changes to all other cells with the same settings Clear All OK Canceled

5. Total Amount paid is more than 150% of loan amount - RED.

• Select cell B6 and Click the small down arrow "Conditional Formatting" in the Home Tab in the Excel Ribbon

Home Insert View Cloud Sign in ? Review À Calibri Custom Page Layout Formulas Data - 11 - A U - E A - W W PS Cloud x Painter I Merge and Wrap Center Text Conditional Formatting B R Agn PMT Formula.xlsx* x +

• Choose highlight cell rules that are Greater than per screenshot below

Conditional Formatting Format as Table Auto Greater Than... La Highlight Cells Rules Less Than... 41. Top/Bottom Rules 3 Between... Data Bars Equal To... Color Scales Lab Text that contains... Icon Sets E A Date Occurring... New Rule... B Duplicate Values... F Clear Rules More Rules... 18 Manage Rules...

• Write the Formula as $B$1 x 1.5 per screenshot below and Press OK

S Greater Than Format cells that are GREATER THAN: = $B$1*1.51 with Light Red Fill with Dark Red Text- OK Canceled