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:

A loan amount that is a positive number

An interest rate that is a percentage between 1% and 20% (inclusive)

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.