Question

Hello, I need some help with some Excel formulas.

FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER 8 Cut ES Copy MS Sans Serif - 10 - A A 2 Wrap Text General Paste BIU- $ -% 9 Merge & Center .00 Format Painter Conditional Format as Formatting Table SE Clipboard Font Alignment Number Styles A2 B F G H J 1 Analysis of Consumer Loans 2 Term 4 3 3 Loan ID 9 L0006 10 L0007 12 L0009 14 L0011 16 L0013 23 L0020 24 L0021 27 Branch North Miami North Miami Opa Locka Kendall Kendall Kendall Opa Locka Loan Amount Loan Type $35,000 A $10,000 A $25,000 A $56,000 A $35.000 A $25,000 A $41.000 A Interest Rate 5.70% 4.00% 8.50% 6.00% 7.50% 7.00% 8.40% 3 3 Start Date July 26, 2004 July 28, 2004 August 4, 2004 August 6, 2004 August 14, 2004 September 18, 2004 September 21, 2004 Monthly End Date Payment July 25, 2008 $817.17 July 28, 2007 $295.24 August 4, 2007 $789.19 August 6, 2007 $1,703.63 August 14, 2007 $1,088.72 September 17, 2008 $598.66 September 21, 2007 $1.292.37 3 4 3 Interest Loan Amount Monthly Payment Rate Term Start Date Loan Type A End Date 28 Loan ID Branch 29 30 31 Summary Statistics 32 Number of Loans 33 Average loan amount 34 Total loan amount 35 7 $32,429 $227,000 Largest loan Average interest rate Average term $56,000 6.73% 3.29 Pivot Table Loans : |

a. Open Excel_Ch11HW.xlsx and save it as Excel_Ch11HW_LastNameFirstName.
b. Click cell H4, the cell containing the ending date for the first loan. Enter the formula to compute the ending date, based on the starting date and the term of the loan. For the sake of simplicity, you don’t have to account for leap year. To compute the ending date, multiply the term of the loan by 365 and add that result to the starting date. Be sure to format the starting and ending dates to show a date format and widen columns as necessary.
c. Click cell I4 and enter the PMT function to compute the monthly payment for the first loan. Copy the formulas in cells H4 and I4 to the remaining rows in the worksheet.
d. Name your database range Loan_Database (A3 through I26) and name your criteria range Loan_Criteria (A28 through I29).
e. Enter the indicated criteria in cell D29. Then enter the indicated database functions into cells C32, C33, C34, H32, H33, and H34. Use the Advanced Filter command to filter the list, in-place (use Loan_Database as your list range and Loan_Criteria as your criteria range) to display only those loans that satisfy the indicated criteria as shown in the Figure below. Format the list as closely as possible to the image in the Figure.

f. Look closely at the bottom of the Figure and note the presence of a PivotTable worksheet. Create a PivotTable that has the loan type and branch in the row and column fields, respectively. Your PivotTable is to contain two data fields, the total amount of the loans and the average interest rate. Format the PivotTable report fields as currency with 0 decimals and percentage with 2 decimals. Add Summary Loan Statistics as the title of the report. Rename the Sheet1 tab as PivotTable.
g. Create a custom footer containing your name in the left section, the name of the worksheet in the center section, and today’s date in the right section. Be sure to print gridlines and row and column headings, making sure the Loans worksheet and the PivotTable would each print on one page. Save your workbook and upload it to Canvas.

Answer 1

b) use the below formula to calculate end date

IF(G9,G9+(F9*365),"")

G9-start date

F9-terms

c) PMT(rate,periods,-amount)