Question

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.

Answer 1

1. For Loan Amount, to check if value inputted is positive:
   • Use IF function in Excel as: IF(B1<0,"Please enter value greater than zero for Loan Amount",IF(B1=0,"Please enter value greater than zero for Loan Amount","You entered required loan amount"))
   • If amount is negative or zero, it will ask to input positive value for loan amount in D1.
2. Similarly for interest rate, duration as below:
   • For interest rate: IF condition is
     • IF(B2<0%,"Please enter interest rate value between 1% and 20% inclusive",IF(B2>31%,"Please enter interest rate between 1% and 20% inclusive",IF(B2=0%, "Please enter interest rate between 1% and 20% inclusive", "You entered expected rate of interest on your loan")))
     • If interest rate entered is negative, or zero or greater than 20% then it will prompt to enter values between 1% & 20% inclusive
   • For Duration: IF condition is
     • IF(B3<0,"Please enter values between 1 and 30 inclusive",IF(B3>31,"Please enter values between 1 and 30 inclusive",IF(B3=0,"Please enter values between 1 and 30 inclusive","You entered the loan term in years")))
     • If number other than the one between 1 & 30 is entered, it will ask to input porperly
3. For EMI, PMT function is used with rate divided by 12, nper multiplied by 12, payment made at beginning of the year (type=1). Used ABS function to return positive EMI
4. For Total Amount Paid, used Conditional Formatting, with New Rule set to
   • If total amount paid is greater than (1.5 * Loan Amount), then color background with blue and text font as Italic. (My Excel version is 2010, so could not edit font color to red, facility disabled)
5. Attached is the Excel sheet screenshot below with scenario