the instructions are in the first three screen shots and the excel files follow that 
12 2.0 Insert a formula in E3 to calculate the number of payment periods. 2.0 Insert a formula in E4 to calculate the monthly interest rate. 13 In cell E5, use the PMT function to calculate the monthly payment. Make sure the result 14 6.0 displays as a positive number In cell E6, use the CUMIPMT function to calculate the cumulative total interest paid throughout the loan. Make sure the result displays as a positive number. Assume 15 16 6.0 payment is made at the end of the month. 1.0 In cell C11, enter a formula to reference the date stored in cell B7 In cell C12, use the EOMONTH function to calculate the date for the next payment. Copy the 17 18 5.0 formula to the range C13:C34 1.0 In cell D11, enter a formula to reference the loan amount in cell E2. 6.0 positive value. Copy the formula to the range E12:E34 6.0 is a positive value. Copy the function to the range F12:F34 In cell E11, use the IPMT function to calculate the interest paid. Make sure the result is a In cell F11, use the PPMT function to calculate the principal payment. Make sure the result In cell G11, enter a formula to calculate the ending balance Copy the formula to the 19 20 range G12:G34. Adjust the width of column G, if needed, to display the values. 21 2.0 Hint The ending balance is the principal payment deducted from the beginning balance In cell E11, use the IPMT function to calculate the interest paid. Make sure the result is a In cell F11, use the PPMT function to calculate the principal payment. Make sure the result In cell G11, enter a formula to calculate the ending balance. Copy the formula to the 6.0 positive value. Copy the formula to the range E12 E34 19 20 6.0 is a positive value. Copy the function to the range F12:F34. range G12:G34. Adjust the width of column G, if needed, to display the values 21 2.0 Hint: The ending balance is the principal payment deducted from the beginning balance. Insert a formula in cell D12 that references the ending balance for the previous payment (G11). 1.0 Copy the formula in D12 to the range D13:D34. 4.0 Select the range D11:G34 and apply the Accounting Number Format with two decimal places. 23 In cell 14 use the PV function to calculate the present value of the total monthly rent you will collect for the 8 apartments for 30 years. Use the number of periods and monthly rate in the Summary Calculations section. The payment amount in your formula is the monthly rent 24 6.0 for all 8 apartments. The result should be a positive value. Save and close the workbook, and submit the file as directed in Blackboard. 0.0 10.0 G4 || pv of future building rent Summary Calculations Loan Amount No. Periods Monthly Rate Monthly Payment Total Interest Paid What If Monthly rent per unit (apartment) # of units PV of future building rent 1 Input Area 2 $2,000 Building Cost $1,850,000.00 3 Down Payment$ 750,000.00 41# of Pmts per Year 5 Years 6 APR 7 1st Payment Date 3/31/2018 12 30 4.75% PrincipalEnding Payment # Date Beginning Balance Interest Paid Payment Balance Payment 10 16 10. instructions SummaryDatabaseLoan 19 21 17 Principal Ending Date Beginnine Balance Interest Paid Pavment Balance instructions Summary Database Loan
