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 solve this problem, you can use the PMT function to calculate the monthly payment amount and the IPMT function to calculate the interest paid for each month of repayment. Here's how you can set up your Excel spreadsheet to calculate the total interest paid over the duration of the repayment:
1. Create a table with the following structure:
- The interest rates (3.0%, 3.5%, 4.0%, 4.5%, and 5.0%) should be in a column on the left side of the table.
- The loan payoff times (15, 20, 25, and 30 years) should be in a row at the top of the table.
- Leave an empty cell at the top left of the table for headers.
2. Enter the principal loan amount of $150,000 in a cell outside of the table. Make note of the cell reference (e.g., A1).
3. In the first cell inside the table (below the interest rate column and to the right of the payoff time row), enter the following formula to calculate the monthly payment amount:
```
=PMT(interest_rate/12, payoff_time*12, -loan_amount)
```
Replace "interest_rate" with the cell reference of the first interest rate in your table (e.g., A2) and "payoff_time" with the cell reference of the first payoff time in your table (e.g., B1).
4. Press Enter to calculate the monthly payment amount. Note the cell reference of this formula (e.g., B2).
5. Copy the cell with the monthly payment amount (B2), and paste it into the entire table. When pasting, make sure to choose the "Paste Values" option, so the values stay the same.
6. In each cell of the table containing the monthly payment amount, enter the following formula to calculate the interest paid for the respective interest rate and payoff time:
```
=IPMT(interest_rate/12, ROW()-row_of_first_interest_rate_cell , COLUMN()-column_of_first_payoff_time_cell , payoff_time*12, -loan_amount)
```
Replace "interest_rate" with the cell reference of the current interest rate in your table (e.g., A2), "payoff_time" with the cell reference of the current payoff time in your table (e.g., B1), "row_of_first_interest_rate_cell" with the row number of the first interest rate cell in your table, and "column_of_first_payoff_time_cell" with the column number of the first payoff time cell in your table.
7. Press Enter to calculate the interest paid for each cell. The resulting values will correspond to the interest rate at the left of the row and the payoff time at the top of the column.
8. To find the total interest payment for a specific interest rate and payoff time (e.g., 4.0% and 20 years), locate the corresponding cell in the table and multiply it by the number of months (payoff time * 12):
```
=interest_payment_cell * (payoff_time * 12)
```
Replace "interest_payment_cell" with the cell reference of the corresponding interest payment cell in your table (e.g., C8) and "payoff_time" with the desired payoff time (e.g., 20).
By following these steps, you should be able to create a spreadsheet that calculates the total interest paid over the duration of the repayment for the given interest rates and loan payoff times.