calculate how much a lender would have to increase interest rate on a loan. If a broker charging fee of 1% of loan amount wants to be paid by lender. The loan amount is 950000 5 year term amortized over 25 years and the par pricing is 6.5%. show the how it can be done in execl

To calculate how much a lender would have to increase the interest rate on a loan so that they can pay a broker's fee of 1% of the loan amount, we need to break the problem down into steps. Here’s how we can do this in Excel:

### Steps:
1. **Loan Details**:
   - Loan Amount: \$950,000
   - Broker's Fee: 1% of loan amount = 0.01 * \$950,000 = \$9,500
   - Par Pricing Interest Rate: 6.5% (the interest rate without the broker fee)
   - Term: 5 years
   - Amortization Period: 25 years

2. **Amortization Formula**:
   The loan repayment formula for a fixed-rate loan is given by the following:
   \[
   M = \frac{P \cdot r}{1 - (1 + r)^{-n}}
   \]
   Where:
   - $M$ = Monthly payment
   - $P$ = Loan amount
   - $r$ = Monthly interest rate (annual interest rate / 12)
   - $n$ = Total number of payments (amortization period in months)

3. **Step-by-Step Process**:

### Excel Implementation:

#### Step 1: Calculate Monthly Payment at Par Pricing (6.5%)
- **In Excel**:
   1. Input the following values:
      - Loan Amount = 950,000
      - Annual Interest Rate = 6.5%
      - Loan Term (Amortization) = 25 years
      - Number of Payments = 25 years * 12 months = 300 payments

   2. Use the formula for calculating the monthly payment at par interest rate:
      ```
      =PMT(6.5%/12, 25*12, 950000)
      ```

#### Step 2: Calculate Broker's Fee
   1. **Broker's Fee Calculation**:
      - Broker’s Fee = 1% of \$950,000 = \$9,500

#### Step 3: Adjust Interest Rate to Cover Broker’s Fee
The lender will need to increase the interest rate to cover the cost of the broker’s fee. The question is how much the interest rate needs to be increased so that the Present Value of the additional monthly payments over the 5-year term equals the broker’s fee.

1. **Solve Iteratively in Excel**:
   1. Add a column to trial different interest rates.
   2. Create the formula to calculate the monthly payment using different rates.
   3. Adjust the interest rate until the present value of the difference in payments for 5 years equals \$9,500.

#### Step 4: Example of Step-by-Step in Excel:
1. In Cell A1: Enter `950000` (loan amount)
2. In Cell A2: Enter `6.5%` (par interest rate)
3. In Cell A3: Enter `300` (term of loan in months, 25 years * 12)
4. In Cell A4: Enter `5*12` (60 months for 5-year term)

In Cell B1, use the formula to calculate the monthly payment:
```
=PMT(A2/12, A3, -A1)
```

In Cell B2, calculate broker’s fee:
```
=0.01 * A1
```

In Cell B3: Add an interest rate increment (e.g., 6.6%) and calculate a new monthly payment:
```
=PMT(6.6%/12, A3, -A1)
```

Finally, calculate the difference in monthly payments and multiply by 60 months to see how much the difference accumulates over the 5 years:
```
=(B3 - B1) * 60
```

Adjust the interest rate until the difference equals \$9,500.

### Detailed Explanation:
The key idea is that the lender will have to raise the interest rate enough so that the higher monthly payments cover the broker’s fee over the first 5 years. We use Excel to trial and error the new interest rate.

Do you want to explore any further details or have additional questions?

---

### 5 Follow-up Questions:
1. How does the term of the loan affect the interest rate adjustment?
2. Can we calculate the effect of the fee over different loan durations (e.g., 10 years)?
3. How would the calculation change if the loan had a different amortization period (e.g., 20 years)?
4. How would early repayment penalties affect this calculation?
5. What is the relationship between the loan's interest rate and monthly payment?

### Tip:
Using Excel's **Goal Seek** function can help automate the process of finding the required interest rate to cover the broker's fee!

Calculate how much a lender would have to increase the interest rate on a loan if a broker charging a fee of 1% of the loan amount wants to be paid by the lender. The loan amount is $950,000, 5-year term amortized over 25 years, and the par pricing is 6.5%. Show how it can be done in Excel.

Learn how to calculate the necessary interest rate increase on a loan to cover a broker's fee using Excel. This tutorial covers financial mathematics concepts, including loan amortization, interest rate adjustments, and using Excel functions like PMT. Suitable for undergraduate or graduate finance students.

Math Problem Statement

Solution

Ask a new question for Free

By Image

Math Problem Analysis

Mathematical Concepts

Formulas

Theorems

Suitable Grade Level

Related Recommendation