Excel MidTerm Fall 2023 – ONCII Excel MidTerm Fall 2023 – ONCII @Mr.AlbertLab

Contact Us:
WhatsApp no. : 923006085085
Email : [email protected]
WhatsApp Direct Link for Chat: https://wa.me/message/Q3UK23XALVRWI1

#Excel MidTerm Fall 2023 – ONCII
#Excel MidTerm Fall 2023 – ONCII
#Excel MidTerm Fall 2023 – ONCII
#Excel MidTerm Fall 2023 – ONCII
#Excel MidTerm Fall 2023 – ONCII
#ExcelMidTermFall 2023
#ExcelMidTermFall 2023
#ExcelMidTermFall 2023
#ExcelMidTermFall 2023

Steps to Perform:
Step Instructions Points Possible
1 Download the file named StarterMidTermFall2023 and change the name to your name [First Name_Last Name] 0
2 In the Loan sheet, In Cell C10, insert a function to calculate the downpayment by multiplying the cost of home in Cell B10 by the downpayment % in Cell B3.
Copy the formula down into cells C11 to C13.
You must use absolute referencing. 3
3 In the Loan sheet, In Cell D10, calculate the Loan (Amount Financed) by subtracting the downpayment in Cell C10 from the cost of home in Cell B10.

Copy the formula down into cells D11 to D13. 3
4 In the Loan sheet, In Cell F10, calculate the monthly payment (PMT) based on the following inputs:
1) To calculate the monthly Rate, use APR in Cell B4 and No. Of Payments Per Year in B5
2) To calculate the total number of payments (Nper) use the No. of years in Cell E10 and No. Of Payments Per Year in B5
3) The preset value of the loan (Pv) is the Loan (Amount Financed) in D10
Copy the formula down into cells F11 to F13.

You must use absolute referencing.

#ExcelMidTermFall
#ExcelMidTermFall
#ExcelMidTermFall
#ExcelMidTermFall
# MidTerm Fall 2023 – ONCII
# MidTerm Fall 2023 – ONCII
# MidTerm Fall 2023 – ONCII
# MidTerm Fall 2023 – ONCII
# MidTerm Fall 2023 – ONCII
# MidTerm Fall 2023
Excel MidTerm Fall 2023 – ONCII
Hint:
Use a negative sign with the present value of the loan (Pv). 6
5 In the Loan sheet, In Cell B2, insert a formula to return today’s date.
In Cell H10, insert a formula to calculate the fraction number of years based on the start date in Cell G10 and the end date in Cell B2.
Copy the formula down into cells H11 to H13
Hint: Check referencing method 3
6 In the Loan sheet, In Cell I10, use exact match Vlookup function to search the name in Cell A10 in the range from Cell A10 to Cell H13 and retrieve the monthly payment in the 6th column.
Multiply the formula by E10 and B5.
Copy the formula down into cells I11 to I13

Hint:
For the exact match option, you must make the Range_Lookup value equal to False.
Check the referencing method in Cell B5. 6
7 In the Loan sheet, In Cell I10 to I13, use one formula to apply conditional formatting to color the cells in orange color if the No.of years in Cell E10 25 and the No. of years already paid in Cell H10 1.5.
The orange color is option number 6 in the background color options. 3