Monthly Loan Payment Develop a spreadsheet model to compute the monthly payment on a $100,000 loan with an annual interest rate of 6% and a term of 25 years. The monthly payment can be computed using the Excel function = -PMT(interest_rate/12, term*12, loan_amount) ornote placement of the minus signs in both formulas = PMT(interest_rate/12, term*12, -loan_amount)

Develop a two-way data table that shows the effect on the mortgage payment of varying the interest rate from 2% to 8% in increments of 0.5% and varying the term of the loan from 15 to 30 years in increments of 5 years.

How much can a loan applicant afford to borrow if they can pay at most $1500/month on a 30-year loan? (Hint: Use one of the What-If Analysis tools to figure this out.)

How much can a loan applicant afford to borrow if they can pay at most $2250/month on a 20-year loan?

According to the data table you created for the Monthly Loan Payment model, what is the maximum monthly payment based on these possible combinations of interest rate and term? (Format your answer rounded to the nearest cent. )

B D E F G I A 1 Monthly Loan Payment 2 3 Data M 4 Loan amount $100,000.00 6% 5 Annual interest rate 6 Term 25 years 7 8 Model 9 Monthly payment 10 $0.64 11 12 13 14 15 16 17 18