Could someone tell me how to calculate this in excel?
You are applying for a 30-year, fixed-rate (APR 6.50%), monthly-payment-required mortgage loan for a house that sells for $80,000 today. The mortgage bank will ask you for 20% initial down payment (in cash, paid immediately) of the house value, and charge you an extra $3,000 closing cost (carried into loan balance and amortized later) when the loan is approved. What should be your monthly loan payment (assuming payment is due by the end of each month)?
10 years after buying the house, what will be the remaining principal balance of your loan? (Hint: For a loan or investment, the beginning loan balance is the same as “present value”, whereas the ending or remaining balance is the same as “future value”. And please don’t forget this is a monthly loan.)
10 years after buying the house (as Part b aforementioned), the loan market rate drops from 6.50% APR to 4.50% APR, you want to refinance on the remaining loan principal balance, but the bank will charge you an extra $4,000 refinancing fee (carried into the remaining loan balance and amortized later). Would you be able, and by how much, to lower your monthly loan payment if you choose to refinance over the remaining loan life (i.e., instead of the extension of another 30 years)? Based on your calculation results, should you choose to refinance or not?
fds
To calculate the monthly loan payment, you can use the PMT function in Excel.
1. Open Excel and enter the following information in cells:
- Cell A1: $80,000 (house value)
- Cell A2: 20% (down payment percentage)
- Cell A3: 6.50% (APR)
- Cell A4: 30 (loan term in years)
- Cell A5: $3,000 (closing cost)
2. Calculate the loan amount by subtracting the down payment and closing costs from the house value. In cell A6, enter the formula: =A1*(1-A2)+A5
3. Convert the APR and loan term from annual to monthly values. In cell A7, enter the formula: =A3/12 (to get the monthly interest rate) and in cell A8, enter the formula: =A4*12 (to get the number of monthly payments)
4. Calculate the monthly loan payment using the PMT function. In cell A9, enter the formula: =PMT(A7,A8,-A6)
The result in cell A9 will be your monthly loan payment.
To calculate the remaining principal balance after 10 years, you can use the FV function in Excel.
1. In a new cell, let's say cell B1, enter the formula: =FV(A7,10*A8,-A9)
The result in cell B1 will be the remaining principal balance.
To calculate the new monthly loan payment after refinancing, you can follow a similar process as above.
1. Enter the information for the refinance scenario in new cells:
- Cell B2: 4.50% (new APR)
- Cell B3: $4,000 (refinancing fee)
2. Calculate the remaining loan principal balance after 10 years with the new APR and refinancing fee. In a new cell, let's say cell B4, enter the formula: =B1+B3
3. Calculate the new monthly loan payment using the PMT function. In a new cell, let's say cell B5, enter the formula: =PMT(B2,A8,-B4)
The result in cell B5 will be the new monthly loan payment after refinancing.
To determine if you should refinance, compare the new monthly loan payment with the current monthly loan payment. If the new payment is lower, it might be beneficial to refinance. If it's higher, refinancing may not be advantageous.