Final answer:
To calculate the remaining balance on a 30-year loan with a 7% interest rate and monthly payments of $1,200 after seven years, use the Excel FV function with the formula =FV(0.07/12, 7*12, -1200). This will provide the remaining balance, considering the monthly interest and payments over the 7-year period.
Step-by-step explanation:
Calculating the Remaining Balance of an Amortizing Loan
To calculate the remaining balance of a 30-year fixed-payment fully-amortizing loan at the end of seven years with an interest rate of 7% and a monthly payment of $1,200, we need to use the remaining balance formula in Excel, which is based on the present value of an annuity. This formula requires the interest rate per period (monthly in this case), the total number of periods, and the payment amount.
To solve for the remaining balance in Excel:
Enter the interest rate per period (monthly) into a cell by dividing the annual rate by 12. For a 7% annual rate, this would be 0.07/12.
Identify the total number of payments for 30 years, which is 30 x 12.
Identify the number of payments already made, which is 7 x 12.
Use the PMT function for the monthly payment, =PMT(annual rate/12, 30 x 12, loan principal); however, we know the payment amount is $1,200.
Finally, calculate the remaining balance using the FV function: =FV(monthly interest rate, number of payments made, -payment, present value). The present value is the initial loan amount, which we don't have, but it's not needed since the payment and interest rates are consistent.
The Excel formula will look like =FV(0.07/12, 7 x 12, -1200). Enter this formula in a cell to get the remaining balance after 7 years.
Note that in Excel, a negative sign is placed before the payment amount in the FV function to indicate a cash outflow.