I'm not sure what two excel functions to use that will give the correct answer and how to copy the information to each cell and the values stay the same.
2. A home loan is to be repaid by a series of equal monthly payments over many years. Prepare a worksheet to calculate the total of the interest paid over the duration of the repayment, using one or more Excel functions. Your results should be in the form of a two-dimensional table, with each yearly interest rate of 3.0%, 3.5%, 4.0%, 4.5%, and 5.0% shown in a separate row, and each loan payoff time of 15, 20, 25, and 30 years shown in a separate column. The total interest payment calculated in each table cell should correspond to the interest rate at the left of the row and the payoff time at the top of the column. The home loan amount of $150,000 should be located in a cell outside of the table that is referenced by the table formulas. The table formulas should make correct use of relative and absolute addresses so that copying any table cell to the entire table gives correct formulas in every cell. NOTE: For an interest rate of 4.0% and a payoff time of 20 years, the total interest payment is $68,152.92 (negative of the value given by the Excel functions), assuming each payment is made at the end of the month.
To calculate the total interest paid over the duration of the repayment for different interest rates and loan payoff times, you can use the PMT function and the CUMIPMT function in Excel.
Here's how you can set up the worksheet to calculate the total interest paid:
1. Create a table with rows representing the different interest rates (3.0%, 3.5%, 4.0%, 4.5%, and 5.0%) and columns representing the different loan payoff times (15, 20, 25, and 30 years). The intersection of each row and column will represent a specific combination of interest rate and payoff time.
2. In the cell outside of the table, enter the home loan amount of $150,000.
3. In each cell of the table, you will enter the formula to calculate the total interest payment for the corresponding interest rate and payoff time. To achieve this, follow these steps:
a. Select the first cell in the table, which will correspond to the 3.0% interest rate and 15-year payoff time.
b. Enter the following formula:
`=-CUMIPMT(interest_rate/12, payoff_time*12, loan_amount)`
Replace "interest_rate" with the appropriate reference to the interest rate cell in the same row, and "payoff_time" with the appropriate reference to the payoff time cell in the same column. Finally, replace "loan_amount" with the reference to the cell containing the home loan amount.
c. Press Enter to calculate the total interest payment for this cell.
d. Copy this formula to the rest of the cells in the table. Make sure to use relative references so that the formulas adjust correctly for each cell. To do this, select the first cell and then drag the fill handle (a small square at the bottom-right corner of the cell) across the entire table.
e. Excel will automatically adjust the relative references in the copied formulas to refer to the correct interest rate and payoff time cells.
4. The results in each cell will represent the total interest payment for the corresponding interest rate and payoff time. Keep in mind that the values might be negative, depending on how you set up the formulas and the conventions used for positive and negative values in your context.
By following these steps, you will have a table that calculates the total interest paid over the duration of the repayment for different interest rates and loan payoff times.